Skip to content
Home » List All Dates Between Two Dates in Excel | One VBA Macro Method and Two Formula Methods

List All Dates Between Two Dates in Excel | One VBA Macro Method and Two Formula Methods

    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