How to caluclate no of days excluding list of holidays and weekends

[sunilvedula]

Hi All,

i want to calucate sla - the numbr of days items needs to be worked and it should exclude holidays and weekends (saturday and sunday) . for example. today is monday and 20th and the SLA for the item is 5 days which should be friday the 24th. but assume since friday is holiday and saturday and sunday are weekends the SLA should ideally be next Monday i.e 27th. How do we do it?

 

[HiTechCoach]

A quick search found many posts on the subject.

Here is just a random pick:
Omitting Public Holidays from CalcWorkDays

 

[sunilvedula]

hi,

i did the search and found the link you mentioned earlier. i did download the 2000 version you posted in the site. But what baffles me is how do i use the modules to calucalte the SLA. i do have a list of holidays which i can input in tblholidays. What is tblannualeaves for? how do i call them in a query. can you please explain as i am dumb

 

[sunilvedula]

i think i understand. If annual leaves are to be accounted then we use the query else can i use it like this (if i do not need to considre annual leave and just holidays) Workdays: fNetWorkdays([Reportdt], 5) since i have a report date and SLA need to be calucalted from that date. Can i do like that ? will it work?

 

[HiTechCoach]

Did you find this code here:

fNetWorkdays and fAddWorkDays

Use this function:

Public Function fAddWorkdays(dtStartDate As Date, _ 
                             lngWorkDays As Long) _ 
                             As Date 
'Adds the passed number of workdays to a passed date.  This code uses 
'fNetWorkdays(), so the assumptions of tblHoliday apply for this function 
'as well. Also note that if a ZERO is entered as the lngWorkDays parameter 
'the function will return the start date, if its a work day, or the first 
'workday PRIOR to the dtStartdate. 
''''''''''''''''''''''''''''''''''''''''''' 
'Author: Brent Spaulding 
'Version: 7 
'Date: Aug 8 2008 
'''''''''''''''''''''''''''''''''''''''''''

It can be called in a query, on a form, or on a report as needed.

 

[sunilvedula]

Yeah i have seen it but my doubt is whether does it require the table annual leaves to be available or not. Because when i gave the reportdt as April 7, 2009 it gave me the sla date as April 16, 2009. ideally it should give me April 13 or April 14. Any suggestions pls... sorry i got it .... it works fine.. just did not check the holidays in which april 10 and april 14 are holiidays and hence it gives me april 16 which is right thanks a lot.... Awesome

 

[HiTechCoach]

Glad you got it figured out!