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]

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe...
I rearranged your data to make things easier
Dates as 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]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Start​

[/TD]
[TD]

End​

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

Stoppable days​

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

2​

[/TD]
[TD]

05/05/2017​

[/TD]
[TD]

01/06/2017​

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

65​

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

3​

[/TD]
[TD]

14/05/2017​

[/TD]
[TD]

14/06/2017​

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

4​

[/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

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

5​

[/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

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

6​

[/TD]
[TD]

13/06/2017​

[/TD]
[TD]

04/07/2017​

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

7​

[/TD]
[TD]

07/07/2017​

[/TD]
[TD]

10/07/2017​

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


Formula in D2
=MAX(A2:B7)-MIN(A2:B7)+1-SUMPRODUCT(--(A3:A8>B2:B7),A3:A8-B2:B7-1)

Hope this helps

M.

 

[shodagad]

Hi Marcelo,

I tried your formula but it gives #VALUE !?

 

[Marcelo Branco]

Hi Marcelo,

I tried your formula but it gives #VALUE !?

Check the ranges. The formula worked perfectly for me.

M.

 

[shodagad]

@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

 

[TonyUK72]

Is the answer for the first row 60

 

[shodagad]

Is the answer for the first row 60

I added the answer in column Stoppage Days
[TABLE="width: 650"]
<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]Stoppage Days[/TD]
[/TR]
[TR]
[TD]SH0051[/TD]
[TD]5/5/2017[/TD]
[TD]01/06/17[/TD]
[TD]14/05/17[/TD]
[TD]14/06/17[/TD]
[TD]13/06/17[/TD]
[TD]04/07/17[/TD]
[TD]13/06/17[/TD]
[TD]04/07/17[/TD]
[TD]13/06/17[/TD]
[TD]04/07/17[/TD]
[TD]09/07/17[/TD]
[TD]14/07/17[/TD]
[TD]09/07/17[/TD]
[TD]10/07/17[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]SH0052[/TD]
[TD]5/5/2017[/TD]
[TD]01/06/17[/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]
[/TR]
[TR]
[TD]SH0053[/TD]
[TD]5/5/2017[/TD]
[TD]01/06/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13/06/17[/TD]
[TD]04/07/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]SH0054[/TD]
[TD]5/5/2017[/TD]
[TD]01/06/17[/TD]
[TD]14/05/17[/TD]
[TD]14/06/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]SH0055[/TD]
[TD]5/5/2017[/TD]
[TD]01/06/17[/TD]
[TD]14/05/17[/TD]
[TD]29/05/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27[/TD]
[/TR]
</tbody>[/TABLE]

 

[TonyUK72]

<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")[ code]
<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0") [="" code]
<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
** EDITED ** for some reason it will not show the entire formula!!!! It's huge.
There HAS to be an easier way!!!!!</c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
</c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
</c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")></c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")[>

 

[shodagad]

<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")[ code]
<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0") code]
<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
** EDITED ** for some reason it will not show the entire formula!!!! It's huge.
There HAS to be an easier way!!!!!</c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
</c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")
</c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")></c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))+if(min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))<0,1+min(days360(b2,c2),if(d2="","0",if(d2<c2,days360(c2,e2),days360(d2,e2))),if(f2="","0",if(f2<e2,days360(e2,g2),days360(f2,g2))),if(h2="","0",if(h2<g2,days360(g2,i2),days360(h2,i2))),if(j2="","0",if(j2<i2,days360(i2,k2),days360(j2,k2))),if(l2="","0",if(l2<k2,days360(k2,m2),days360(l2,m2))),if(n2="","0",if(n2<m2,days360(m2,o2),days360(n2,o2))))*-1,"0")[>

Can we make it works on at least three Start/End Dates?

 

[TonyUK72]

Can we make it works on at least three Start/End Dates?

I'll try and use two dates, the 2nd dates (Col D & Col E) is the formula you would need to use to build the rest.