cancel
Showing results for
Did you mean:
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?

 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
5 REPLIES 5
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:

 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

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

Connect with me!

Super User IV

## Re: Calculate time spent in each country

Hi,

Please show the exact expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

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

Regular Visitor

## Re: Calculate time spent in each country

Anyone can help? 😞

Announcements

#### Announcing the New Spanish Forum

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

#### 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

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors