There must be many ways to arrive at an answer to this question. I don't claim that the methods used in this answer are necessarily the most efficient, but they do work, and someone (maybe the OP) maybe able to improve upon them.
Summary
- Design: The pivot table is created manually (Insert, Pivot Table). A screenshot of the design attributes is included below.
- Source: The source data is a query which consists of six stacked queries
=query({ query({'Entry/exit details'!E2:E51,'Entry/exit details'!G2:G51,'Entry/exit details'!L2:L51}); query({'Entry/exit details'!E2:E51,'Entry/exit details'!G2:G51,'Entry/exit details'!M2:M51}); query({'Entry/exit details'!E2:E51,'Entry/exit details'!G2:G51,'Entry/exit details'!N2:N51}); query({'Entry/exit details'!E2:E51,'Entry/exit details'!G2:G51,'Entry/exit details'!O2:O51}); query({'Entry/exit details'!E2:E51,'Entry/exit details'!K2:K51,'Entry/exit details'!P2:P51}); query({'Entry/exit details'!E2:E51,'Entry/exit details'!G2:G51,'Entry/exit details'!Q2:Q51})}, "select Col1, Col2, Col3 label Col1 'INBOUND COUNTRY', Col2 'YEAR', Col3 'Days'")
- Queries: these represent calculations for specific year/trip combinations:
- #1: The first trip record
- #2: The last trip record
- #3: Trip records where the year is the same as the previous trip and the next trip
- #4: The last trip in any year-the days for the departure year
- #5: The last trip in any year-the days for the following year
- #6: The first trip in any new year
Structure#1-Trip Years
The first step is to identify, for each record, each of three years. This was done by adding additional columns on the "Entry/exit details" sheet.
The year of the trip - =arrayformula(year(A2:A51))
The year of the previous trip - if(isdate(A2)=false,"",year(A2))
- the formula for the first trip is slightly different -
=if(isdate(A1)=false,year(A2),year(A1))
The year of the next trip - =if(isdate(A3)=false,year(A2),year(A3))
Structure#2 - Trip days
The second step is to recognise the six trip elements, and to calculate the number of days that relates to each.
#1 1st record - =ARRAYFORMULA(IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A),1,3)))=2, DATEDIF(A2:A, A3:A, "D"), 0))
#2 Last Record - =ARRAYFORMULA(IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A),1,3)))=COUNTA($A$1:$A), DATEDIF(A2:A, today(), "D"), 0))
#3 Year is same for Year/Last/Next:
=ARRAYFORMULA(IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))=2,0,IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))<COUNTA($A$1:$A),if(ArrayFormula((year(A2:A)=year(A1:A))*(year(A2:A)=year(A3:A))),DATEDIF(A2:A, A3:A, "D"),0))))
#4 Last trip in a year-Days for balance of This Year:
=ARRAYFORMULA(IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))<COUNTA($A$1:$A),IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))=2,0,if(arrayformula((year(A2:A)<>year(A3:A))),datedif(A2:A,eomonth(date(year(A2:A),12,1),0)+1,"D"),0))))
#5 Last in a year-Days till the first trip Next Year:
=ARRAYFORMULA(IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))<COUNTA($A$1:$A),IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))=2,0,if(arrayformula((year(A2:A)<>year(A3:A))),DATEDIF(date(year(A3:A),1,1),A3:A,"D"),0))))
#6 First trip in a Year:
=ARRAYFORMULA(IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))=2,0,IF(row(indirect(address(2,1,3)&":"&address(COUNTA($A$1:A)-1,1,3)))<COUNTA($A$1:$A),if(arrayformula((Year(A2:A)<>Year(A1:A))),DATEDIF(A2:A, A3:A, "D"),0))))
Structure#3 - Linking trip years to trip days
Each the query has three fields:
- The Inbound Country - common for every query.
- A year calculation and a day calculation
- Years and Days are combined as follows:
- 1st Record = Year of trip (Column G)
- Last Record = Year of trip (Column G)
- This Year/Last/Next = Year of trip (Column G)
- Last trip in a year (This Year) = Year of Trip (Column G)
- Last trip in a year (Next Year) = Year of Next Trip (Column K)
- First trip in a Year = Year of Trip (Column G)
An Example
Trip departure: 22 December 2018
Inbound country: Italy
Next departure: 7 January 2019
OP calculation: 16 Days
Breakdown using this answer:
- Last trip in a year (This Year)= 2018 - 10 Days (22/12/2018 to 31/12/2018)
- Last trip in a year (Next Year)= 2019 - 6 days (1/1/2019 to 7/1/2019)
- Total days = 16 but breakdown: 2018=10, 2019=6.
Source
Pivot Table design
