Solved if then statements with dates in excel

July 11, 2011 at 16:19:00
Specs: Windows 7
How do I write an If/Then statement in excel to change data to red if it's 6 months old? I have one field for the current date, B2, and a column of dates, B6-B40, that I want to know if the dates are older than 6 mo from today's date. If they are older then 6 months from today's date, I want the dates in column B and their corresponding data in column A to turn red. Any ideas? Thanks in advance.

See More: if then statements with dates in excel

Report •

July 11, 2011 at 16:37:20
✔ Best Answer
See if this works for you:

This is for Excel 2007

1) Select your cell or Range of Cells: B6-B40
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: =DATEDIF(G3,TODAY(),"M")>6

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

This will highlight the cells that are GREATER THAN Six months.
For Six Month OR GREATER use:


EDITED, did change the cells from my test sheet.

Report •

July 12, 2011 at 09:19:26
Excellent! This worked very well. Thank you for your time and expertise. It's much appreciated.

Report •

Related Solutions

Ask Question