1

I'm having trouble with conditional formatting. I would like a cell with a date to be highlighted after a certain amount of time has passed. That amount of time depends upon another cell. For instance, A1 says "Monthly" and I want A2 to highlight after a month has passed. B1 says "Weekly" and I want B2 to highlight after a week has passed.

These work:

=AND(TODAY() - $H7 > 7, $G7 = "Weekly")

=AND(TODAY() - $H7 > 14, $G7 = "Bi-weekly")

=AND(TODAY() - $H7 > 28, $G7 = "Monthly")

=AND(TODAY() - $H7 > 56, $G7 = "Bi-monthly")

I basically want to be able to change G7 from "Weekly" to "Monthly" and it change H7 to the proper amount of time passed.

Thank you!

1

1 Answer 1

0

TL;DR

Add your conditions together. If at least one matches the result will be a non-zero number that Sheets will interpret as TRUE.

HLOOKUP

=TODAY()-$H7 >  HLOOKUP($G7,
   {"Weekly","Bi-Weekly","Monthly", "Bi-Monthly"; 
     7, 14, 28, 56}, 2, 0))

SUMPRODUCT

=LET(days,TODAY()-$H7, max,$G7,
   SUMPRODUCT(
     (max="Weekly")*(days>7), 
     (max="Bi-Weekly")*(days>14), 
     (max="Monthly")*(days>28),
     (max="Bi-Monthly")*(days>56))) 

AND

=LET(days,TODAY()-$H7, max,$G7,
   AND(max="Weekly", days>7)+ 
   AND(max="Bi-Weekly", days>14)+
   AND(max="Monthly", days>28)+
   AND(max="Bi-Monthly", days>56))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.