Calculate time spent in each country

[Qwerty2002]

Hi everyone!

I would need to calculate amount of time which salesperson spent in each country while traveling between different cities. Hardest part is that salesperson can visit one country several times. Idea is to enumerate each visit and then calculate time spent in each country. Any idea how this can be done?

Date	City	Country
2/10/2019	A	US
2/11/2019	B	US
2/14/2019	A	US
2/17/2019	A	GB
2/20/2019	B	GB
2/25/2019	C	GB
2/27/2019	B	GB
2/28/2019	A	US
3/1/2019	B	US
3/3/2019	C	US
3/5/2019	D	US
3/6/2019	B	US
3/7/2019	D	US
3/8/2019	A	GB
3/9/2019	B	GB
3/14/2019	C	GB
3/15/2019	A	GB
3/17/2019	B	GB

[Ashish_Mathur]

Hi,

Please show the exact expected result.

[Qwerty2002]

Ashish, hi

Result should be as follows:

US 1 (first occurence of country) - 7 (2/10->2/17)

GB 1 - 11 (2/17->2/28)

US 2 - 8 (2/28 -> 3/08)

...

An so on

Thanks

[Qwerty2002]

Anyone can help? 😞

[SteveCampbell]

Can you confirm the calculation?

You ask for how long in each country, but there is single entries for date. A, for eaxmple:

Date	City	Country
2/10/2019	A	US
2/14/2019	A	US
2/17/2019	A	GB
2/28/2019	A	US

are we to assume A was in the US from 2/10 - 2/17, and in GB 2/17-2/18 ?

Or is the date counted as one day, so in the example 3 days in US and 1 day in GB

[Qwerty2002]

Steve, hi

You are correct. We assume that A in US was 2/10 - 2/17; GB - GB 2/17-2/28. We also assume that city A in GB is different from city A in US

Thanks