Hi All,
I am using the following formula in conditional formatting to highlight relevant cells.
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!
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!