0

I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country:

Date & time From To Outbound country Inbound country Days in inbound country
10/10/2020 12:45 LGW FCO United Kingdom Italy 36
15/11/2020 17:45 FCO LGW Italy United Kingdom 34
19/12/2020 20:00 LGW FCO United Kingdom Italy 22
10/01/2021 17:45 FCO LGW Italy United Kingdom 153
12/06/2021 20:20 LGW FCO United Kingdom Italy 15
27/06/2021 12:45 FCO LGW Italy United Kingdom 99
04/10/2021 21:35 LTN FCO United Kingdom Italy 13
17/10/2021 14:10 FCO LHR Italy United Kingdom 20
06/11/2021 06:55 LGW FCO United Kingdom Italy 14
20/11/2021 10:20 CIA STN Italy United Kingdom 21
11/12/2021 18:25 LHR FCO United Kingdom Italy 51
31/01/2022 14:05 FCO LHR Italy United Kingdom

I have then created a Pivot Table that summarises the days spent in each country per calendar year:

Year Italy Jordan United Kingdom USA Grand Total
2018 49 5 216 21 291
2019 48 306 15 369
2020 58 307 365
2021 93 293 386
2022 0 0

It seems to be working well. The problem is dealing with the end of the year. I will not take a flight every year on the 1st of January, so there will always be days in January that are counted as previous year's days. For example the days in 2022 are counted as 2021.

Any ideas on how I can deal with the "end of year" problem?

Here's the link to the Google Sheet: https://docs.google.com/spreadsheets/d/1seH0A13AqhY41_mCcgqncHdoCWqU21lDnKQwtz3oCZM/edit?usp=sharing

Thanks!

0

3 Answers 3

1

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.

Pivot


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

Year info


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

Days calculations


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

Query


Pivot Table design

pivot design

2
  • Thank you so much @Tedinoz for the incredibly detailed explanation and the potential solution! I will keep this open just for another couple days then I will accept your answer if there aren't easier ones. Feb 10, 2022 at 4:11
  • GREAT and nice work!
    – Daniele
    Feb 10, 2022 at 8:51
0

I think that your better option is to add a new row when the year change, like the one I added in you sheet, see this picture:

enter image description here

4
  • Two things: 1) what does "IDEM" mean (Columns D & E)? Using your example, wouldn't you need to have "Italy" in Column E? 2) Wouldn't the "departure date" for the inserted row be the first day of the "new year" since it is a continuation of a stay in the same Inbound Country? This would create a split of 21 days (2021) and 30 days (2022).
    – Tedinoz
    Feb 9, 2022 at 22:58
  • Thanks @Daniele I was also thinking to do that initially as a solution - and I could create a custom airport code that would signal a new year... Feb 10, 2022 at 4:13
  • I think you are right @Tedinoz it would need to be the first day of the year Feb 10, 2022 at 4:13
  • @Tedinoz yes, you are correct on both :) my new row was intended only to show its position into the sheet
    – Daniele
    Feb 10, 2022 at 8:50
-1

This is excellent, exactly what I was looking for. I am now trying to adjust it for how many days I'm in each country per financial year 01-Apr to 31-Mar. If you have a moment and could point me in the right direction on how to do this, that would be great,

thanks again, Raj

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.