Forum Discussion
Conditional Formatting in Excel tables
Hi,
I am a complete novice to conditional formatting. I have managed to set some rules successfully, but have no idea how to do this one. I have a table based on the dates events have taken place. The table will be added to periodically with more dates as and when needed.
Essentially, what I want to happen is to set a date-based rule where the formatting of the rows changes (eg. strikethrough or colour change) once a set time period (eg. 3 or 6 months from the date in that row) has passed. The dates are always in column A.
I thought I might need to involve EDATE, but honestly I haven't the first clue where to start. Could anyone give me some advice on the right formula to use, please?
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]
6 Replies
This is exactly what I was looking to do, thank you so much for your help!
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 possibilityYes, 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!
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.
