Multiple Start End Dates Overlapping Count Days

[shodagad]

Hello,

I couldn't make it happens so far so I decided to get your help in order to find formula to do the job automatically.

below table is displaying all the delays for a service in days so I want to calculate the stoppable days excluding overlap days.

by calculating it manual:
Total days (Max-Min) 67 days
Total overlapping days 21 days (from 14-May till 1-Jun & From 13-Jun till 14-Jun)
Total free days with no delays 2 days (from 5-Jul till 6-Jul)

Total Stoppable days = 67-2
[TABLE="class: grid, width: 100%"]
<tbody>[TR]
[TD]Start1[/TD]
[TD]End1[/TD]
[TD]Start2[/TD]
[TD]End2[/TD]
[TD]Start3[/TD]
[TD]End3[/TD]
[TD]Start4[/TD]
[TD]End4[/TD]
[TD]Start5[/TD]
[TD]End5[/TD]
[TD]Start6[/TD]
[TD]End6[/TD]
[TD]Start7[/TD]
[TD]End7[/TD]
[/TR]
[TR]
[TD]5-May[/TD]
[TD]1-Jun [/TD]
[TD]14-May [/TD]
[TD]14-Jun [/TD]
[TD]13-Jun[/TD]
[TD]4-Jul[/TD]
[TD]13-Jun[/TD]
[TD]4-Jul[/TD]
[TD]13-Jun[/TD]
[TD]4-Jul[/TD]
[TD]13-Jun[/TD]
[TD]4-Jul[/TD]
[TD]7-Jul[/TD]
[TD]10-Jul[/TD]
[/TR]
</tbody>[/TABLE]

 

[TonyUK72]

I've sent you a PM with the formula in, let me know if you receive the message with formula.

NOTE: the formula works (when there is a negative date value returned eg row 2 & 6) but it currently subtracts 1 day on rows 3,4 & 5. But it's close.

 

[Marcelo Branco]

@Marcelo Branco:

I've tried and yes it works but it doesn't end with the needed result.

I attached on Gdrive sample with the same setup of the sheet with different scenarios that could be happened.

it would be great to find the solution as currently I don't exclude the overlap days. only calculating MAX-MIN which is wrong.

Stoppage.xlsx - Google Drive

It's very hard to create a formula with such data setup. Anyway, see if this does what you need.
Dates dd/mm/yyyy

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

User Ref.​

[/TD]
[TD]

Start Date1​

[/TD]
[TD]

End Date1​

[/TD]
[TD]

Start Date2​

[/TD]
[TD]

End Date2​

[/TD]
[TD]

Start Date3​

[/TD]
[TD]

End Date3​

[/TD]
[TD]

Start Date4​

[/TD]
[TD]

End Date4​

[/TD]
[TD]

Start Date5​

[/TD]
[TD]

End Date5​

[/TD]
[TD]

Start Date6​

[/TD]
[TD]

End Date6​

[/TD]
[TD]

Start Date7​

[/TD]
[TD]

End Date7​

[/TD]
[TD]

Stoppage Days​

[/TD]
[TD]

Free Days​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

SH0051​

[/TD]
[TD]

05/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD]

14/05/2017​

[/TD]
[TD]

14/06/2017​

[/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD]

09/07/2017​

[/TD]
[TD]

14/07/2017​

[/TD]
[TD]

09/07/2017​

[/TD]
[TD]

10/07/2017​

[/TD]
[TD]

65​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

SH0052​

[/TD]
[TD]

05/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

27​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

SH0053​

[/TD]
[TD]

05/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

48​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

SH0054​

[/TD]
[TD]

05/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD]

14/05/2017​

[/TD]
[TD]

14/06/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

40​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

SH0055​

[/TD]
[TD]

05/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD]

14/05/2017​

[/TD]
[TD]

29/05/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

27​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in Q2 copied down (gets the free days)
=SUMPRODUCT(--(SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:N2)-COLUMN(D2)+1))>SUBTOTAL(4,OFFSET(C2,,,,COLUMN(C2:M2)-COLUMN(C2)+1))),ISNUMBER(SEARCH("Start",D$1:N$1))*SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:N2)-COLUMN(D2)+1))-ISNUMBER(SEARCH("End",C$1:M$1))*SUBTOTAL(4,OFFSET(C2,,,,COLUMN(C2:M2)-COLUMN(C2)+1)))

Formula in P2 copied down
=MAX(B2:O2)-MIN(B2:O2)-Q2

M.

 

[TonyUK72]

=MAX(B2:O2)-MIN(B2:O2)-SUMPRODUCT(--(SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:N2)-COLUMN(D2)+1))>SUBTOTAL(4,OFFSET(C2,,,,COLUMN(C2:M2)-COLUMN(C2)+1))),ISNUMBER(SEARCH("Start",D$1:N$1))*SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:N2)-COLUMN(D2)+1))-ISNUMBER(SEARCH("End",C$1:M$1))*SUBTOTAL(4,OFFSET(C2,,,,COLUMN(C2:M2)-COLUMN(C2)+1)))

