calculate overlap time between 2 protagonists

[mary128]

Hello

I need your help. I would like to calculate the overlap time spent in the nest between the mother and the father. Moreover the cells are not face to face in time, it means that I can't look at cells by cells to check overlap. I saw some posts with the function "sumproducts" but I was never able to apply it properly to my data. Basically, I should find 56 seconds of overlap in total in this example.
Here is a simple samples of my data.
father father father mother mother mother
nest building nest building
start end duration start end duration
26/12/2012 00:19:03 26/12/2012 00:19:11 8,00 26/12/2012 00:18:59 26/12/2012 00:19:03 4,00
26/12/2012 00:26:45 26/12/2012 00:26:49 4,00 26/12/2012 00:34:23 26/12/2012 00:34:25 2,00
26/12/2012 00:47:51 26/12/2012 00:47:59 8,00 26/12/2012 00:48:01 26/12/2012 00:48:52 51,00
26/12/2012 00:48:06 26/12/2012 00:48:11 5,00 26/12/2012 00:49:02 26/12/2012 00:56:31 449,00
26/12/2012 00:56:26 26/12/2012 00:56:38 12,00 26/12/2012 01:05:08 26/12/2012 01:05:13 5,00

Thank you very much to have a look!sample dataset parental behaviour.xlsx

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[mary128]

Hello

Yes this is just a very very small sample of the data I have. I already manipulate them a bit to report which problems I have in the real dataset. I just need to understand which formula I need to use to apply it to my real dataset.