Rolling number of days out in 365 day period, for UK citizenship calculation

[dkuz]

Hi, I travel extensively for work and want to keep track of my days outside of UK for the citizenship application later on. I have the dates for the last 3 years already and want to add additional column, calculating the rolling days I'm out of the country within any 365 day period.
The data I have currently is Column B - departure date; Column C - arrival date; Column D - days outside UK on that trip. I want column F to show the rolling days within the 365 day period, and am completely lost how to arrange the formula.
Can you help please?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMIFS(D$4:D4,B$4:B4,">"&D4-365)

 

[dkuz]

Hi & welcome to MrExcel.
How about

=SUMIFS(D$4:D4,B$4:B4,">"&D4-365)

Hi,
Thank you!
This just gives me continuous cumulative days away.
I think the formula needs to have a condition set around current date and last calendar year within each data but that's where I am getting confused.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[dkuz]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Schedule of Absence template 2020(81).xlsx
	B	C	D		F
7	Departure	Arrival	Trip length		Rolling 12 months
8			n/a
9	11/21/20	11/30/20	8
10	1/20/21	2/11/21	21
11	2/11/21	4/5/21	52
12	7/27/21	8/30/21	33
13	9/18/21	9/25/21	6
14	11/7/21	11/13/21	5
15	2/11/22	4/2/22	49
16	4/27/22	5/4/22	6
17	5/23/22	5/31/22	7
18	7/10/22	7/15/22	4
19	7/17/22	8/31/22	44
20	10/27/22	11/4/22	7
21	11/23/22	12/19/22	25
22	1/15/23	1/25/23	9
23	3/5/23	3/21/23	15
24	4/2/23	4/27/23	24
Dates (edit this page)

Cell Formulas
Range	Formula
D9:D24	D9	=MAX(0,DATEDIF(B9,C9,"d")-1)

 

[dkuz]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Posted above!

 

[Fluff]

Thanks for that. Got the last bit wrong, it should be

Fluff.xlsm
	B	C	D	E	F
7	Departure	Arrival	Trip length		Rolling 12 months
8			n/a
9	21/11/2020	30/11/2020	8		8
10	20/01/2021	11/02/2021	21		29
11	11/02/2021	05/04/2021	52		81
12	27/07/2021	30/08/2021	33		114
13	18/09/2021	25/09/2021	6		120
14	07/11/2021	13/11/2021	5		125
15	11/02/2022	02/04/2022	49		93
16	27/04/2022	04/05/2022	6		99
17	23/05/2022	31/05/2022	7		106
18	10/07/2022	15/07/2022	4		110
19	17/07/2022	31/08/2022	44		154
20	27/10/2022	04/11/2022	7		122
21	23/11/2022	19/12/2022	25		142
22	15/01/2023	25/01/2023	9		151
23	05/03/2023	21/03/2023	15		117
24	02/04/2023	27/04/2023	24		141
Main

Cell Formulas
Range	Formula
D9:D24	D9	=MAX(0,DATEDIF(B9,C9,"d")-1)
F9:F24	F9	=SUMIFS(D$9:D9,B$9:B9,">"&B9-365)

 

[dkuz]

Thanks for that. Got the last bit wrong, it should be

Fluff.xlsm
	B	C	D	E	F
7	Departure	Arrival	Trip length		Rolling 12 months
8			n/a
9	21/11/2020	30/11/2020	8		8
10	20/01/2021	11/02/2021	21		29
11	11/02/2021	05/04/2021	52		81
12	27/07/2021	30/08/2021	33		114
13	18/09/2021	25/09/2021	6		120
14	07/11/2021	13/11/2021	5		125
15	11/02/2022	02/04/2022	49		93
16	27/04/2022	04/05/2022	6		99
17	23/05/2022	31/05/2022	7		106
18	10/07/2022	15/07/2022	4		110
19	17/07/2022	31/08/2022	44		154
20	27/10/2022	04/11/2022	7		122
21	23/11/2022	19/12/2022	25		142
22	15/01/2023	25/01/2023	9		151
23	05/03/2023	21/03/2023	15		117
24	02/04/2023	27/04/2023	24		141
Main

Cell Formulas
Range	Formula
D9:D24	D9	=MAX(0,DATEDIF(B9,C9,"d")-1)
F9:F24	F9	=SUMIFS(D$9:D9,B$9:B9,">"&B9-365)

Incredible!! Thank you so much, spent hours trying to work it out myself.

 

[Fluff]

You're welcome & thanks for the feedback.