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?
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
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!
.
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.
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 08:14 PM.
Audere est facere
Oddly I receive 0:00 as an output.
Are you using Exactly that formula?
Audere est facere
I copied and pasted =MAX(0,MIN(C4,C5)-MAX(B4,B5)) in B6 and the output (B6) displays 0:00
I confirmed there were no leading or trailing spaces as well.
I am using Excel 2007 in case that makes a difference.
Works OK for me, see attached
Audere est facere
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))
Audere est facere
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.
Thank you very much for your help and patience!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks