Create list of dates given list of date ranges in Excel

Question

In Excel, I have a column of start dates and a corresponding column of end dates like so:

Start Date        End Date
6/26/06           7/2/06
7/1/06            7/8/06
8/17/06           8/21/06
8/17/06           8/20/06
9/3/06            9/9/06
9/6/06            9/8/06

How do I get a new column with all dates within these start/end date ranges, inclusive of start date and end date? So I'd want a new column that looks like this:

New Column
6/27/06
6/28/06
6/29/06
6/30/06
7/1/06
7/2/06
7/1/06
7/2/06
7/3/06
7/4/06
7/5/06
7/6/06
7/7/06
7/8/06
8/17/06
8/18/06
8/19/06
8/20/06
8/21/06
8/17/06
8/18/06
8/19/06
8/20/06
9/3/06
9/4/06
9/5/06
9/6/06
9/7/06
9/8/06
9/9/06
9/6/06
9/7/06
9/8/06

I have tried the tutorial here: https://www.extendoffice.com/documents/excel/2659-excel-list-all-dates-between-two-dates.html, but I'm not sure how to replicate it with more than 1 row of Start Date/End Dates.

OpiesDad · Accepted Answer · 2016-12-01 23:49:30Z

1

Let's assume that you have your data starting in column A1, with the word Start in A1 and 9/8/2006 in B7.

Further assume that you want your output in Column D. Make A helper column, called Index in column E.

Put =A2 into Cell D2. Put 1 in Cell E2.

In Cell D3 put:

=IF(E2=0,"",IF(E2=E3,D2+1,OFFSET($A$1,E3,0)))

In Cell E3 put:

=IF(D2=OFFSET($A$1,E2,1), IF(OFFSET($A$1,E2+1,0) = 0, 0,E2+1), E2)

Then just copy D3 and E3 down. Once you have reached the last row, column E will show 0 and column D will be blank.

answered Dec 1, 2016 at 23:49

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create list of dates given list of date ranges in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related