So you want Excel to generate a list of all dates between two dates. This video demonstrates three ways you can achieve this.
Click here to download the featured file (including code)
Method 1: Using the SEQUENCE Function
Click here to see this part of the video
Here’s the formula used in the video:
=C2+SEQUENCE(C3-C2+1,,0)
Where C2 is the cell containing your start date and C3 is the cell containing your end date.
Method 2: Using the IF and ROW Functions
Click here to see this part of the video
Here’s the formula used in the video:
=IF($C$2+ROW(A1)>$C$3,””,F2+1)
Where C2 is the cell containing your start date and C3 is the cell containing your end date. Cell F2 is linked to your start date cell (C2) and is the cell above your formula cell.
Method 3: Use a VBA Macro
Click here to see this part of the video
Here’s the code used in the video:
Sub DateRange()
Dim startdate As Date
Dim enddate As Date
Dim outputcell As Range
Dim x As Integer
startdate = Application.InputBox(Prompt:="Select the cell containing your start date", _
Title:="Start Date", Type:=8)
enddate = Application.InputBox(Prompt:="Select the cell containing your end date", _
Title:="End Date", Type:=8)
Set outputcell = Application.InputBox(Prompt:="Select the cell you want to output dates to", _
Title:="Output", Type:=8)
For x = 0 To enddate - startdate
outputcell.Offset(x) = startdate + x
Next x
End Sub