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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,824
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,118,861
Messages
5,574,708
Members
412,613
Latest member
EFRATA
Top