+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    11-27-2010
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    4

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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))
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-27-2010
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    Quote Originally Posted by daddylonglegs View Post
    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. #4
    Registered User
    Join Date
    11-27-2010
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    Quote Originally Posted by daddylonglegs View Post
    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 ::
    Last edited by Gritic; 11-28-2010 at 06:42 PM. Reason: hit the wrong cell when editing before..

  5. #5
    Registered User
    Join Date
    11-27-2010
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    Quote Originally Posted by daddylonglegs View Post
    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 ::

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1