Fill table with dates given start and end date

[harryhermit]

Hi,

Is there a way to automatically fill a column with dates given a start and end date? For example:
Given fields with
start date of 11/27/2013 and End date of 12/31/2013

A table would be created with these dates
11/27/2013
11/28/2013
11/29/2013
11/30/2013
12/1/2013
---
--
12/31/2013

I know you can start with the earliest date and drag a cell down to the target date, but I'm looking for more automation after a user enters the start and ends dates

[Tony Valko]

One way...

User entered dates in C1 and D1.

Enter this formula in A1:

=IF(COUNT(C1:D1)<2,"",C1)

Format as Date

Enter this formula in A2:

=IF(N(A1),IF(A1+1<=D$1,A1+1,""),"")

Format as Date

Copy down to as many cells as you think you may need to capture every date.

[harryhermit]

Thanks! It basically works, but in the application I am using -- it causes issues with the chart with anything less then 365 days (I copied the formula down 365 rows in the table to account for a user entering dates for a year) It shifts the graph to the left because the x-axis includes empty date fields for any remaining rows.

I decided to use a vba script instead:

Sub InsertDates()
Range("A10:B400").ClearContents
Dim Rng As Range
On Error Resume Next
Set Rng = Range("Table4").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.Delete Shift:=xlUp
End If

r = 8
x = 9
Do While Range("A" & r) < Range("B2")

Range("A" & x).Value = Range("A" & r) + 1

r = r + 1
x = x + 1

Loop

End Sub

[Tony Valko]

Good deal. Thanks for the feedback!