Forum Discussion
Conditional formatting to identify a date in x month's time (3 months and 6 months)
Hello clever people.
I have a workbook that has dates along the top as row headers. the dates are 01/MM/YY but formatted to show mmm-yy.
I need to be able to quickly show month's that are in 3 and in 6 month's time but I think the key thing that is preventing me from writing an effective conditional formatting formula is that I can't use '90 days from now' because that will only be true on a very limited number of occasions.
Conditional formatting on 'Highlight cells > a date occurring' only goes to one month ahead and I have done so many searches looking for the answer to this using conditional formatting but am yet to find the answer.
Please can someone offer a conditional formatting rule to show cells that are 3 month's ahead of where I am now (now being the current month which the above conditional formatting already works for) where the month date is given as 01/xx/xx as in the first of the month?
Thank you for reading my post
Su
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.
13 Replies
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.
