Add date to existing time based conditional format

[Luke777]

Hi All,

I am using the following formula in conditional formatting to highlight relevant cells.

=AND(TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))>$A4,TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))<$B4)

Specifically, columns A and B starting at row 4 contain times in 15 minute intervals.

A4 contains 6:30
B4 Contains 6:45

A5 contains 6:45
B5 contains 7:00

And so on.

The above formula when used in conditional formatting will highlight the two value cells between which is the current time. At the time of writing, A56 and B56 are highlighted on my sheet

So this all works fine.

However, I need to add the date to every time in column A for annoying *other* reasons. I have hidden the date part of the entry so that the cell (A4 for example) still displays identically to before. Unfortunately, and not unexpectedly, this means the formula no longer works as it has no idea what to do with the date part of the cell data.

Any ideas on what I need to add? Am I correct in thinking I just need to add DATE( after the and? and possible add the date in column B too to keep things simple?

Thank you!

 

[Fluff]

How about

=AND(MOD(NOW(),1)>MOD($A4,1),MOD(NOW(),1)<MOD($B4,1))

 

[Luke777]

How about

=AND(MOD(NOW(),1)>MOD($A4,1),MOD(NOW(),1)<MOD($B4,1))

Will this require the date to be in both A and B? I've no idea how MOD works (I shall read up on it though) so I'm unsure on the requirements.

Also, thanks for your help

 

[Fluff]

As long as the times are proper times & not text, then the formula will work.

 

[Luke777]

As long as the times are proper times & not text, then the formula will work.

Works like a charm!

Thank you!

Also much shorter than the formula I was using before

 

[Fluff]

You're welcome & thanks for the feedback.