Forum Discussion
Conditional formatting, color cell if empty and date is passed
Trivium013 In I:K you coloured all dates in the past green, excluding blanks. Probably something like this: =AND($I4<TODAY(),$I4<>"")
For columns N:P, I believe you need three separate rules:
Rule 1: =$N4>=TODAY() , no formatting, stop if true
Rule 2: =AND($N4<TODAY(),$N4<>"") , green
Rule 3: =AND($I4<TODAY(),$I4<>"") , red
Rule 1 captures next appointment made in the future and stops further evaluation
Rule 2 captures next appointment made but already passed, excluding blanks
Rule 3 captures past appointment in the previous block of appointments and no next appointment made yet and excluding blanks
You can apply the same logic to the next block of appointments.
Demonstrated in the attached file.
Trivium013 In I:K you coloured all dates in the past green, excluding blanks. Probably something like this: =AND($I4<TODAY(),$I4<>"")
For columns N:P, I believe you need three separate rules:
Rule 1: =$N4>=TODAY() , no formatting, stop if true
Rule 2: =AND($N4<TODAY(),$N4<>"") , green
Rule 3: =AND($I4<TODAY(),$I4<>"") , red
Rule 1 captures next appointment made in the future and stops further evaluation
Rule 2 captures next appointment made but already passed, excluding blanks
Rule 3 captures past appointment in the previous block of appointments and no next appointment made yet and excluding blanks
You can apply the same logic to the next block of appointments.
Demonstrated in the attached file.
Riny_van_Eekelen Fantastic, this worked! Thank you very much for the support!
