If statement using date to change row colour

Z Kindo Posts 1 Registration date Monday March 3, 2014 Status Member Last seen March 3, 2014 - Mar 3, 2014 at 09:19 AM
 Z Kindo - Mar 3, 2014 at 11:38 AM
I have a sheet with the following header row:

Reference Client MAWB / BoL Date logged Date completed Days to resolve Logged by Caller type Error area/ Type Completed by

This is a rough CRM system to try and resolve queries logged by a certain time. Now I need to know how and what formula am I to use if I for example (1) want a row to stay as is if it is blank (no data) so no change in colour; (2) have it change and stay yellow if it is 1 to 2 day after the date logged; (3) change to another colour say green if it is 3-5 days after the date logged and (4) finally turn red if is is > 5 days than the date logged.

Essentially trying to expedite and increase customer relations by resolving queries as soon as possible and by colour coding them in term of time after date logged we can see who does not attend to queries and who is effective and efficient.

2 replies

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Mar 3, 2014 at 11:11 AM
Hi Z Kindo,

Well, Excel 2003 only has 3 conditional format rules, which requires a code to do what you want.

Since you are asking for formula's I'm assuming you have a later version of Excel.

Formula 1: =E2=""
Formula 2: =VANDAAG()-E2<=2
Formula 3: =VANDAAG()-E2<=5
Formula 4: =VANDAAG()-E2>5

Best regards,
Many thanks, I will try this. Much appreciated!