Most effective way to summarise days travelled in calendar years?
Dear all,
I have a table containing a list of individual travel periods that have occurred over a period of several calendar years (the two left hand columns). Some of these travels have started in one calendar year (e.g. 17-12-2023) and ended in the following calendar year (e.g. 06-01-2024). I need a formula to calculate the total number of travel days in each calendar year (i.e. all trips or parts of trips in a particular year (e.g. 2018) and then sums them), based on this data set.
My question is: which formula is best to obtain that number (i.e. total number of travel days in each calendar year, e.g. 2018, 2019, 2020 etc.) from this data set? You can see the number I am trying to get in the below right hand column. ChatGPT failed me.
| Start date | End date | Year | Total Travel days |
|---|---|---|---|
| 29/09/2018 | 14/10/2018 | 2018 | |
| 26/02/2019 | 21/03/2019 | 2019 | |
| 14/06/2019 | 17/06/2019 | 2020 | |
| 26/07/2019 | 4/08/2019 | 2021 | |
| 30/08/2019 | 16/09/2019 | 2022 | |
| 31/10/2019 | 3/11/2019 | 2023 | |
| 23/01/2020 | 17/02/2020 | 2024 | |
| 27/02/2020 | 28/02/2020 | 2025 | |
| 19/06/2021 | 24/08/2021 | 2026 | |
| 7/11/2021 | 9/11/2021 | ||
| 30/03/2022 | 25/04/2022 | ||
| 18/08/2022 | 20/08/2022 | ||
| 1/09/2022 | 5/09/2022 | ||
| 16/12/2022 | 14/01/2023 | ||
| 14/05/2023 | 28/05/2023 | ||
| 13/06/2023 | 16/06/2023 | ||
| 12/10/2023 | 16/10/2023 | ||
| 11/02/2024 | 26/02/2024 | ||
| 12/04/2024 | 14/04/2024 | ||
| 11/07/2024 | 6/08/2024 | ||
| 24/10/2024 | 28/10/2024 | ||
| 17/12/2024 | 20/01/2025 | ||
| 11/06/2025 | 13/06/2025 | ||
| 3/12/2025 | 4/12/2025 | ||
| 15/12/2025 | 6/01/2026 |
[link] [comments]
Want to read more?
Check out the full article on the original site