Converting negative times

Microsoft Excel 2000
June 1, 2010 at 15:08:23
Specs: Windows XP
I'm trying to write a formula that will tell me if a given time falls between two different time ranges. The two time ranges are 10AM-2PM (Range 1) and 11PM-3AM (Range 2). I have tried the following formula from various forums, but for times after midnight, no results show for Range 2.

=IF(AND(MOD(B3,1)>--$C$2,MOD(B3,1)<--$D$2),"Range 1","")

B3 = 10:35:00 AM Sample time for comparison
C2 = 10:00:00 AM Range 1 (low)
D2 = 2:00:00 PM Range 1 (high)

So for example, the time for comparison is 10:35:00 AM. The above formula will generate "Range 1". However, if the time for comparison is 11:59:00 PM and C2=11:00:00 PM and D2=3:00:00 AM, the formula is true when comparing the time to C2; however, it evaluates to false when compared to D2. Why? How do I write a formula to compare the sample time to the two ranges?

See More: Converting negative times

June 1, 2010 at 16:56:49

I put the four times making up your two ranges into cells C2, D2, E2 and F2 as follows:

1	C	D	E	F
2	10:00	14:00	23:00	03:00

In cell B3 I entered the time to be tested and in cell C3 I used this formula: =IF(AND(B3>=$C$2,B3<=$D$2),"Range1",IF(OR(B3>=$E$2,B3<=$F$2),"Range2","Neither"))

The result was that
times from midnight to 3AM showed 'Range2'
times form 3AM to 10AM showed 'Neither'
times from 10AM to 2PM showed 'Range1'
times from 2PM to 11PM showed 'Neither'
and times from 11PM to midnight showed 'Range2'

Hope this helps.


Report •

June 1, 2010 at 20:43:53
Terrific Humar!!!
Thanks for rescuing me from two days spent trying to figure out how to handle the time after midnight when Excel sees it as a new day (negative time). The formula worked perfectly.

Have a good evening!!!

Report •

June 2, 2010 at 04:19:33

Thanks for the feedback. Glad to hear the formula worked.



Report •
Related Solutions

Ask Question