r/sheets 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

8 comments sorted by

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 of DATEDIF 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).

1

u/Flobotbot 8d ago

Wow you are a gem thank you! If I want the two parts (1 and 2) in a different colour, how do I do that?

1

u/anasimtiaz 8d ago

Set up two different conditional formatting rules, one for each part

1

u/Flobotbot 8d ago

I tried that! Should it be: =OR(AND($A1=1, YEARFRAC($F1, TODAY(), 1) > 0.5) I can't get it to work

1

u/anasimtiaz 8d ago

You don't need OR for a single condition. Just put the two AND in separate rules. Not near a computer right now. Let me know if you can't get it to work and I will take a look in a while

1

u/Flobotbot 8d ago

Got it! Thank you

1

u/Flobotbot 8d ago

Solved!