Time difference in Months, but represented for each Year separately

[tomekm]

Given a start date and end date, I'm trying to calculate the time diff. in number of Months, BUT representing the count of the Months per each year. For example:

Start Date: 01/25/2020
End Date: 06/28/2023

Occurrences in 2020 only: formula I have: timediff (in months) between Start Date and 12/31/2020
Occurrences in 2021 only: need formula here (I cannot hard code the value of 12)
Occurrences in 2022 only: need formula here (I cannot hard code the value of 12)
Occurrences in 2023 only: formula I have: timediff (in months) between 01/01/2023 and End Date

Thank you!

 

[Snakehips]

@tomekm Not sure of your existing formula and whether it rounds or whatever but maybe like below to capture the 'inbetween' 12's ?

Bridal Stuff2.xlsx
	A	B
1	Start	25/01/2020
2	End	28/06/2023
3
4	2020	Your Existing formula?
5	2021	12
6	2022	12
7	2023	Your Existing formula?
Sheet7

Cell Formulas
Range	Formula
B4:B7	B4	=IF(AND(YEAR(B$1)<A4,YEAR(B$2)>A4),12,"Your Existing formula?")

Hope that helps.

 

[JamesCanale]

If you have 365, this would be nice.

MrExcelPlayground4.xlsx
	A	B	C	D	E
1				Year	months
2	Start	1/25/2020		2020	12
3	End	6/28/2023		2021	12
4				2022	12
5				2023	6
Sheet4

Cell Formulas
Range	Formula
D2:D5	D2	=SEQUENCE((YEAR(B3)-YEAR(B2)+1))+YEAR(B2)-1
E2:E5	E2	=SUM((--(DATE(D2,SEQUENCE(12),1)>=DATE(YEAR($B$2),MONTH($B$2),1))*(--(DATE(D2,SEQUENCE(12),1)<=DATE(YEAR($B$3),MONTH($B$3),DAY($B$3))))))
Dynamic array formulas.

I think there are work arounds for older excel.