Formula To Calculate Dates That Overlap

MrMaker

New Member
Joined
Jun 7, 2018
Messages
15
Morning all,

Another little piece of help required please, this time regarding dates/times that overlap.

Example Data:

I need to calculate the number of days overlapping within the data for each unique Name.

Name (A)
Start (B)
End (C)
Dave
08/02/2018 09:00
08/02/2018 11:00
Dave
08/02/2018 09:00
08/02/2018 12:00
Tom
08/02/2018 09:30
08/02/2018 15:30

<tbody>
</tbody>

So in the above example the expected outcome for Dave's total overlap would equal 0.0833 (or in other words 2 hours).

Thanks in advance

MM
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
I have this formula for overlapped time but this is just for the same day

Overlapped time (same day)
A1 B1 Start Finish Range 1
D1 E1 Start Finish Range 2
=MAX(0,MIN(B1,E1)-MAX(A1,D1)+1)
 

MrMaker

New Member
Joined
Jun 7, 2018
Messages
15
Ah thanks SK, sadly my date ranges will be all over the place........the search continues
 

MrMaker

New Member
Joined
Jun 7, 2018
Messages
15
If there are any other potential solutions or workarounds I’m all ears………….:)

Thanks very much
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top