Forum Discussion
Conditional formatting to identify a date in x month's time (3 months and 6 months)
Select the dates in the header row.
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 'equal to' from the second drop down.
In the box next to it, enter the formula
=EOMONTH(TODAY(), 2)+1
Click the Format... button.
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Repeat these steps, but with the formula
=EOMONTH(TODAY(), 5)+1
Select the same fill color or a different one, according to your preference.
Do you only want to highlight cells in the header row, or entire columns?
Today is the 27th of February, 2023. Which months would you like to be highlighted? May 2023 and August 2023?
- Hi there Hans, only the header row please 🙂
Regarding the dates; yes, I'm hoping that it shows whichever month is now + 3 months / 6 months inclusive so you have your example correct
Thank you for replying 🙂Select the dates in the header row.
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 'equal to' from the second drop down.
In the box next to it, enter the formula
=EOMONTH(TODAY(), 2)+1
Click the Format... button.
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Repeat these steps, but with the formula
=EOMONTH(TODAY(), 5)+1
Select the same fill color or a different one, according to your preference.
Hi HansVogelaar
I wonder if you can help with my conditional formatting issues, once again.
Similar to above, I'm looking to highlight cells if the dates comes within 2 months of todays date, but the standard date formula will only allow up to 1 month. Is there a simple formula that I can use for this?
Also, I have an issue with a SUMPRODUCT. I was attempting to make COUNTIFS work in a way that will count how many cells from D2:P2 have dates in the past, but only if the date in C2 is in the future.
I was given the following formula to help: =SUMPRODUCT((C2>=TODAY())*(D2:P2<=TODAY())*(D2:P2<>""))
But I have a further requirement for the above formula. C2 will either contain "N/A", a date in the future or a date in the past. I'd like it to only count up for the cells that have dates in the future, not dates in the past, or N/A.
I appreciate if this isn't your remit but thanks in advance for any help you can provide!
