Separate Date Range Based on Year

nitaly

New Member
Joined
Sep 23, 2015
Messages
22
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.

ABCDEFGHI
LineStart DateEnd DateStart DateEnd DateStart DateEnd DateStart DateEnd Date
1​
11/12/1211/18/14Result>11/12/12
12/31/2012​
1/1/2013​
12/31/2013​
1/1/2014​
11/18/2014​
2​
11/19/1211/25/1311/19/1211/25/13
3​
11/26/1212/02/1211/26/1212/02/12
4​
12/03/1212/09/1212/03/1212/09/12
5​
12/10/1612/16/1812/10/1612/16/18
6​
12/17/1312/23/1412/17/1312/23/14
7​
12/24/1201/01/1312/24/1201/01/13
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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.


Cell Formulas
RangeFormula
E1E1=EOMONTH(D1,11)
F1:M1F1=EDATE(D1,12)
D2D2=YEAR(D1)&" Start Date"
E2E2=YEAR(E1)&" End Date"
D3:D9,L3:L9,J3:J9,H3:H9,F3:F9D3=IF(AND($A3<=E$1,$B3>=D$1),MAX(D$1,$A3),"")
E3:E9,M3:M9,K3:K9,I3:I9,G3:G9E3=IF(AND($A3<=E$1,$B3>=D$1),MIN(E$1,$B3),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,114,653
Messages
5,549,212
Members
410,905
Latest member
Extjel
Top