CODEWITHSUNDEEP
vbadateexcel
user2443476
user2443476

Reputation: 1975

Get all dates between 2 dates in vba

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.

Upvotes: 2

Views: 15965

Answers (5)

cezarypiatek
cezarypiatek

Reputation: 1124

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

Upvotes: 4

Swapnil Wankhede
Swapnil Wankhede

Reputation: 44

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.

Upvotes: 0

snb
snb

Reputation: 343

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

Upvotes: 1

user4039065
user4039065

Reputation:

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:

    UDF for array of dates

Upvotes: 0

Arya
Arya

Reputation: 341

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

Upvotes: 6

Related Questions