MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional formatting and dates


Posted by Philip Bedford on November 07, 2001 12:47 AM

I have a column of dates when a course was passed, and next to it a column with the date when the course is due to be refreshed. How do I highlight the second column to show the course is overdue?

Thanks,
Phil.


Posted by Aladin Akyurek on November 07, 2001 1:20 AM

Phil,

What is your decision rule to conclude that a given course is overdue?

Aladin

============

Posted by phil on November 07, 2001 11:33 PM

Most of the courses have a 5yr life and then require refreshing. I would like to have a 6month warning and an overdue colour (if that makes sense)

Thanks.

Phil.

Posted by Aladin Akyurek on November 08, 2001 12:23 AM


Lets assume that B1 is the first cell that contains an overdue date.
Select all of the cells with overdue dates.
Activate Format|Conditional Formatting.
Choose the option Formula Is for Condition 1 in the Conditional Formatting window.
Enter as formula:

=DATEDIF(TODAY(),B1,"m")=6

Activate Format.
Select a color (red, maybe).
Click OK.
Click OK.

Aladin

============