+ Reply to Thread
Results 1 to 14 of 14

H:MM of intersecting times between two time ranges

  1. #1
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question H:MM of intersecting times between two time ranges

    How do I calculate the number of hours and minutes that two time ranges intersect?

    Example:

    A | B | C
    Name | Start | Stop
    Bob | 4:00am | 3:30pm
    Sue | 8:00am | 7:00pm


    What formula would calculate how many hours and minutes these two people worked together?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: H:MM of intersecting times between two time ranges

    Do the shifts always finish before midnight? If so then assuming valid time values in B2:C3 try

    =MAX(0,MIN(C2,C3)-MAX(B2,B3))
    Audere est facere

  3. #3
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    Thank you even though I am still just a little confused. Let me give you an exact time range and cell values I am working with.
    A | B | C
    row 3 Name | Start | Stop
    row 4 Bob | 5:39 AM | 5:01 PM
    row 5 Sue | 4:00 AM | 4:07 PM


    Thanks very much for your help!


    .

  4. #4
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    Oh yeah, the times always end before midnight. Does the am pm indicators play a role in your formula? I asked based of the fact that a schedule is often just over 12 hours.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: H:MM of intersecting times between two time ranges

    OK - AM and PM are fine as long as the cells contain valid times - same formula but adjusted for your cell references, i.e.

    =MAX(0,MIN(C4,C5)-MAX(B4,B5))

    Format result cell as h:mm and that should give 10:28 for your example, i.e. the overlap between 5:39 AM and 4:07 PM

    Edit: just realised that you posted this is "tips and tutorials" - that's not a question formum - moved to Excel General
    Last edited by daddylonglegs; 12-18-2011 at 09:14 PM.

  6. #6
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    Oddly I receive 0:00 as an output.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: H:MM of intersecting times between two time ranges

    Are you using Exactly that formula?

  8. #8
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    I copied and pasted =MAX(0,MIN(C4,C5)-MAX(B4,B5)) in B6 and the output (B6) displays 0:00

  9. #9
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    I confirmed there were no leading or trailing spaces as well.

  10. #10
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    I am using Excel 2007 in case that makes a difference.

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: H:MM of intersecting times between two time ranges

    Works OK for me, see attached
    Attached Files Attached Files

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: H:MM of intersecting times between two time ranges

    It occurs to me that your "times" might be text rather than true times (in which case zero would be the probable result). You can tell if you have valid times. If you do then they are counted by COUNT function so

    =COUNT(B4:C5)

    should give a result of 4 (4 times). If it doesn't then they aren't valid times. In which case this version of the formula might work

    =MAX(0,MIN(C4+0,C5+0)-MAX(B4+0,B5+0))

  13. #13
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    Ok, comparing the two tables I see the solution even though I'm not sure why. In your example, your times include the seconds values (5:39:00) and mine did not. When I add them it works great.

  14. #14
    Registered User
    Join Date
    12-18-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: H:MM of intersecting times between two time ranges

    Thank you very much for your help and patience!

+ 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