# Error with conditional formatting formula

Copper Contributor

# 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

# Re: Error with conditional formatting formula

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.

# Re: Error with conditional formatting formula

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

# Re: Error with conditional formatting formula

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

# Re: Error with conditional formatting formula

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.