Monitor date and time range

**smcelv1** · 06-12-2012, 02:34 PM

Hi guys
Need a formula to return a true or false response if the "current" date is within a time range between 14:00 and 06:30 - daily.

Can anyone please help?

**ChemistB** · 06-12-2012, 02:42 PM

How about just setting up either row 1 or cell 1 (A1) to be green only when the current time is within that range? Does that sound like what you want?

14:00 to 6:30

so any time before 6:30 AM or after 2:00 PM?

**smcelv1** · 06-12-2012, 03:03 PM

I think that would work.
Its just that I also was wanting a comparison with the "true or false" return if say cell a2 contained 63
ie if now() is between 14:00 and 06:30 AND A2=63 then . . .then cell could be blue.

Please excuse that feeble attempt to explain this. Hope it makes sense to you?

Thank you

**ChemistB** · 06-12-2012, 03:14 PM

Got it. The key is you want to strip the date off the NOW() function and just have the time (strangely MS didn't create a simple TIME function). Since days are represented by whole numbers;

=MOD(NOW(),1) returns the current time. So, IF(OR(MOD(NOW(),1)<("6:30"*1),MOD(NOW(),1)>("14:00"*1)) returns a TRUE/FALSE
NOW is volatile so will recalculate whenever the sheet recalculates but won't if the sheet is just sitting there.

**smcelv1** · 06-12-2012, 03:37 PM

Thank you so very much.
Excel said the formula was missing a parenthesis but I found it.

IF(OR(MOD(NOW(),1)<("6:30"*1)),MOD(NOW(),1)>("14:00"*1))

It is delivering a False statement right now - I presume that will change to True at 6:30 a.m. tomorrow?