Public Sub CurrentYear_Holiday()
Dim Year
Set wb = ThisWorkbook
Set ws = wb.Sheets(msSHEET_LST)
Year = wb.Sheets(msSHEET_KEY).Range("D2").value 'version year of workbook
With ws
.Range("Holiday").Offset(, 1) = "=DATE(" & Year & ",1,1)" 'new year day
.Range("Holiday").Offset(1, 1) = "=DATE(" & Year & ",3,29.56+0.979*MOD(204-11*MOD(" & Year & ",19),30) - WEEKDAY(DATE(" & Year & ",3,28.56+0.979*MOD(204-11*MOD(" & Year & ",19),30))))" 'easter
.Range("Holiday").Offset(2, 1) = "=DATE(" & Year & ",6,1)-WEEKDAY(DATE(" & Year & ",6,6))" 'memorial day
.Range("Holiday").Offset(3, 1) = "=DATE(" & Year & ",7,4)" 'independence day
.Range("Holiday").Offset(4, 1) = "=DATE(" & Year & ",9,1)+CHOOSE(WEEKDAY(DATE(" & Year & ",9,1)),1,0,6,5,4,3,2)" 'labor day
.Range("Holiday").Offset(5, 1) = "=DATE(" & Year & ",11,1)+21+CHOOSE(WEEKDAY(DATE(" & Year & ",11,1)),4,3,2,1,0,6,5)" 'thanksgiving day
.Range("Holiday").Offset(6, 1) = "=DATE(" & Year & ",12,25)" 'christmas day
End With
End Sub
Bookmarks