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

[Gritic]

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

[daddylonglegs]

Try like this

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

[Gritic]

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

[Gritic]

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.

Does that look about right?

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

[Gritic]

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