Add date to existing time based conditional format

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
189
Office Version
  1. 365
Platform
  1. Windows
Hi All,

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

Excel Formula:
=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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=AND(MOD(NOW(),1)>MOD($A4,1),MOD(NOW(),1)<MOD($B4,1))
 
Solution

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
189
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
As long as the times are proper times & not text, then the formula will work.
 

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
189
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,043
Messages
5,835,088
Members
430,340
Latest member
xoxe1337

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top