Calculate Working days (Excluding Holdiays) using Excel Function / VBA

Calculate the End date (Excluding Holidays) based on No. of Days using Excel Function / VBA

Most of the time you want to exclude weekends and holidays in the calculation for workdays, here is the simple way to do that.


This uses WORKDAY WorksheetFunction, which returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Function Calculate_Workday_With_Holidays_direct Value()



Dim WrkDays As Integer
Dim StartDate As Date
Dim EndDate As Date
Dim arHolidays() As Date

'arHolidays() = Array(#1/1/2008#)

StartDate = Now
EndDate = WorksheetFunction.WorkDay(StartDate, 12, #2/23/2008#)


End Function


The following excludes the holiday dates from the range (Range("b2:b15") here)

Function Calculate_Workday_With_Holidays_As_Range()




Dim WrkDays As Integer
Dim StartDate As Date
Dim EndDate As Date
Dim arHolidays() As Date

'arHolidays() = Array(#1/1/2008#)

StartDate = Now
EndDate = WorksheetFunction.WorkDay(StartDate, 12, Range("b2:b15"))


End Function


The above excludes weekends and calculates the end date of the task based on the no. of days


Calculate the End date programmatically, Code Calculate Workdays - Excel VBA,