# Excel 2007 : Time falling between two times and accounting for midnight..

1. ## Time falling between two times and accounting for midnight..

So I got stuck and have spent some time trying to figure this out. Any help would be very appreciated.

How can I determine if a given time falls between two other given times while also taking into consideration midnight?

Example would be as follows:

A1 : 10:00 PM
A2: 7:00 AM

A3: 1:00 AM

How can I determine if A3 falls between A1 and A2?

I am trying to see if I can get a "1" for true "0" for false.

Here is what I had before I was compensating for the midnight divide:

``Please Login or Register  to view this content.``

This was my last attempt:

``Please Login or Register  to view this content.``

And finally maybe you want to know why I am doing this. I am trying to show when people are working at a certain time range and add them as a value of 1. This will assist in showing the coverage we have of people every 15 minute.

Thank you for any suggestions you may have,

-Che

2. ## Re: Time falling between two times and accounting for midnight..

Try like this

=IF(A1="",0,IF((A3>=A1)+(A3<=A2)+(A2<A1)=2,1,0))

3. ## Re: Time falling between two times and accounting for midnight..

Try like this

=IF(A1="",0,IF((A3>=A1)+(A3<=A2)+(A2<A1)=2,1,0))
Thanks! I will give it a try.. I was just logging on to add this..

Okay.. so here it is..

A1 = Start B1= End C1 = Given Time

True
A1 11 PM
B1 7 AM
C1 11:45 PM

or

True
A1 1 PM
B1 10 AM
C1 7 PM

or

False
A1 11 AM
B1 7 PM
C1 8 PM

This is the first attempt that worked kind of like I wanted it to.

``Please Login or Register  to view this content.``
I will break it down as this is bit of a monster and there might be some people out there that can help me clean it up...

The \$ are to lock the cell letter and cell number while copying and pasting.

This is to return a blank if there is nothing in the target cell:

=IF(\$A\$1="","0",

This is to check to see if both times fall within the normal 24 hour period (not after midnight) and to see if the selected time falls between both times:

(IF(AND(\$A\$1<\$B\$1,\$A\$1<=C1,C1<=\$B\$1),"1",

This is to check to see if the start time is "greater" than the end time.
(IF(\$A\$1>\$B\$1,

If the the time seems to include midnight this will return a true value:
(IF(AND((\$A\$1+0.5))<=(C1+0.5),(C1)<=(\$B\$1+1)),"1","0")),"0")))))

I am sure there is some stuff in here that isn't needed but as I said before it worked out to do what I was looking for.

Just a side note if you are looking to show this on a second day the times you are comparing against have to have a +1 to register.

That is it for now.. if you have any suggestions I would love to hear them..

Thanks,

:: Gritic ::

4. ## Re: Time falling between two times and accounting for midnight..

Try like this

=IF(A1="",0,IF((A3>=A1)+(A3<=A2)+(A2<A1)=2,1,0))
Okay, So I was playing around with this and the only thing I had to add was accounting for a starting time of midnight.

``Please Login or Register  to view this content.``

I honestly don't quite understand the logic of it yet.. but I am working on it. I think what I don't understand are the "+" and the "=2" in the last IF statement.

Thanks btw DDL.. it works great!

:: Gritic ::

5. ## Re: Time falling between two times and accounting for midnight..

Try like this

=IF(A1="",0,IF((A3>=A1)+(A3<=A2)+(A2<A1)=2,1,0))
Last modification to account to include the time that is provided as true:

``Please Login or Register  to view this content.``
You made it clean and elegant DLL! Thank you so much for your help!

:: Gritic ::

There are currently 1 users browsing this thread. (0 members and 1 guests)