Days on the Ground- Calc days spent without overlapping

[chump1901]

Hi all,

First, thanks for reading this and trying to help!

Background: I have different travelers going to the same location at different dates. Sometimes these dates overlap, sometimes they don't. I'm looking for a formula that will calculate the number of 'unique' days spent in a location between different ranges. My data is currently in the following format:

Departure date - Return date

e.g

17-Jan-15	25-Jan-15
17-Jan-15	25-Jan-15
28-Feb-15	6-Mar-15
28-Feb-15	7-Mar-15
03-Apr-15	16-May-15
18-Apr-15	27-Apr-15
26-May-15	02-Jun-15
05-Jun-15	13-Jun-15
21-Jun-15	29-Jun-15
21-Jun-15	27-Jun-15

<colgroup><col><col></colgroup><tbody>
</tbody>

As you can see, there is some short overlaps and there may be some extended overlaps as well that cross months. Most formulas I`ve seen only compare the current (e.g line 47) with the next one (line 48) to check for overlap.

Thanks so much!

 

[Matty]

Hi,

Here's how I did it...

Data as follows:

	B	C	D	E	F	G	H	I	J
Data
Name	Start Date	End Date	Day Count	Date 1	Date 2	Date 3	Date 4	Date 5
Mark
Craig
David

<tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]17/01/2015[/TD]
[TD="align: right"]19/01/2015[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17/01/2015[/TD]
[TD="align: right"]18/01/2015[/TD]
[TD="align: right"]19/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18/01/2015[/TD]
[TD="align: right"]22/01/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18/01/2015[/TD]
[TD="align: right"]19/01/2015[/TD]
[TD="align: right"]20/01/2015[/TD]
[TD="align: right"]21/01/2015[/TD]
[TD="align: right"]22/01/2015[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20/01/2015[/TD]
[TD="align: right"]23/01/2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20/01/2015[/TD]
[TD="align: right"]21/01/2015[/TD]
[TD="align: right"]22/01/2015[/TD]
[TD="align: right"]23/01/2015[/TD]
[TD="align: right"][/TD]

</tbody>

Formulas used here:

E5, copied down:

=D5-C5+1

F5, copied down and across:

=IF(COLUMNS($F5:F5)>$E5,"",INDEX(ROW(INDIRECT($C5&":"&$D5)),COLUMNS($F5:F5)))

Calculation:

	B	C	D	E	F
Calculation
Date Count	Date List	Unique Dates	Unique Date Count	Unique Date Count (2)

<tbody>
[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17/01/2015[/TD]
[TD="align: right"]17/01/2015[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18/01/2015[/TD]
[TD="align: right"]18/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18/01/2015[/TD]
[TD="align: right"]19/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]19/01/2015[/TD]
[TD="align: right"]20/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]19/01/2015[/TD]
[TD="align: right"]21/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20/01/2015[/TD]
[TD="align: right"]22/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20/01/2015[/TD]
[TD="align: right"]23/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]21/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]21/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"]23/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Formulas used here:

B12:

=SUM(E5:E7)

C12, copied down:

=IF(ROWS(C$12:C12)>B$12,"",SMALL(F$5:J$7,ROWS(C$12:C12)))

D12, committed with CTRL+SHIFT+ENTER and then copied down:

=IFERROR(INDEX(C$12:C$31,MATCH(0,COUNTIF(D$11:D11,C$12:C$31),0)),"")

E12:

=COUNT(D12:D31)

If you want to avoid the formula used in D12:D31 (it will be quite slow on a big data set), then use the following formula to skip this step:

=SUM(IF(FREQUENCY(IF(C12:C31<>"",MATCH(C12:C31,C12:C31,0)),ROW(C12:C31)-ROW(C12)+1),1))

Committed with CTRL+SHIFT+ENTER (the result is shown in F12).

I'm sure they'll be a way of doing this 'in memory' with a single formula, but I can't figure out how to get the matrix of dates into a single array in order to calculate the unique count. I'm hoping someone else sees this and can enlighten us on how to do it.

Hope this helps.

Matty

 

[chump1901]

Thanks a lot Matty. It definitely does need a lot of dummy columns but it works! It's for sure better than how I had been doing it so it's a definite plus. Thanks for the hard work and hopefully we get someone around here who can figure it out in a single array.

 

[chump1901]

Bump- hopefully someone can take this on for a simpler solution!

 

[Matty]

Hi chump1901,

I did check with one of the MVPs on here in case I'd missed something obvious.

Unfortunately, it would appear that it isn't possible to escape the intermediate processing in order to achieve the desired result.

Matty