cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Qwerty2002
Regular Visitor

Calculate time spent in each country

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?

DateCityCountry
2/10/2019AUS
2/11/2019BUS
2/14/2019AUS
2/17/2019AGB
2/20/2019BGB
2/25/2019CGB
2/27/2019BGB
2/28/2019AUS
3/1/2019BUS
3/3/2019CUS
3/5/2019DUS
3/6/2019BUS
3/7/2019DUS
3/8/2019AGB
3/9/2019BGB
3/14/2019CGB
3/15/2019AGB
3/17/2019BGB
5 REPLIES 5
Super User I
Super User I

Re: Calculate time spent in each country

Can you confirm the calculation?

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

DateCityCountry
2/10/2019AUS
2/14/2019AUS
2/17/2019AGB
2/28/2019AUS

 

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



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Super User IV
Super User IV

Re: Calculate time spent in each country

Hi,

Please show the exact expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Qwerty2002
Regular Visitor

Re: Calculate time spent in each country

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

Qwerty2002
Regular Visitor

Re: Calculate time spent in each country

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
Regular Visitor

Re: Calculate time spent in each country

Anyone can help? 😞

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors