Sunday, March 02, 2008

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,

No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group