VBA to determine next working date excluding holiday List and WeekEnds

[mchilapur]

Hello Experts,
I have a requirement where i need to update a column in excel with dates thats equal to todays Date + 2 days ahead (Ex: 4-Aug + 2 Days ahead).
But the date must not be a weekend (Saturday and Sunday) and also none among the below shown holiday list.
Suppose i am running this Macro Today (4th-August), how can determine next working as 10-Aug based on below holiday list + Weekends.?

I can determine weekends using below code but i need check for holiday list too to determine for how many days i need to count ahead.

Weekday(MyDate, vbMonday) > 5

Can anyone please help with the VBA code to find this..?


Holiday list

7-Aug-17

8-Aug-17

9-Aug-17

24-Nov-17

25-Dec-17

26-Dec-17

27-Dec-17

28-Dec-17

29-Dec-17

<tbody>
</tbody>

 

[GTO]

You are most welcome and glad that helped. The example sub was simply to demonstrate the function.

Mark

 

[Ingolf]

@GTO

I work in Europe where the week begins on Monday. I tested your code, tried to change it, but I did not. For some reason, the VBA code, do not skip over the holiday days in the "Holiday" sheet.
Can you help me?
Thank you.

 

[mchilapur]

This code works perfectly.. Make sure you add Holiday list in separate sheet by name "Holiday". Also change the code per below weekdays.

vbUseSystemDayOfWeek	Returns a number from 1 to 7 and uses your system settings to determine the first day of the week
vbMonday	Returns a number from 1 (Monday) to 7 (Sunday).
vbTuesday	Returns a number from 1 (Tuesday) to 7 (Monday).
vbWednesday	Returns a number from 1 (Wednesday) to 7 (Tuesday).
vbThursday	Returns a number from 1 (Thursday) to 7 (Wednesday).
vbFriday	Returns a number from 1 (Friday) to 7 (Thursday).
vbSaturday	Returns a number from 1 (Saturday) to 7 (Friday).
vbSunday	Returns a number from 1 (Sunday) to 7 (Saturday).

<tbody style="box-sizing: border-box;">
</tbody>

This code below is checking for Saturday and sundays. Adjust the number per above table as per your time-zone.

If Weekday(dateInputOutput, vbMonday) = 7 Then    dateInputOutput = dateInputOutput + 1
  ElseIf Weekday(dateInputOutput, vbMonday) = 6 Then
    dateInputOutput = dateInputOutput + 2
  End If

 

[Ingolf]

OK. Using the holidays in your post and putting in A1=04.08.2017 (Friday), in B1 = Add2DaysAndSkipHolidayList (A1) the result is 07.08.2017, which is not correct since 07.08.2014, 08.08.2014 and 09.08.2014 are holidays . In B1 should appear August 10, 2014, as the first working day after the A1 date.
Or maybe I did not understand what you want.
"VBA to determine next working date excluding holiday List and WeekEnds"