number of overlapping minutes between two sets of times

[laborspy]

I'm looking to find a method of finding the amount of overlapping minutes between 2 sets of times. I thought that Max/Min might help but while it will give a number it doesn't appear to be giving the amount of time that is actually overlapping. Meaning 1p-2p and 12:30-1:30p would yield 30mins.

Start Time End Time Start Time End Time Overlap
6:50:00 AM 3:20:00 PM 9:55:00 AM 7:32:00 PM ?

[nimrod]

Assuming your example is in cells A1,B1,C1,D1 the following formula would give you the results you seek:

= TEXT(MIN(B1,D1) - MAX(A1,C1),"h.mm.ss")

OPTION (2) changing hours to minutes and showing the result in minutes use this

= MINUTE(MIN(B2,D2) - MAX(A2,C2)) +( HOUR(MIN(B2,D2) - MAX(A2,C2)) * 60)

[nimrod]

This second attached file shows results in two formats h.m.s and also only in minutes

[laborspy]

Yes! Thank you!!!

[nimrod]

Thanks , if you are happy with my solution please Add to my reputation.