Thursday, February 03, 2005

Using Conditional Formatting to flag due dates in Excel

I used the formulas in the image below to colour date cells in a column based on a age requirement. I needed to flag dates that were 4 years past the date entered Red as overdue, 1 month short of the 4 years Orange as a warning and 2 months short of 4 years Yellow so as to indicate the four years was almost up.
How I did it.
I selected one cell and went to the conditional formatting dialog box. (Format | Conditional Formatting).
Then I worked out the formula I needed. The AND formula requires that all events inside the brackets must be TRUE for the event to occur. In this case the date in the current cell plus 1461 days (4 years - including leap year) must be less than or equal to the current date TODAY() and the current cell must not be blank. In Excel <> is the way to express is not equal to.
I then added the second event and repeated the first formula but made it 1431 (less 30 days) plus the entered date to give a warning when 1 month away from 4 years.
Finally I added the third condition and used 1401 to be 60 days short of 4 years that the yellow warning will happen.
Note I have used relative formulas here. Once I completed it for one cell I copied that cell and then selected the entire column and Pasted Special as Formats.


Posted by: Jethro | 10:41 PM |