vba first day of month code example

Example 1: excel vba first working day month

' Returns first working day of month (except public holidays)
Public Function FirstWorkingDayOfMonth(ByVal pDate As Date) As Date
    FirstWorkingDayOfMonth = CDate(Application.WorkDay(DateSerial(Year(Date), _
        Month(Date), 1), 0))
End Function

Public Sub TestMe()
    MsgBox FirstWorkingDayOfMonth(Date)
End Sub

Example 2: excel vba first day of month

' Returns last day of month
Public Function LastDayOfMonth(ByVal pDate As Date) As Date
    LastDayOfMonth = DateSerial(Year(pDate), Month(pDate) + 1, 0)
End Function

' Returns first day of month
Public Function FirstDayOfMonth(ByVal pDate As Date) As Date
    FirstDayOfMonth = DateSerial(Year(pDate), Month(pDate), 1)
End Function

Public Sub TestMe()
    MsgBox LastDayOfMonth(Date)
    MsgBox FirstDayOfMonth(DateSerial(2020, 2, 18))
End Sub