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]
Thank you so much, that makes so much more sense than what I was trying 😅
Just curious (as I said, total novice) - is there a way to make the whole row follow the format of column A so if column A changes colour due to this rule, the entire row follows suit? It's not a huge issue if not, just wondered if it was a possibility
Yes, absolutely! You can make the entire row follow the same formatting based on the value in Column A.
Here’s how to do it:
Step-by-Step:
- Select the entire range of rows you want to format (e.g., A2:C100, or more columns if needed).
- Go to the Home tab → Conditional Formatting → New Rule.
- Choose “Use a formula to determine which cells to format.”
- Then enter a formula that checks the value in Column A. For example:To highlight rows older than 6 months:
bash
CopyEdit
=AND(ISNUMBER($A2), $A2 < EDATE(TODAY(), -6)) - To highlight rows older than 3 months (but less than 6):
bash
CopyEdit
6. =AND(ISNUMBER($A2), $A2 >= EDATE(TODAY(), -6), $A2 < EDATE(TODAY(), -3))
7. Click Format, choose the color you want (e.g., green or red), then click OK.
🔁 Explanation:
- The dollar sign ($A2) locks the formula to column A, while the row number adjusts as Excel checks each row.
- This way, the rule is triggered by what's in Column A, but it applies the formatting to the entire row.
Yes, absolutely!
You can make the entire row follow the same formatting based on the value in Column A.Here’s how to do it:
Step-by-Step:
- Select the entire range of rows you want to format (e.g., A2:C100, or more columns if needed).
- Go to the Home tab → Conditional Formatting → New Rule.
- Choose “Use a formula to determine which cells to format.”
- Then enter a formula that checks the value in Column A. For example: To highlight rows older than 6 months:
=AND(ISNUMBER($A2), $A2 < EDATE(TODAY(), -6)) - To highlight rows older than 3 months (but less than 6):
=AND(ISNUMBER($A2), $A2 >= EDATE(TODAY(), -6), $A2 < EDATE(TODAY(), -3)) - Click Format, choose the color you want (e.g., green or red), then click OK.
Explanation:
- The dollar sign ($A2) locks the formula to column A, while the row number adjusts as Excel checks each row.
- This way, the rule is triggered by what's in Column A, but it applies the formatting to the entire row.
Let me know if you'd like a sample file or short video guide — happy to help!