Excel - VBA First working day of the month

1
votes

I've an workbook_open macro as below; 

Private Sub Workbook_Open()
Sheets("Staff List").Activate

If Range("G1") = "Yes" Then

Sheets(Format(Now, "mmmm")).Activate

Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

End If

End Sub

The formula in G1 is as below;

=IF(OR(TODAY()=WORKDAY(EOMONTH(TODAY(),-1),1,0),TODAY()=WORKDAY(EOMONTH(TODAY(),-1),2,0)),"Yes","No")

So checking to see whether today is either the first or second working day of a month. How would remove the need to reference G1 and instead work the formula directly into the VBA?

Many thanks

3
excelvba

3 Answers

3
votes

An approach which doesn't use worksheet functions:

Function WorkdayOfMonth(ByVal dtInput As Date) As Integer
    Dim dt As Date
    Dim i As Integer
    If Weekday(dtInput, vbMonday) < 6 Then
        For dt = DateSerial(Year(dtInput), Month(dtInput), 1) To dtInput Step 1
            If Weekday(dt, vbMonday) < 6 Then i = i + 1
        Next dt
        WorkdayOfMonth = i
    Else
        WorkdayOfMonth = 0
    End If
End Function

Sub Test()
    If WorkdayOfMonth(Date) > 0 and WorkdayOfMonth(Date) <= 2 Then
        'do stuff
    End If
End Sub
2
votes

Try,

if Date = application.workday(dateserial(year(date), month(date), 0), 1) or _
   Date = application.workday(dateserial(year(date), month(date), 0), 2) then
    debug.print "1st or 2nd workday of month"
end if

Day 0 of a month is the previous month's EOMONTH.

2
votes

Try

If Evaluate("=IF(OR(TODAY()=WORKDAY(EOMONTH(TODAY(),-1),1,0),TODAY()=WORKDAY(EOMONTH(TODAY(),-1),2,0)),""Yes"",""No"")") = "Yes" Then

Thanks to @jeeped for helping me re-phrase this as:

If Evaluate("OR(TODAY()=WORKDAY(EOMONTH(TODAY(),-1),1,0),TODAY()=WORKDAY(EOMONTH(TODAY(),-1),2,0))") Then