This as a combined formula is way way shorter than the one I created.

Not that it makes any sense to me Mr Branco.

 

[shodagad]

@TonyUK72
@Marcelo Branco

You guys made my day absolutely nothing to express the much support you give regarding this request it could be used in my daily work forever.

Thank you so much and I hope one day I could return it back

 

[shodagad]

I just added a new line as follows, if we calculate it manual the result is 10 stoppage days (exclude overlap dates 4-jun till 6-jun)

in the shortest formula it gives 29 days it didn't realize that from 13-jun till 2-jul there was no stoppage days.

but the good news here the long formula that Mr. Toni sent it PM It gives the correct value which is 10 days (Actually it gives less 1 number in all lines so I added +1 to fix it)
[TABLE="class: grid, width: 1647"]
<tbody>[TR]
[TD]User Ref.[/TD]
[TD]Start Date1[/TD]
[TD]End Date1[/TD]
[TD]Start Date2[/TD]
[TD]End Date2[/TD]
[TD]Start Date3[/TD]
[TD]End Date3[/TD]
[TD]Start Date4[/TD]
[TD]End Date4[/TD]
[TD]Start Date5[/TD]
[TD]End Date5[/TD]
[TD]Start Date6[/TD]
[TD]End Date6[/TD]
[TD]Start Date7[/TD]
[TD]End Date7[/TD]
[TD]M.Branco
Stoppage Days[/TD]
[TD]M.Branco
Free Days[/TD]
[TD]Tony
Private msg[/TD]
[/TR]
[TR]
[TD]SH0056[/TD]
[TD]2-Jul-17[/TD]
[TD]3-Jul-17[/TD]
[TD]4-Jun-17[/TD]
[TD]13-Jun-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6-Jun-17[/TD]
[TD]13-Jun-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]29[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="3"><col><col><col><col><col span="2"><col><col><col><col><col><col></colgroup>[/TABLE]

=MAX(B2:O2)-MIN(B2:O2)-SUMPRODUCT(--(SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:N2)-COLUMN(D2)+1))>SUBTOTAL(4,OFFSET(C2,,,,COLUMN(C2:M2)-COLUMN(C2)+1))),ISNUMBER(SEARCH("Start",D$1:N$1))*SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:N2)-COLUMN(D2)+1))-ISNUMBER(SEARCH("End",C$1:M$1))*SUBTOTAL(4,OFFSET(C2,,,,COLUMN(C2:M2)-COLUMN(C2)+1)))

This as a combined formula is way way shorter than the one I created.

Not that it makes any sense to me Mr Branco.

 

[Marcelo Branco]

I just added a new line as follows, if we calculate it manual the result is 10 stoppage days (exclude overlap dates 4-jun till 6-jun)

in the shortest formula it gives 29 days it didn't realize that from 13-jun till 2-jul there was no stoppage days.

[TABLE="class: grid, width: 1647"]
<tbody>[TR]
[TD]User Ref.[/TD]
[TD]Start Date1
[/TD]
[TD]End Date1[/TD]
[TD]Start Date2[/TD]
[TD]End Date2[/TD]
[TD]Start Date3[/TD]
[TD]End Date3[/TD]
[TD]Start Date4[/TD]
[TD]End Date4[/TD]
[TD]Start Date5[/TD]
[TD]End Date5[/TD]
[TD]Start Date6[/TD]
[TD]End Date6[/TD]
[TD]Start Date7[/TD]
[TD]End Date7[/TD]
[TD]M.Branco
Stoppage Days[/TD]
[TD]M.Branco
Free Days[/TD]
[TD]Tony
Private msg[/TD]
[/TR]
[TR]
[TD]SH0056[/TD]
[TD]2-Jul-17[/TD]
[TD]3-Jul-17[/TD]
[TD]4-Jun-17[/TD]
[TD]13-Jun-17[/TD]
[TD][/TD]
[TD][/TD]
[TD]6-Jun-17[/TD]
[TD]13-Jun-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

In all the examples you showed the start dates were in ascending order, ie, Start Date 2>= Start Date 1, Start Date 3>= Start Date 2 and so on. So I assumed this would always happen - makes perfect sense.
This example, however, is not so, so my formula did not work correctly.
It is always important to present all possible scenarios, otherwise the suggested solutions will not meet the requirements.
I'm glad you found a solution to the problem.

M.

 

[shodagad]

Hi Marcelo,

Yes you're right, I'm currently using your formula after set fixed start/end dates in the first Start and first End with the lowest value.

I think it works fine in all scenarios faced so far. thanks for help

In all the examples you showed the start dates were in ascending order, ie, Start Date 2>= Start Date 1, Start Date 3>= Start Date 2 and so on. So I assumed this would always happen - makes perfect sense.
This example, however, is not so, so my formula did not work correctly.
It is always important to present all possible scenarios, otherwise the suggested solutions will not meet the requirements.
I'm glad you found a solution to the problem.

M.