Help calculating days spent travelling at / between locations

[jdawg1984]

Hi,

I have a list of travel itineraries with the names / IDs removed for GDPR / confidentiality reasons that I don't have access too.

Each set of rows that are consecutively filled is one person's itinerary. Following a blank row a new persons itinerary starts. I have approx 10 different workbooks each with approx 2000 rows of itineraries as above.

Is there any way to calculate days spent travelling and days spent at specific locations (e.g.in London) for each person using a formula that i don't need to copy / paste thousands of times?

Any help much appreciated.

[TABLE="width: 522"]
<colgroup><col width="87" span="6" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl65, width: 87, align: center"]A[/TD]
[TD="class: xl65, width: 87, align: center"]B[/TD]
[TD="class: xl65, width: 87, align: center"]C[/TD]
[TD="class: xl65, width: 87, align: center"]D[/TD]
[TD="class: xl65, width: 87, align: center"]E[/TD]
[TD="class: xl65, width: 87, align: center"]F[/TD]
[/TR]
[TR]
[TD="class: xl58, bgcolor: green"]Name / Post[/TD]
[TD="class: xl60, bgcolor: green"]Depart.[/TD]
[TD="class: xl63, bgcolor: green"]From[/TD]
[TD="class: xl63, bgcolor: green"]To[/TD]
[TD="class: xl60, bgcolor: green"]Arriv.[/TD]
[TD="class: xl63, bgcolor: green"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]27/12/2007[/TD]
[TD="class: xl59"]Luton airport[/TD]
[TD="class: xl59"]stevenage[/TD]
[TD="class: xl62, align: right"]29/12/2007[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]03/01/2008[/TD]
[TD="class: xl59"]stevenage[/TD]
[TD="class: xl59"]plymouth[/TD]
[TD="class: xl62, align: right"]05/01/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]14/02/2008[/TD]
[TD="class: xl59"]plymouth[/TD]
[TD="class: xl59"]london[/TD]
[TD="class: xl62, align: right"]14/02/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]18/02/2008[/TD]
[TD="class: xl59"]london[/TD]
[TD="class: xl59"]paris[/TD]
[TD="class: xl62, align: right"]18/02/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]23/02/2008[/TD]
[TD="class: xl59"]paris[/TD]
[TD="class: xl59"]Luton airport[/TD]
[TD="class: xl62, align: right"]24/02/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl61"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl61"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]21/11/2007[/TD]
[TD="class: xl59"]London Heathrow[/TD]
[TD="class: xl59"]edinburgh[/TD]
[TD="class: xl62, align: right"]22/11/2007[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]25/11/2007[/TD]
[TD="class: xl59"]edinburgh[/TD]
[TD="class: xl59"]Pitlochry[/TD]
[TD="class: xl62, align: right"]25/11/2007[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]26/11/2007[/TD]
[TD="class: xl59"]Pitlochry[/TD]
[TD="class: xl59"]Pitlochry[/TD]
[TD="class: xl62, align: right"]22/10/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl61"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl61"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]20/11/2007[/TD]
[TD="class: xl59"]London[/TD]
[TD="class: xl59"]Caracas[/TD]
[TD="class: xl62, align: right"]21/11/2007[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]29/11/2007[/TD]
[TD="class: xl59"]caracas[/TD]
[TD="class: xl59"]georgetown[/TD]
[TD="class: xl62, align: right"]26/12/2007[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]29/12/2007[/TD]
[TD="class: xl59"]georgetown[/TD]
[TD="class: xl59"]san jose[/TD]
[TD="class: xl62, align: right"]31/12/2007[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]17/02/2008[/TD]
[TD="class: xl59"]Panama city[/TD]
[TD="class: xl59"]havana[/TD]
[TD="class: xl62, align: right"]18/02/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]21/02/2008[/TD]
[TD="class: xl59"]havana[/TD]
[TD="class: xl59"]miami[/TD]
[TD="class: xl62, align: right"]11/04/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl62, align: right"]12/04/2008[/TD]
[TD="class: xl59"]miami[/TD]
[TD="class: xl59"]London[/TD]
[TD="class: xl62, align: right"]14/04/2008[/TD]
[TD="class: xl59"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[WaterGypsy]

In column F calculate the time spent in location B:

=IF(C2=D1,B2-E1,"")

In Column G calculate the time spent travelling from location B:

=IF(B2="","",E2-B2)

If you need total by location I'd use a pivot table

 

[Fluff]

Cross posted https://www.excelforum.com/excel-ge...-calculating-days-spent-at-each-location.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[jdawg1984]

Apologies, I am new to forum posting in this regard and wasn't aware of the rules, I wasn't sure how it all worked but have received useful replies from both threads.