Get all dates between 2 dates in vba

Question

I am a newbie in vba and I am trying to get in vba all dates between 2 dates, for example I will call the function with the parameters 01-01-2015 and 15-01-2015, and I will get in return an array with all the dates possibles, i.e :

01-01-2015
02-01-2015
03-01-2015
.....
15-01-2015

I didn't find the answer on the forums, so thanks in advance for your help.

Answer 1

Maybe this.

Function udf_Array_of_Dates(dtSTART As Date, dtEND As Date, rDATEs As Range)
    Dim dt() As Date, r As Range, d As Long
    For Each r In rDATEs
        If r.Value >= dtSTART And r.Value <= dtEND Then
            d = d + 1
            ReDim Preserve dt(1 To d)
            dt(d) = r.Value
        End If
    Next r
    udf_Array_of_Dates = dt
End Function

Proof & syntax:

    

Answer 2

Function to get all dates from given range

Function GetDatesRange(dateStart As Date, dateEnd As Date) As Collection
    Dim dates As New Collection
    Dim currentDate As Date
    currentDate = dateStart
    Do While currentDate <= dateEnd
        dates.Add currentDate
        currentDate = DateAdd("d", 1, currentDate)
    Loop
    Set GetDatesRange = dates
End Function

Sample usage

Dim dateStartCell as Range, dateEndCell as Range
Dim allDates as Collection
Dim currentDateSter as Variant
Dim currentDate as Date
Set dateStartCell = ActiveSheet.Cells(3, 3)
Set dateEndCell = ActiveSheet.Cells(3, 6)
Set allDates = GetDatesRange(dateStartCell.Value, dateEndCell.Value)    
For Each currentDateSter In allDates
    currentDate = CDate(currentDateSter)
    'Do something with currentDate
Next currentDateSter

Answer 3

If you just want to print the dates between two date in excel then my Suggestion is to you try below the code.

Sub DateFill()

Dim Start_Date As Date
Dim End_Date As Date
Dim Number_Of_Days As Integer


Start_Date = InputBox(prompt:="Enter the Start Date", Title:="Date Print", Default:="3/1/2013")
End_Date = InputBox(prompt:="Enter the End Date", Title:="Date Print", Default:="3/23/2013")

Range("A1").Value = Start_Date
'Range("B1").Value = End_Date
Range("A1").Select
Number_Of_Days = DateDiff("d", Start_Date, End_Date) ' Return Day

Number_Of_Days = Number_Of_Days + 1
'Range("C1").Formula = "=DATEDIF(A1, B1, ""D"") "


Selection.AutoFill Destination:=Range("A1:A" & Number_Of_Days), Type:=xlFillDefault
    Range("A1:A" & Number_Of_Days).Select


End Sub

Here you have avoid the use of Loop that save the execution time.

Answer 4

you can simply convert the dated in long and make loop(+1) and get all dated between 2 dates(convert that to date again)

Sub Calling()
    Dim test
    test = getDates(#1/25/2015#, #2/5/2015#)
End Sub

Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant

    Dim varDates()      As Date
    Dim lngDateCounter  As Long

    ReDim varDates(1 To CLng(EndDate) - CLng(StartDate))

    For lngDateCounter = LBound(varDates) To UBound(varDates)
        varDates(lngDateCounter) = CDate(StartDate)
        StartDate = CDate(CDbl(StartDate) + 1)
    Next lngDateCounter

    getDates = varDates

ClearMemory:
    If IsArray(varDates) Then Erase varDates
    lngDateCounter = Empty

End Function

Answer 5

An array 'sn' containing all dates from 01-01-2015 to 15-01-2015. Msgbox introduced to illustrate the result.

Sub M_snb()
  sn = Evaluate("index(text(datevalue(""01-01-2015"")+row(1:" & DateDiff("d", CDate("01-01-2015"), CDate("15-01-2015")) & ")-1,""dd-mm-yyyy""),)")
  MsgBox sn(1, 1) & vbLf & sn(2, 1) & sn(UBound(sn), 1)
End Sub