Overlapping Times

[brawnystaff]

I am looking for a formula that will give me the overlapping times for two paired time periods per day, per person.

See attached sample. Figured out how to get total time, but not sure how to get cross-over timeframe in minutes (as shown in Column F). Any ideas? Thanks.

 

[John Jairo V]

Hi, @brawnystaff!

You could try:
[F2] : =1440*MAX(,IF(A2=A3,C2-B3,IF(A2=A1,C1-B2,)))

And drag it down. Blessings!

 

[brawnystaff]

Thanks. But for some reason when I use that formula, for Name "Smith", the first row answer is correct, but subsequent ones for Smith are incorrect. I take it is because Smith has more than one pair in the spreadsheet.

The data I have will have multiple repeating pairs. Is it possible to do this with a SUMPRODUCT or an array formula? Thx..

 

[John Jairo V]

Hi, again!

You must give an real and representative example that what you have in Excel, and what you want to obtain. First you said:

I am looking for a formula that will give me the overlapping times for two paired time periods per day, per person.

And now you say:

I take it is because Smith has more than one pair in the spreadsheet.

The data I have will have multiple repeating pairs.

Your example don't show this new thing. Please, upload your representative example in Excel. Blessings!

 

[brawnystaff]

Sorry...See attached. Thanks.

 

[John Jairo V]

Hi, again!

Check this new formula:
[F2]: =1440*MAX(,IF(MOD(ROWS(A$2:A2),2),IF(A2=A3,C2-B3,),IF(A2=A1,C1-B2,)))

Blessings!

 

[brawnystaff]

Excellent. Worked like a charm!! Thank you..

 

[Peter Bartholomew]

I don't pretend to really understand the purpose of the calculation but, from what you have shown this far, I can't help thinking that the data format represents self-inflicted damage when it comes to processing.

If linked pairs of records were combined into one it would be far easier to perform the calculations, together with any validity checks that may be necessary.