Counting number of days in several periods ignoring repeating dates

[sergeten]

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 fo
the provision of services.

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

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

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

Thanks in advance.

Cheers

 

[Spencer101]

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 fo
the provision of services.

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

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

2) I need to check whether the duration of the stay of representative
of the company did not exceed 183 days in any consecutive 365-day
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 yo
post it.
Just "Name 1", "Name 2" etc will suffice

 

[sergeten]

Could you post some example data?
You will want to swap the real names out for alternatives before yo
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

 

[jack_n_bub]

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 th
lesser one, i.e. Departure Date - Arrival Date. Format it as General s
that you see the number not a date. If you wanted working days use th
NETWORKDAYS formula.

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

Hope it helps.

Prashan