April 13, 2020

Automatically Find First and Last Working Day in Month

How to automatically find first and last working day in any month using VBA.

Below is the recorded and optimized code:
Option Explicit

Sub FirstLastWorkingDayInMonth()

‘ FirstLastWorkingDayInMonth Macro
‘ How to find the first and last working day in a month given any date of the month


‘Range(“A2”).Select
‘ActiveCell.FormulaR1C1 = “First working Day”
‘Range(“C2”).Select
‘ActiveCell.FormulaR1C1 = _
“=WORKDAY(DATE(YEAR(R[-1]C),MONTH(R[-1]C),1)-1,1,RC[2]:R[3]C[2])”
‘Range(“C2”).Select
‘Selection.NumberFormat = “m/d/yyyy”
‘Range(“E2”).Select
‘Selection.ClearContents
‘Range(“E2”).Select
‘ActiveCell.FormulaR1C1 = “1-Jan-2020”
‘Range(“A3”).Select
‘ActiveCell.FormulaR1C1 = “Last Working Day”
‘Range(“C3”).Select
‘ActiveCell.FormulaR1C1 = _
“=WORKDAY(DATE(YEAR(R[-2]C),MONTH(R[-2]C)+1,1)-1,R[-1]C[2]:R[2]C[2])”
‘Range(“C3”).Select
‘ActiveCell.FormulaR1C1 = _
“=WORKDAY(DATE(YEAR(R[-2]C),MONTH(R[-2]C)+1,1),-1,R[-1]C[2]:R[2]C[2])”
‘Range(“C3”).Select
‘ Selection.NumberFormat = “m/d/yyyy”
‘Range(“C1”).Select
‘ ActiveCell.FormulaR1C1 = “1/15/2020”
‘Range(“E3”).Select
‘ActiveCell.FormulaR1C1 = “1/31/2020”
‘Range(“E3”).Select
‘ActiveCell.FormulaR1C1 = “1/26/2020”
‘Range(“K3”).Select
Range(“A2”) = “First Working Day”
Range(“C2”) = “=WORKDAY(DATE(YEAR(R[-1]C),MONTH(R[-1]C),1)-1,1,RC[2]:R[3]C[2])”
Range(“C2”).NumberFormat = “m/d/yyyy”

Range(“A3”) = “Last Working Day” Range(“C3”) = “=WORKDAY(DATE(YEAR(R[-2]C),MONTH(R[-2]C)+1,1),-1,R[-1]C[2]:R[2]C[2])” Range(“C3”).NumberFormat = “m/d/yyyy”

End Sub