Microsoft Excel 2000

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?

Hi, 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.

Regards

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!!!

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

Regards

Humar

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History