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
[TABLE="width: 0"]
<tbody>[TR]
[TD]7-Aug-17
[/TD]
[/TR]
[TR]
[TD]8-Aug-17
[/TD]
[/TR]
[TR]
[TD]9-Aug-17
[/TD]
[/TR]
[TR]
[TD]24-Nov-17
[/TD]
[/TR]
[TR]
[TD]25-Dec-17
[/TD]
[/TR]
[TR]
[TD]26-Dec-17
[/TD]
[/TR]
[TR]
[TD]27-Dec-17
[/TD]
[/TR]
[TR]
[TD]28-Dec-17
[/TD]
[/TR]
[TR]
[TD]29-Dec-17
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.


[TABLE="class: std_table, width: 100%"]
<tbody style="box-sizing: border-box;">[TR]
[TD]vbUseSystemDayOfWeek[/TD]
[TD]Returns a number from 1 to 7 and uses your system settings to determine the first day of the week[/TD]
[/TR]
[TR]
[TD]vbMonday[/TD]
[TD]Returns a number from 1 (Monday) to 7 (Sunday).[/TD]
[/TR]
[TR]
[TD]vbTuesday[/TD]
[TD]Returns a number from 1 (Tuesday) to 7 (Monday).[/TD]
[/TR]
[TR]
[TD]vbWednesday[/TD]
[TD]Returns a number from 1 (Wednesday) to 7 (Tuesday).[/TD]
[/TR]
[TR]
[TD]vbThursday[/TD]
[TD]Returns a number from 1 (Thursday) to 7 (Wednesday).[/TD]
[/TR]
[TR]
[TD]vbFriday[/TD]
[TD]Returns a number from 1 (Friday) to 7 (Thursday).[/TD]
[/TR]
[TR]
[TD]vbSaturday[/TD]
[TD]Returns a number from 1 (Saturday) to 7 (Friday).[/TD]
[/TR]
[TR]
[TD]vbSunday[/TD]
[TD]Returns a number from 1 (Sunday) to 7 (Saturday).[/TD]
[/TR]
</tbody>[/TABLE]
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"