Allocating Date Ranges by Start & End Date

[PWH1968]

Hi All - I have a conundrum that I am hoping you can help me with.

I have Start and End Dates that I want to allocate across a number of columns to show how many Years the dates span.

A​	B​	C​	D​	E​	F​	G​
1	Start Date​	End Date​	Year 1​	Year 2​	Year 3​	Year 4​	Year 5​
2	01.Jan.2022​	31.Dec.2025​	01.Jan.2022 - 31.Dec.2022	01.Jan.2023 - 31.Dec.2023	01.Jan.2024 - 31.Dec.2024	01.Jan.2025 - 31.Dec.2025	<blank>​
3	20.Mar.2022​	31.Aug.2025​	20.Mar.2022 - 19.Mar.2023	20.Mar.2023 - 19.Mar.2024	20.Mar.2024 - 19.Mar.2025	20.Mar.2025 - 31.Aug.2025	<blank>​
4

I know how to allocate the Years using combinations of Text and EDATE formulas etc. what I am struggling with is the end date. For example, in Column G - Row 2 I want the formula to ‘end’ and say that there are no dates for Year 5.

Likewise in Column F - Row 3 there is only 5 months which is fine; and then in Column G - Row 3 there would be no dates.

Please help

 

[Dave Patton]

You can review the logic and customize for your requirements.
The headings have real dates like Dec 31, 2022.
Edit the text for your preferences.

T202211a.xlsm
	A	B	C	D	E	F	G	H
1		Start Date	End Date	31-Dec-2022	31-Dec-2023	31-Dec-2024	31-Dec-2025	31-Dec-2026
2		1-Jan-22	31-Dec-25	20220101 - 20221231	20230101 - 20231231	20240101 - 20241231	20250101 - 20251231	No dates
5e

Cell Formulas
Range	Formula
D2:H2	D2	=IF(AND($B2<=EDATE(D$1,-12)+1,$C2>=D$1),TEXT(EDATE(D$1,-12)+1,"yyyymmdd")&" - "&TEXT(D$1,"yyyymmdd"),"No dates")

 

[Dave Patton]

You may like this version better.
You can format the headings to just year and format the dates in the ranges to your preference.

T202211a.xlsm
	A	B	C	D	E	F	G	H
1
2		Start Date	End Date	01-Jan-2022	01-Jan-2023	01-Jan-2024	01-Jan-2025	01-Jan-2026
3		1-Jan-22	31-Dec-25	20220101 - 20221231	20230101 - 20231231	20240101 - 20241231	20250101 - 20251231	No
4		30-Sep-24	31-Aug-25	No	No	20240930 - 20241231	20250101 - 20250831	No
5		20-Mar-23	31-Aug-25	No	20230320 - 20231231	20240101 - 20241231	20250101 - 20250831	No
5e

Cell Formulas
Range	Formula
D3:H4,D5:G5	D3	=LET(s,MAX($B3,D$2),e,MIN(EDATE(D$2,12)-1,$C3),IF(e-s>0,TEXT(s,"yyyymmdd")&" - "&TEXT(e,"yyyymmdd"),"No"))
H5	H5	=LET(s,MAX($B5,H$2),e,MIN(EDATE(H$2,12)-1,$C5),IF(e-s>0,"Yes","No"))

 

[PWH1968]

You may like this version better.
You can format the headings to just year and format the dates in the ranges to your preference.

T202211a.xlsm
	A	B	C	D	E	F	G	H
1
2		Start Date	End Date	01-Jan-2022	01-Jan-2023	01-Jan-2024	01-Jan-2025	01-Jan-2026
3		1-Jan-22	31-Dec-25	20220101 - 20221231	20230101 - 20231231	20240101 - 20241231	20250101 - 20251231	No
4		30-Sep-24	31-Aug-25	No	No	20240930 - 20241231	20250101 - 20250831	No
5		20-Mar-23	31-Aug-25	No	20230320 - 20231231	20240101 - 20241231	20250101 - 20250831	No
5e

Cell Formulas
Range	Formula
D3:H4,D5:G5	D3	=LET(s,MAX($B3,D$2),e,MIN(EDATE(D$2,12)-1,$C3),IF(e-s>0,TEXT(s,"yyyymmdd")&" - "&TEXT(e,"yyyymmdd"),"No"))
H5	H5	=LET(s,MAX($B5,H$2),e,MIN(EDATE(H$2,12)-1,$C5),IF(e-s>0,"Yes","No"))

Dave you are an absolute legend - thank you so much for the different options