Error with conditional formatting formula

Copper Contributor

I have an issue with the formula I created below. I am trying to create a schedule for employees that will allow them to input their appointments and see what appointment is currently underway. I created the formula in conditional formatting so that it would highlight the half hour we are currently in. It works for the first half hour of every real-time hour (hh:00-hh:29), however, once the real-time hour reaches hh:30-hh:59 it highlights the current half hour AND the previous half hour. I am unsure of what I've done wrong. Could anyone assist?   

 

FORMULA:

=AND(

     $C$2=TODAY(),

     OR(

          HOUR($B2)=HOUR(NOW()),

          HOUR($B3)=HOUR(NOW()),

          HOUR($B4)=HOUR(NOW())

     ),

     AND(

          AND(MINUTE($B2)<=MINUTE(NOW()),MINUTE($B2)-MINUTE(NOW())<=0),

          AND(MINUTE($B3)<=MINUTE(NOW()),MINUTE($B3)-MINUTE(NOW())<=0),

          AND(MINUTE($B4)<=MINUTE(NOW()),MINUTE($B4)-MINUTE(NOW())<=0)

     )

)

 

Progress Capture (Error).PNG

Progress Capture.PNG

4 Replies

@lexmayfie 

To highlight current half-hour

image.png

it could be

=( ($C$2+$B4) <=NOW() )*( ($C$2+$B4+TIME(0,30,0) ) >NOW() )

Be sure B4 is the first cell for the time.

This is perfect for the first row of the half hour but I have three rows per half hour, I can play around with it a little to get the other rows to interact as well but I think that's where I felt things became more complicated.

 

Edit: I just created an or condition for each separate row and it works perfect! Thank you so much :)

@lexmayfie 

Select C4:I57. C4 should be the active cell in the selection.

Use this formula for the conditional formatting rule:

 

=AND($C$2=TODAY(),OFFSET($B4,-MOD(ROW($B4)-1,3),0)=FLOOR(MOD(NOW(),1),TIME(0,30,0)))

@lexmayfie 

Sorry, missed that. It looks like B2:B4 are merged. When in B2 you have time, B3 and B4 are actually empty. Easiest way is to unmerge such cells and repeat time 3 time for each block. With another conditional formatting rule you may show the time only in the middle cell, for two others select font color same as background.