Struggling with some conditional formatting and I really don't understand why as it's something I've done numerous times before.
I have dates in Column F I need these to change if 6-11 months has passed, and a different colour for 11 months or more has passed
ONLY IF column U is equal to "specific text"
For some reason I can't get it to play how it should.
Any help appreciated.
2 Answers
Create a custom rule applying to =$F:$Fwith the following formula: =AND(TODAY()-F1>182.5,TODAY()-F1<334.8,U1="specific text").
It's not exactly 6 or 11 months on a calendar, but it's corresponding percentages of the year. I chose 182.6 days and 334.8 days (365.25 divided by 2 and 365.25 * 11/12).
So all of the following need to be true:
- Today's date minus the date in the cell must be more than 182.6
- Today's date minus the date in the cell must be less than 334.8
- The corresponding U cell must match the text.
Then create a second rule and just use the same format without the second AND clause and tweaked for the right days.
You may try this one.
How it works:
Formula to highlight Dates, when 6 -11 months has been passed.
=AND(H33>=DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY())),H33<=DATE(YEAR(TODAY()),MONTH(TODAY())+11,DAY(TODAY())),I33="A")Formula to highlight Dates, when 11 months or more has been passed.
=AND($H33>=DATE(YEAR(TODAY()),MONTH(TODAY())+11,DAY(TODAY())),$I33="A")
N.B.
The base date to compare is,
Current date.You may edit the base date as well adjust cell references and the specific text in the formula, along with applied colors as needed.
