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.
Sorry Hans. For ease, I've copied and pasted in my previous conversation with someone else on the tech community:
Me:
I wonder if you could help me with my own COUNTIFS issue.
I'm 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.
The formula I have so far is:
=COUNTIFS(C2,">="&TODAY(), D2:P2,"<="&TODAY())
This is returning #VALUE! and I can't seem to work out where I'm going wrong with the formula.
Thanks in advance.
Other member:
=SUMPRODUCT((C2>=TODAY())*(D2:P2<=TODAY()))
SUMPRODUCT returns the intended result in my sheet if there are no empty cells in range D2:P2.
=SUMPRODUCT((C2>=TODAY())*(D2:P2<=TODAY())*(D2:P2<>""))
This formula works if i want to disregard empty cells in range D2:P2.
Me:
Thank you so much. One last requirement that I forgot to mention:
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.
Thanks again.
The above is my requirement for the SUMPRODUCT function (apologies, I copied and pasted the wrong formula'.
The only other thing I needed help with, was that the conditional formatting to change the colour of the dates within 2 months to yellow is colouring dates in previous years. For example, it's changing the colour of 20-Feb-2022 and 31-Dec-2021
=SUMPRODUCT((C2>=TODAY())*(C2<>"N/A")*(D2:P2<=TODAY())*(D2:P2<>""))
For the conditional formatting: a rule with formula
=AND(D2>=TODAY(), D2<=EDATE(TODAY(), 2))