Calculating dates in a year by a start and end date

[olive2403]

Hi Everyone,

I am trying to calculate the number of days a year someone was in a location using a start and end date. Any idea how to do this.

Example:

Period Start Date	Period End Date
7/20/2020​	12/31/2022​

So in this case, I need to know the number of days in 2020, 2021 and 2022.

Thanks!!

 

[jtakw]

Hi,

Does something like this work for you:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Period Start Date	Period End Date		Days	2019	2020	2021	2022	2023
2	7/20/2020	12/31/2022				164	365	364
3	11/30/2019	8/15/2023			31	366	365	365	226
Sheet1000

Cell Formulas
Range	Formula
E2:I3	E2	=IF(OR(YEAR($A2)>E$1,E$1>YEAR($B2)),"",IF(YEAR($B2)=E$1,DAYS($B2,DATE(E$1,1,1)),DAYS(DATE(E$1,12,31),$A2)-SUM($D2:D2)))

 

[jtakw]

If you need the End Date Inclusive

Use this instead:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Period Start Date	Period End Date		Days	2019	2020	2021	2022	2023
2	7/20/2020	12/31/2022				164	365	365
3	11/30/2019	8/15/2023			31	366	365	365	227
Sheet1000

Cell Formulas
Range	Formula
E2:I3	E2	=IF(OR(YEAR($A2)>E$1,E$1>YEAR($B2)),"",IF(YEAR($B2)=E$1,DAYS($B2,DATE(E$1,1,1))+1,DAYS(DATE(E$1,12,31),$A2)-SUM($D2:D2)))

 

[StephenCrump]

Welcome to the Forum!

Another way, using Excel 365 functionality:

	A	B	C	D	E	F	G	H
1	Start	End		2019	2020	2021	2022	2023
2	20 Jul 2020	31 Dec 2022		-	165	365	365	-
3	30 Nov 2019	15 Aug 2023		32	366	365	365	227
4	1 Jan 2021	31 Dec 2022		-	-	365	365	-
5	31 Dec 2019	1 Jan 2021		1	366	1	-	-
6	1 Jan 2022	1 Jan 2022		-	-	-	1	-
Sheet4

Cell Formulas
Range	Formula
D1:H1	D1	=LET(y,YEAR(MIN(A2:A6)),SEQUENCE(,1+YEAR(MAX(B2:B6))-y,y))
D2:H6	D2	=LET(s,DATE(D$1#,1,1),x,IF(EDATE(s,12)>B2,1+B2,EDATE(s,12))-IF(s<A2,A2,s),IF(x>0,x,"-"))
Dynamic array formulas.

 

[olive2403]

Hi,

Does something like this work for you:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Period Start Date	Period End Date		Days	2019	2020	2021	2022	2023
2	7/20/2020	12/31/2022				164	365	364
3	11/30/2019	8/15/2023			31	366	365	365	226
Sheet1000

Cell Formulas
Range	Formula
E2:I3	E2	=IF(OR(YEAR($A2)>E$1,E$1>YEAR($B2)),"",IF(YEAR($B2)=E$1,DAYS($B2,DATE(E$1,1,1)),DAYS(DATE(E$1,12,31),$A2)-SUM($D2:D2)))

Thank you! Do you happen to know why this would work until 2021? I'm going from 2014-2021 and any date range that starts and ends in 2021, is coming back with 365.

 

[olive2403]

If you need the End Date Inclusive

Use this instead:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Period Start Date	Period End Date		Days	2019	2020	2021	2022	2023
2	7/20/2020	12/31/2022				164	365	365
3	11/30/2019	8/15/2023			31	366	365	365	227
Sheet1000

Cell Formulas
Range	Formula
E2:I3	E2	=IF(OR(YEAR($A2)>E$1,E$1>YEAR($B2)),"",IF(YEAR($B2)=E$1,DAYS($B2,DATE(E$1,1,1))+1,DAYS(DATE(E$1,12,31),$A2)-SUM($D2:D2)))

Actually i'm noticing its wrong for any start or end dates that happen within the same year. Do you happen to know? For example a start of 8/4/2014 and 8/14/2014, is resulting in 226 days in 2014.

 

[olive2403]

Welcome to the Forum!

Another way, using Excel 365 functionality:

	A	B	C	D	E	F	G	H
1	Start	End		2019	2020	2021	2022	2023
2	20 Jul 2020	31 Dec 2022		-	165	365	365	-
3	30 Nov 2019	15 Aug 2023		32	366	365	365	227
4	1 Jan 2021	31 Dec 2022		-	-	365	365	-
5	31 Dec 2019	1 Jan 2021		1	366	1	-	-
6	1 Jan 2022	1 Jan 2022		-	-	-	1	-
Sheet4

Cell Formulas
Range	Formula
D1:H1	D1	=LET(y,YEAR(MIN(A2:A6)),SEQUENCE(,1+YEAR(MAX(B2:B6))-y,y))
D2:H6	D2	=LET(s,DATE(D$1#,1,1),x,IF(EDATE(s,12)>B2,1+B2,EDATE(s,12))-IF(s<A2,A2,s),IF(x>0,x,"-"))
Dynamic array formulas.

Thank you but unfortunately I have Excel 16 and so this appears to be erroring.

 

[StephenCrump]

Thank you but unfortunately I have Excel 16 ...

Oops, sorry. Don't know how I managed to read 2016 as 365 ...

Try this instead:

	A	B	C	D	E	F	G	H
1	Start	End		2019	2020	2021	2022	2023
2	20 Jul 2020	31 Dec 2022		-	165	365	365	-
3	30 Nov 2019	15 Aug 2023		32	366	365	365	227
4	1 Jan 2021	31 Dec 2022		-	-	365	365	-
5	31 Dec 2019	1 Jan 2021		1	366	1	-	-
6	1 Jan 2022	1 Jan 2022		-	-	-	1	-
Sheet1

Cell Formulas
Range	Formula
D2:H6	D2	=IFERROR(1/(1/MAX(0,MIN(DATE(D$1+1,1,1),$B2+1)-MAX($A2,DATE(D$1,1,1)))),"-")

 

[jtakw]

Actually i'm noticing its wrong for any start or end dates that happen within the same year. Do you happen to know? For example a start of 8/4/2014 and 8/14/2014, is resulting in 226 days in 2014.

Fixed below:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Period Start Date	Period End Date		Days	2019	2020	2021	2022	2023
2	7/20/2020	12/31/2022				164	365	365
3	11/30/2019	8/15/2023			31	366	365	365	227
4	8/4/2022	8/14/2022						10
Sheet1000

Cell Formulas
Range	Formula
E2:I4	E2	=IF(OR(YEAR($A2)>E$1,E$1>YEAR($B2)),"",IF(YEAR($B2)=E$1,IF(YEAR($A2)=E$1,DAYS($B2,$A2),DAYS($B2,DATE(E$1,1,1))+1),DAYS(DATE(E$1,12,31),$A2)-SUM($D2:D2)))