How to auto populate date range (working days only) from start date and end date

[ihwk12]

Hi I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet. Would appreciate any assisstance on this thanks!

 

[Rukt]

Hi ihwk12,

Perhaps something like this? It will take start and end dates from cells B1 and B2 on Sheet1, and fill in dates on Sheet2 starting at A1. You'll need to change the names of each sheet. You can also specify where it starts filling in the dates, by changing Cells(i + 1, 1) to appropriate values for Cells(i + start_row, start_column). (e.g. C5 would be i + 5, 3)

Sub FillDates()

    Dim datStartDate As Date, datEndDate As Date
    Dim lngStartDate As Long, lngEndDate As Long
    
    datStartDate = Sheets("Sheet1").Range("B1").Value
    datEndDate = Sheets("Sheet1").Range("B2").Value
    
    lngStartDate = datStartDate
    lngEndDate = datEndDate

    Dim i As Integer

    For i = 0 To (lngEndDate - lngStartDate)
        Sheets("Sheet2").Cells(i + 1, 1) = lngStartDate + i
    Next i

End Sub

Cheers,
Rukt

 

[dmt32]

Hi,
to create weekday only dates in your sheet, see if following does what you want. Adjust ranges as required.

Sub AddDates()
    Dim sDate As Date
    Dim r As Long
    Dim i As Integer
    Dim NoDays As Integer
    Dim ws1 As Worksheet
    
    Set ws1 = Sheets("Sheet1")
    
    NoDays = ws1.Range("EndDate") - ws1.Range("StartDate")
    
    r = 1
    For i = 0 To NoDays
        sDate = ws1.Range("StartDate") + i
        If IsWeekDay(sDate) Then
            Sheets("Sheet2").Cells(r, 1).Value = sDate
            r = r + 1
        End If
    Next
End Sub

Function IsWeekDay(ByVal sDate As Date) As Boolean
    IsWeekDay = Weekday(sDate, 1) > 1 And Weekday(sDate, 1) < 7
End Function

Dave

 

[ihwk12]

Thanks for the assistance guys Just hit a small snag (I am a total klutz at this sorry) For now my start date and end date is located at I6 and I7 respectively on the first worksheet called "Charts" My desired output of the dates will start from cell B4 onwards on the second worksheet called "Data Table" When i run the code only cell B4 is populated -.- Again thanks for the help you guys are awesome!

Sub AddDates()
Dim sDate As Date
Dim r As Long
Dim i As Integer
Dim NoDays As Integer
Dim ws1 As Worksheet

Set ws1 = Sheets("Charts")

NoDays = ws1.Range("I7") - ws1.Range("I6")

r = 1
For i = 0 To NoDays
sDate = ws1.Range("I6") + i
If IsWeekDay(sDate) Then
Sheets("Data Table").Range("B4").Value = sDate
r = r + 1
End If
Next
End Sub


Function IsWeekDay(ByVal sDate As Date) As Boolean
IsWeekDay = Weekday(sDate, 1) > 1 And Weekday(sDate, 1) < 7
End Function

 

[dmt32]

Try this change to main code:

Sub AddDates()
    Dim sDate As Date
    Dim r As Long
    Dim i As Integer
    Dim NoDays As Integer
    Dim ws1 As Worksheet
    Set ws1 = Sheets("Charts")
    NoDays = ws1.Range("I7") - ws1.Range("I6")
    r = 4
    For i = 0 To NoDays
        sDate = ws1.Range("I6") + i
        If IsWeekDay(sDate) Then
            Sheets("Data Table").Cells(r, 2).Value = sDate
            r = r + 1
        End If
    Next
End Sub

Dave