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?   

















Progress Capture (Error).PNG

Progress Capture.PNG

4 Replies


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 :)


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

Use this formula for the conditional formatting rule:




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.