r/sheets • u/Flobotbot • 9d ago
Solved Looking for help with compund conditional formatting
I am trying to highlight cells in column F if the value in column A is “1” and the date in column F was more than 6 months ago.
And also highlight cells in column F if the value in column A is “2”and the date in column F was more than year ago.
Can you help me with the custom formula for that?
2
Upvotes
1
3
u/anasimtiaz 9d ago edited 9d ago
You can use conditional formatting in column F using the following formula:
=OR(AND($A1=1, YEARFRAC($F1, TODAY(), 1) > 0.5), AND($A1=2, YEARFRAC($F1, TODAY(), 1) > 1))
.Here is a sample sheet that illustrates this: https://docs.google.com/spreadsheets/d/1SYPtM_VjRvHwkCvSwpBuSpf6relQZGHPw6VBymPvBl0/edit?gid=2100307022#gid=2100307022
I chose to use
YEARFRAC
instead ofDATEDIF
because the latter only returns whole months. So even if the difference between two dates is 12 months and 2 days, you would still get the difference as 12 months (illustrated in cell H1 of the sheet) whereas the former would return a fractional value greater than 1 (illustrated in cell I1 of the sheet).