# Excel VBA to calculate observed Boxing Day Holiday

Looking for a VBA script of code to calculate both the observed Christmas and Boxing days.
If Christmas falls on a Friday, then Boxing Day would be observed as Monday, December 28
If Christmas falls on a Saturday, then observed holiday is December 24, and Boxing Day would be Sunday and observed as Monday December 27
If Christmas falls on a Sunday, then Christmas is observed as Monday December 26, and Boxing Day moves to Tuesday December 27
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
HI,

pls try

``````Function ObservedBoxingDay(thedate As Date) As Date
dtXmas = DateValue("25/12/" & Year(thedate))
If dtXmas Mod 7 = 1 Then
res = WorksheetFunction.WorkDay(dtXmas, 2)
Else
res = WorksheetFunction.WorkDay(dtXmas, 1)
End If
ObservedBoxingDay = res

End Function
Function ObservedXmasDay(thedate As Date) As Date
dtXmas = DateValue("25/12/" & Year(thedate))
If dtXmas Mod 7 = 1 Then
res = WorksheetFunction.WorkDay(dtXmas, 1)
Else
res = dtXmas
End If
ObservedXmasDay = res

End Function
``````
Regards
0
Author Commented:
Thank you for your reply. Actually I found a snippet of some code and modified it a little to make it work.

Function Observed(thedate As Date) As Date
'Offsets for holidays on weekends.

If WeekDay(thedate, vbSunday) = 1 Then
Observed = thedate + 1
ElseIf WeekDay(thedate, vbSunday) = 7 Then
Observed = thedate - 1
Else:
Observed = thedate
End If

End Function

Function ObservedBoxing(thedate As Date) As Date
'Offsets for Boxing Day on weekends.

If WeekDay(thedate, vbSunday) = 2 Then
ObservedBoxing = thedate + 1
ElseIf WeekDay(thedate, vbSunday) = 7 Then
ObservedBoxing = thedate + 2
Else:
ObservedBoxing = thedate
End If

End Function
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
The presented solution had errors when run. It did not properly dim the variables.
0