VBA Code for Next Business Day

[AM742]

Hi There,

I have quarterly payment schedule and need to check if any of the payments are falling on Holiday.

If its a holiday, it should move the date to next business day. I am looking for a VBA code to do this.

Please let me know if any additional information is required.

Regards

[arnelgp]

have you looked into Workday() function.
you need to list all your holidays .
see this demo.

[snb]

Sub M_snb()
c00 = "_0101_0105_2512_2612" ' holidays
sn = [transpose(date(2022,1,0)+row(1:365))]

For j = 1 To UBound(sn)
If Weekday(sn(j), 2) > 5 Or InStr(c00, Format(sn(j), "_ddmm_")) Then sn(j) = " "
Next

MsgBox Join(Filter(sn, " ", 0), vbLf) ' workdays in 2022
End Sub

[AM742]

have you looked into Workday() function.
you need to list all your holidays .
see this demo.

Thanks for below. I will review this and get back to you

[AM742]

Sub M_snb()
c00 = "_0101_0105_2512_2612" ' holidays
sn = [transpose(date(2022,1,0)+row(1:365))]

For j = 1 To UBound(sn)
If Weekday(sn(j), 2) > 5 Or InStr(c00, Format(sn(j), "_ddmm_")) Then sn(j) = " "
Next

MsgBox Join(Filter(sn, " ", 0), vbLf) ' workdays in 2022
End Sub

Thanks for this. let me run this and get back to you.