Separate Date Range Based on Year

[nitaly]

Hello Mr.Excel Community, I have a number of date ranges that span multiple years. I need to separate the date ranges into separate date ranges by year. For example, line 1 columns A & B on the left would result in line 1 columns D - I. Thanks for any help.

	A	B	C	D	E	F	G	H	I
Line	Start Date	End Date		Start Date	End Date	Start Date	End Date	Start Date	End Date
1​	11/12/12	11/18/14	Result>	11/12/12	12/31/2012​	1/1/2013​	12/31/2013​	1/1/2014​	11/18/2014​
2​	11/19/12	11/25/13		11/19/12					11/25/13
3​	11/26/12	12/02/12		11/26/12					12/02/12
4​	12/03/12	12/09/12		12/03/12					12/09/12
5​	12/10/16	12/16/18		12/10/16					12/16/18
6​	12/17/13	12/23/14		12/17/13					12/23/14
7​	12/24/12	01/01/13		12/24/12					01/01/13

 

[Jeffrey Mahoney]

Please try this. I added an extra row at the top that you can hide. The top row dates are formulas that allow you to just change the first date in cell D1. After you create the formulas in cells D3 and E3, you can copy those across to the other years. Then you can copy Row 3 of formulas down to the rest of the rows.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				1/1/2012	12/31/2012	1/1/2013	12/31/2013	1/1/2014	12/31/2014	1/1/2015	12/31/2015	1/1/2016	12/31/2016
2	Start Date	End Date		2012 Start Date	2012 End Date	Start Date	End Date	Start Date	End Date
3	11/12/2012	11/18/2014		11/12/2012	12/31/2012	1/1/2013	12/31/2013	1/1/2014	11/18/2014
4	11/19/2012	11/25/2013		11/19/2012	12/31/2012	1/1/2013	11/25/2013
5	11/26/2012	12/2/2012		11/26/2012	12/2/2012
6	12/3/2012	12/9/2012		12/3/2012	12/9/2012
7	12/10/2016	12/16/2018										12/10/2016	12/31/2016
8	12/17/2013	12/23/2014				12/17/2013	12/31/2013	1/1/2014	12/23/2014
9	12/24/2012	1/1/2013		12/24/2012	12/31/2012	1/1/2013	1/1/2013
Sheet1

Cell Formulas
Range	Formula
E1	E1	=EOMONTH(D1,11)
F1:M1	F1	=EDATE(D1,12)
D2	D2	=YEAR(D1)&" Start Date"
E2	E2	=YEAR(E1)&" End Date"
D3:D9,L3:L9,J3:J9,H3:H9,F3:F9	D3	=IF(AND($A3<=E$1,$B3>=D$1),MAX(D$1,$A3),"")
E3:E9,M3:M9,K3:K9,I3:I9,G3:G9	E3	=IF(AND($A3<=E$1,$B3>=D$1),MIN(E$1,$B3),"")

 

[nitaly]

Thank you I'm going to try this!