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.

A	B	C	D	E	F
Name / Post	Depart.	From	To	Arriv.
	27/12/2007	Luton airport	stevenage	29/12/2007
	03/01/2008	stevenage	plymouth	05/01/2008
	14/02/2008	plymouth	london	14/02/2008
	18/02/2008	london	paris	18/02/2008
	23/02/2008	paris	Luton airport	24/02/2008
	21/11/2007	London Heathrow	edinburgh	22/11/2007
	25/11/2007	edinburgh	Pitlochry	25/11/2007
	26/11/2007	Pitlochry	Pitlochry	22/10/2008
	20/11/2007	London	Caracas	21/11/2007
	29/11/2007	caracas	georgetown	26/12/2007
	29/12/2007	georgetown	san jose	31/12/2007
	17/02/2008	Panama city	havana	18/02/2008
	21/02/2008	havana	miami	11/04/2008
	12/04/2008	miami	London	14/04/2008

<colgroup><col width="87" span="6" style="width: 65pt;"></colgroup><tbody>
</tbody>

 

[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.