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]

 

[Fluff]

If your holiday dates are A1:A15 try
=WORKDAY(TODAY(),2,A1:A15)

 

[mchilapur]

Hello Fluff,
Thanks for your response. That works great, but I need VBA to determine this.
I need to run this Code on huge data which already has enough formulas in it coz of which it responds bet slowly.

Kindly provide me the script for this.

 

[mchilapur]

fluff, can you please provide the script..? Sorry but, are you working on this.. Dont mistake me..

 

[Fluff]

If you want the full code rather than just the method, I will need to know
Sheets names
Data ranges
Holiday ranges
where the formula goes

 

[mchilapur]

Hello fluff, i agree and below is the details
I have 2 sheets, 1) Data. 2) Holidays
Data sheet had 13000 plus of rows with A:H column. Column A had the dates on which i pull this report (Todays date).Under column H i need to generate the dates for each entry with criteria as stated in this post.

Holiday sheet has holiday from Range A2:A10.

Hope this helps.

 

[mchilapur]

Hello Fluff, sorry but any updates on this..?

 

[GTO]

Presuming the dates are actual dates and not strings, and look like:
Holiday[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD="align: center"]HOLIDAYS[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]7-Aug-17[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]8-Aug-17[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]9-Aug-17[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]24-Nov-17[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]25-Dec-17[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]26-Dec-17[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]27-Dec-17[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]28-Dec-17[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]29-Dec-17[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

In a Standard Module:

Option Explicit
  
Public Function Add2DaysAndSkipHolidayList(ByVal dateInputOutput As Date) As Date
Dim Index As Long
  
  dateInputOutput = dateInputOutput + 2
  If Weekday(dateInputOutput, vbMonday) = 7 Then
    dateInputOutput = dateInputOutput + 1
  ElseIf Weekday(dateInputOutput, vbMonday) = 6 Then
    dateInputOutput = dateInputOutput + 2
  End If
  
  On Error Resume Next
  Do
    Index = 0
    Index = Application.Match(dateInputOutput, ThisWorkbook.Worksheets("Holiday").Range("A2:A10").Value, 0)
    dateInputOutput = dateInputOutput + 1
  Loop While Not Index = 0
  On Error GoTo 0
  
  dateInputOutput = dateInputOutput - 1
  
  
  If Weekday(dateInputOutput, vbMonday) = 7 Then
    dateInputOutput = dateInputOutput + 1
  ElseIf Weekday(dateInputOutput, vbMonday) = 6 Then
    dateInputOutput = dateInputOutput + 2
  End If
  
  Add2DaysAndSkipHolidayList = dateInputOutput
  
End Function
  
Sub example()
  MsgBox Add2DaysAndSkipHolidayList(#8/4/2017#)
  MsgBox Add2DaysAndSkipHolidayList(#12/21/2017#)
End Sub

Does that help?

Mark

 

[Fluff]

Cheers for stepping in here Mark, I must have lost this thread somehow.

 

[mchilapur]

Works like charm.. Many thanks..
Just curious, what is below code for..?

[COLOR=#333333]MsgBox Add2DaysAndSkipHolidayList(#12/21/2017#)[/COLOR]