+ Reply to Thread
Results 1 to 10 of 10

Calculate whether time is between two #'s

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Calculate whether time is between two #'s

    All,

    This place has been a great help to me in the past 6 months or so, so I'd like to start by thanking everyone for their help.

    Scenario:
    1. I have the following Dates and Times: ETA and Arrival
    2. I have calculated the difference between the two resulting in [h]:mm

    Example:
    ETA = 4/2/2013 14:00 (A1)
    Arrive = 4/2/2013 19:51 (A2)
    Calc Difference = 5:51 (A3)

    Challenge:
    I am trying to determine if the difference value in column/row A3 above falls within a certain period of time. So for example, does A3 above fall between 2 and 3 hours? If so, Yes, if not, No. Then in A4, we would be moving onto 3 and 4 hour time window with same results, continuing to repeat in the subsequent columns.

    Can someone help me with this? It seems straight forrward but there is something with the formatting that appears to be causing me issues.

    thanks!!!!!

  2. #2
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate whether time is between two #'s

    Ok I think I may have gotten it to work.

    =IF(AND(A3>=--"2:00",A3<=--"3:00"),"Yes","No")

    Sound right?

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate whether time is between two #'s

    Well that didn't work as you can see below:

    ETA - ATA Time Difference 2-3 hours late
    2:00 No
    2:00 Yes
    2:00 No
    2:00 Yes
    2:00 No
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 No
    2:00 Yes
    2:00 No
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 Yes
    2:00 No

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculate whether time is between two #'s

    An alternative idea

    Try
    =HOUR(A2-A1)&"-"&HOUR(A2-A1)+1&" hours"

    this will give you the time span in a single cell itself. Does this work?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate whether time is between two #'s

    I like the idea, but it doesn't look like it worked. See below

    ETA - ATA Time Difference
    5:51 5-6 hours
    288:01 0-1 hours
    0:00 0-1 hours
    43:16 19-20 hours
    0:00 0-1 hours
    0:00 0-1 hours
    3:20 3-4 hours
    0:09 0-1 hours
    20:00 20-21 hours
    16:00 16-17 hours

  6. #6
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculate whether time is between two #'s

    Try

    =IF(A2<=A1,"",INT((A2-A1)*24)&"-"&INT((A2-A1)*24)+1&" hours")

  7. #7
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate whether time is between two #'s

    that should work. thanks!

  8. #8
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate whether time is between two #'s

    Ok, last question

    So now I need to count and report on trends. The above is working well, but I'm not sure it's relevant to count individual hours beyond a certain timeframe.

    So how would I could all those that came back beyond 24 hours? Right now I am counting based on specific returned results
    =COUNTIF($AB23,"0-1 hours")

    If I wanted everything beyond 24 hours would it look like this? I doubt it, but not sure where to start on this one
    =COUNTIF($AB23,"24-**** hours")

  9. #9
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculate whether time is between two #'s

    You could have jsut the initla part of the formula in a column i.e

    =IF(A2<=A1,"",INT((A2-A1)*24)

    then use
    =COUNTIF(Range,">="&24)

  10. #10
    Registered User
    Join Date
    10-15-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate whether time is between two #'s

    cool. the formula only returns the hours, not the minutes (e.g. 5 instead of 5:51), but I think I can still work with this. thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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