I would like to have a vba that works only for working days. I built a loop that has a working day for start and end dates but not for the middle days. So when a middle day is a non working day then the loop stops with an error. In my loop the "middle day" is called CurrentDate.

1. I want a working date that could be set up on a dashboard in cell say C2 - Start and C3-End
2. CurrentDate which is a middle day being a workday if not move to the next working day

I tried: 'dtReportDateStart = Application.ThisWorkbook.WorksheetFunction.workDay(("C2"), 1)

'dtReportDateEnd = Format(ThisWorkbook.Worksheets("Dashboard").Range("C3"), "YYYYMMDD") but it doesn't work ...

So far I have:

dtReportDateStart = Format(Application.WorksheetFunction.workDay("2022-05-15", 1), "YYYYMMDD")

dtReportDateEnd = Format(Application.WorksheetFunction.workDay("2022-05-18", 1), "YYYYMMDD") 

CurrentDate = dtReportDateStart 

Do While CurrentDate <> dtReportDateEnd
         [...]
End Loop 

CodePudding user response：

You can use a for loop (because date in Excel is a number), and weekday() function:

Sub WorkingDays()
    With ThisWorkbook.Sheets(1)
        dtReportDateStart = .Range("C2") ' 29.08.2022
        dtReportDateEnd = .Range("C3") ' 15.09.2022
    End With

    For CurrentDate = dtReportDateStart To dtReportDateEnd
        If Weekday(CurrentDate, vbMonday) < 6 Then
            Debug.Print Format(CurrentDate, "YYYYMMDD") & " is working day"
        End If
    Next
End Sub

Prints:

20220829 is working day
20220830 is working day
20220831 is working day
20220901 is working day
20220902 is working day
20220905 is working day
20220906 is working day
20220907 is working day
20220908 is working day
20220909 is working day
20220912 is working day
20220913 is working day
20220914 is working day
20220915 is working day