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]

Welcome to MrExcel...

Could you tell us what result you would want to see given the above data set?

Matty

 

[chump1901]

Thanks for the quick response Matty.

We want to see how many unique days in a given time frame we've spent at a given location.

For example, we don't want to spend more than 100 days in one year in a location yet we have many people travelling there on different schedules. Using the 'date-date' formula, I get the absolute number of dates but not the 'unique' number of dates. So, if three people spent 10 days there at the same time, I'd like it to just add 10 to the count and not 30, as the date-date formula would do. As of now, we've been doing it manually looking for and removing overlapping days.

I hope I've explained myself correctly- if not please let me know!

 

[Matty]

Thanks for the quick response Matty.

We want to see how many unique days in a given time frame we've spent at a given location.

For example, we don't want to spend more than 100 days in one year in a location yet we have many people travelling there on different schedules. Using the 'date-date' formula, I get the absolute number of dates but not the 'unique' number of dates. So, if three people spent 10 days there at the same time, I'd like it to just add 10 to the count and not 30, as the date-date formula would do. As of now, we've been doing it manually looking for and removing overlapping days.

I hope I've explained myself correctly- if not please let me know!

Could you provide a little more data? I assume there are names linked to the dates already provided?

You can use a HTML maker to post data to the board. See the following link:

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

Matty

 

[chump1901]

Excel 2010
	A	C	D
1	Mark	17-Jan-15	25-Jan-15
2	Craig	17-Jan-15	25-Jan-15
3	David	28-Feb-15	6-Mar-15
4	William	28-Feb-15	7-Mar-15
5	Irene	03-Apr-15	16-May-15
6	David	18-Apr-15	27-Apr-15
7	Newt	26-May-15	02-Jun-15
8	David	05-Jun-15	13-Jun-15
9	William	21-Jun-15	29-Jun-15
10	David	21-Jun-15	27-Jun-15
11	Jeff	19-Jul-15	25-Jul-15
12	David	25-Jul-15	02-Aug-15
13	David	12-Aug-15	20-Aug-15
14	John	21-Aug-15	31-Aug-15
15	Luke	03-Sep-15	17-Sep-15
16	David	07-Sep-15	15-Sep-15
17	David	12-Sep-15	20-Sep-15
18	Mark	13-Sep-15	20-Sep-15
19	David	19-Sep-15	27-Sep-15
20	David	9-Oct-15	27-Oct-15
21	Matt	2-Nov-15	12-Nov-15
22	David	7-Nov-15	19-Nov-15
23	Bill	2-Oct-15	23-Oct-15
Sheet2

There's a sample range of data. With this example, Irene spent most of April there so David's April trip should not be counted in 'unique' days. I also have longer sets of dates (with more overlap) to which this would apply to- let me know if more data would help. It would just take more time to change the names. Again- thanks so much for your help!

 

[Matty]

Hi,

Thanks for the extra data. Could you confirm the expected result(s) as well as detailing how it's been arrived at?

Matty

 

[Matty]

For the record, I get 191 unique dates from this data. Is this the expected result?

Matty

 

[chump1901]

Excel 2010

	B	C	D	E	F	G
Total days	Days overlapped	Total unique days

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Mark[/TD]
[TD="bgcolor: #FFFFFF, align: right"]17-Jan-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]25-Jan-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Craig[/TD]
[TD="bgcolor: #FFFFFF, align: right"]17-Jan-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]25-Jan-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]28-Feb-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]06-Mar-15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]William[/TD]
[TD="bgcolor: #FFFFFF, align: right"]28-Feb-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-Mar-15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Irene[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03-Apr-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]16-May-15[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]18-Apr-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]27-Apr-15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]Newt[/TD]
[TD="bgcolor: #FFFFFF, align: right"]26-May-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]02-Jun-15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]05-Jun-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]13-Jun-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]William[/TD]
[TD="bgcolor: #FFFFFF, align: right"]21-Jun-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]29-Jun-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]21-Jun-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]27-Jun-15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]Jeff[/TD]
[TD="bgcolor: #FFFFFF, align: right"]19-Jul-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]25-Jul-15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]25-Jul-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]02-Aug-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12-Aug-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20-Aug-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]John[/TD]
[TD="bgcolor: #FFFFFF, align: right"]21-Aug-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]31-Aug-15[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]Luke[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03-Sep-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]17-Sep-15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-Sep-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]15-Sep-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12-Sep-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20-Sep-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF"]Mark[/TD]
[TD="bgcolor: #FFFFFF, align: right"]13-Sep-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20-Sep-15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]19-Sep-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]27-Sep-15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]09-Oct-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]27-Oct-15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFFFFF"]Matt[/TD]
[TD="bgcolor: #FFFFFF, align: right"]02-Nov-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12-Nov-15[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFFFFF"]David[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-Nov-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]19-Nov-15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFFFFF"]Bill[/TD]
[TD="bgcolor: #FFFFFF, align: right"]02-Oct-15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]23-Oct-15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/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: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]Sums[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]271[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]191[/TD]

</tbody>

Sheet2​

I confirm we've got the same number! Except I had to go over it 3 times to find errors because I was getting different numbers... hence the need for a better way to do it! The way I've been doing it is like in the spreadsheet above- calculate total days, find any overlaps, add all unique days up. It's always been prone to errors and painstakingly slow.

 

[Matty]

Hi,

The only way I can think of doing this is quite long-winded, requiring helper rows and columns.

If you're still interested in such a solution, post back and I will explain how I did it.

I'm still sure there's a way of doing this without helper rows and columns though. Hopefully one of the gurus will see this thread and be able to offer a more succinct solution!

Matty

 

[chump1901]

Hey Matty,

Thanks a lot for your help. Even your questions have given anyone who checks a better idea of what the problem is.

I'm very open to hearing your solution- could you provide it? Even with helper rows and columns, it may be more efficient than the way I currently do it.