Forum Discussion
Conditional Formatting in Excel tables
How to Automatically Highlight Past Event Dates (Older Than 3 or 6 Months)
Hi Excel Community,
I highlighted rows in the events log once the date becomes 3 or 6 months old.so I used Conditional Formatting with formulas (no VBA).
🧪 Sample Table:
Date
Event
Location
01/12/2024
Workshop
London
10/01/2025
Launch Day
Lagos
15/03/2025
Webinar
Online
01/05/2025
Demo
Nairobi
15/06/2025
Training
Accra
10/07/2025
Networking
Cape Town
Goal: Highlight rows where the date in Column A is more than 3 months ago.
Steps:
- Select your full table (A2:C10)
- Go to Home > Conditional Formatting > New Rule > Use a Formula
- Use this formula for 3 months ago:
=AND(ISNUMBER($A2), $A2 < TODAY() - 90)
- Format with strikethrough or light gray fill
- Click OK
🗓️ Use this formula for 6 months ago:
=AND(ISNUMBER($A2), $A2 < EDATE(TODAY(), -6))
📷 Screenshots included below showing setup and result.
- [Setup Screenshot – Table View]
2. [Result Screenshot – Highlighted Past Dates]
Let's say you want to set the font (or fill) color to orange if the date is more than 3 months in the past and to red if the date is more than 6 months in the past.
Select the range with the dates.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than' from the second drop down.
In the box next to it, enter the formula
=EDATE(TODAY(), -3)
Click Format...
Activate the Font (or Fill, depending on your preference) tab.
Select orange as color.
Click OK, then click OK again.
Repeat these steps, but with the formula
=EDATE(TODAY(), -6)
and red as color.