Forum Discussion
Conditional formatting, color cell if empty and date is passed
So for my gym I'm creating a sheet for personal training apointments.
Idealy I color apointments that are in the past green. Using =$I4<TODAY()
Though excel autofills all blanks green aswell, so I use ''Formal cells that only contain'' on BLANKS to counter that.
I wish to mark the empty collums N:P, S:U etc red IF the previous apointment is in a past date.
(So I can easily see when a new apointment is to be made)
But it does not seem to be allowing it due to the empty cell formatting BLANK
I've tried =IF(AND(I$4>TODAY(),N$4=""),1,0)
Any ideas on solving this?
Thanks a lot in advance!
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.
3 Replies
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!
