MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional formating for future date


Posted by giohess on August 09, 2001 5:17 PM

I have a spreadsheet listing employees names and the date they were hired. I need to send various notifications to them 10 months from the date of hire. I want to use conditional formating to turn the cell red when ten months have passed since the date of hire. Since everyone has a different hire date, how do I set the condition to calculate ten months past their hire date and turn the cell red?


Posted by Aladin Akyurek on August 10, 2001 1:02 AM

Activate the first cell that has (1) an employee name if you want the name to turn red or (2) a hire date if you want the name to turn red. I'll assume A1 and B1 to be the first respective cells.

Select "Formula Is" on Conditonal Formatting dialog.
Enter as formula:

=DATEDIF(B1,TODAY(),"M")>=10

Activate Format, select red on Patterns tab.
Activate OK.

While in the activated cell, click on the icon for Format Painter, then select the rest of the cells below the activated first cell and let it go.


Aladin