Forum Discussion
Error with conditional formatting formula
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)
)
)
4 Replies
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)))
To highlight current half-hour
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 🙂
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.
