Solved Conditional Formatting and IF/THEN Statements in Excel

Microsoft Office excel 2007
October 25, 2012 at 12:56:03
Specs: Windows XP
I have a spreadsheet with report dates that need to be renewed annually. I'd like to format the date cell to turn red when the date is within 30 days of its annual expiration.

For example, a report was last completed on 1/1/2012 thus cell C5 = 1/1/2012. I would like C5 to turn red on and after when the current date is 12/2/2012 (I have the current date in cell C26).


See More: Conditional Formatting and IF/THEN Statements in Excel

Report •

October 25, 2012 at 15:45:27
✔ Best Answer
Try this:

Conditional Formatting 2007

1) Select your cell or range of cells, IE C5
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:


6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

See how that works.


Report •

October 27, 2012 at 09:21:58
Hi Mike,

Thank you for DATEDIF - I wasn't aware of this one. The formula that ended up working for me was =DATEDIF(C5,TODAY(),"D")>=335 as I want the cell to turn red 30 days prior to the annual expiration (so 365 - 30 days).

I appreciate your assistance!

Report •

Related Solutions

Ask Question