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>

 

[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

	A
1	HOLIDAYS
2	7-Aug-17
3	8-Aug-17
4	9-Aug-17
5	24-Nov-17
6	25-Dec-17
7	26-Dec-17
8	27-Dec-17
9	28-Dec-17
10	29-Dec-17

<thead>
</thead><tbody>
</tbody>

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]