Counting number of days in several periods ignoring repeating dates
 

Hi there everyone!

There is one issue I cannot solve. Hope to get your help on this.

Basically I have 3 columns:
1) person's name;
2) date of arrival to the country;
3) departure date.

This data is info on employees of a company arriving to the country for the provision of services.

I have such data on several companies. I may need to do 2 things with data on each company depending on the circumstances:

1) I need to check the duration of stay of company's employees in the country in days. However, if 2 or more employees were present at any particular date, this day needs to be counted only once.

2) I need to check whether the duration of the stay of representatives of the company did not exceed 183 days in any consecutive 365-days period (not calendar year).

Thanks in advance.

Cheers.

Could you post some example data?
You will want to swap the real names out for alternatives before you post it.
Just "Name 1", "Name 2" etc will suffice.

There you go, m8.

Date of arrival Date of departure
Person 1 4/9/2011 11/13/2011
Person 2 10/11/2011 4/3/2012
Person 3 2/12/2012 2/18/2012
Person 4 11/29/2011 2/18/2012
Person 5 7/5/2012 9/1/2012
Person 6 9/11/2012 9/19/2012
Person 7 7/7/2012 7/30/2012
Person 8 12/22/2012 2/27/2012
Person 9 3/12/2012 4/19/2012
Person 10 1/14/2011 3/6/2011

Something like this. Hope it helps.

Hi,

To get difference in days, simply subtract the greater date with the lesser one, i.e. Departure Date - Arrival Date. Format it as General so that you see the number not a date. If you wanted working days use the NETWORKDAYS formula.

I didn't quite get your second query. How do you know if 2 people stayed on the same dates?

Hope it helps.

Prashant