Help with times

Hello Guys, currently I am working on an excel sheet that my boss has asked me to do. The excel table has cells in time format; for example, 00:00:00 for 12 am, and 00:05:00 for 12:05 am. My boss would like the table to highlight the cell that contains 00:00:00 if the current time is between 12:00:00 a.m. and 12:04:59 a.m. And highlight 00:05:00 if the cell is between 12:05:00 a.m. and 12:09:59 a.m.

I understand that somehow this is doable with custom formatting (correct me if I'm mistaken please) However, I have not found the way to make this work. Can you please help me on this? I only know about excel when it comes to finance.

Thanks in advance.

Hi, welcome to the forum

1. Are you sure those are real times and not text looking like real times? (they look like real times as you entered them here, but may not be in the real WB) Test with =isnumber(cell-ref) FALSE indicates text and we will need to convert them.

2. Actually, I think you need Conditional Formatting, not Custom Formatting.

3. What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Fri 28 Sep 2018) is actually 43371

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So you would need to test for would be something like...
=and(A1=0,MOD(NOW(),1)>=0,MOD(NOW(),1)<=0.003460648)

Play around with that and see how you make out?

Excel sees time as a decimal of 1 (day), it then converts it to something we recognize as a time. So really, minutes and seconds dont really come into it at all, unless you want use 5/60/24 to give you 5 minutes as a decimal.

And yes, you would need to apply that to all cells you want to highlight.

Happy to help

Thanks for the kind words and the feedback