Forum Discussion

Trivium013's avatar
Trivium013
Copper Contributor
Nov 30, 2020
Solved

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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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.

Resources