Omitting Public Holidays from CalcWorkDays

T

tangledball

Registered User.
Local time
Today, 21:46
Joined
Mar 22, 2009
Messages
14
Is there a way for me to omit public holidays using the CalcWorkDays Funtion?

I have the following code that works great for removing the weekends from my calculation, but is there a way for me to remove the public holidays from the equation?

I am currently using the following code:

Function CalcWorkDays(StartDate, EndDate) As Integer

Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer

On Error GoTo Err_Execute

CalcWorkDays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate <= StartDate Then
CalcWorkDays = 1
Else
LTotalDays = DateDiff("d", StartDate - 1, EndDate)
LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkDays = LTotalDays - LSaturdays - LSundays

End If
End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkDays = 0

End Function
=========================================================
But have also found this code:

Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", _
"[HOLI_DATE] between #" & dtmStart & "# And #" & DtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

but i can't seem to get this to work for some reason, i have removed the dtm's quoted in the code above and changed the descriptions to that of the table names from which the function will use to calc the number of days. I have also created a Holiday table and entered some public holiday dates and created a leave table with some sample dates that will cross both weekends and public holidays, however when i call this up through my query the records seem to triple (ie i have 3 sample dates in the leave table) but these now show as 9 records when run through the query, also the calculations are all out and the dates where public holidays cross are showing as minus figures.

In the query table i have just entered the StartDate and EndDate in the query fields seen as i would be calling up the public holiday dates from the Holiday table, but it's not working. I have tried various methods, linking the holiday table with the Leave table within the query and including the Holiday [PubDate] within the query field, but still nothing.

I have called up the function using an expresssion which is as follows:
Expr1: Calcworkdays(Date(),[Holidays]![PubDate])

If someone has a working sample of this in operation, i should be able to find out what i'm doing wrong, alternatively any help would be appreciated.

Appologies to the mods for my previous duplicate posting.

:confused:
 
Last edited:
:eek: What on earth is all that!

I have copied the sample code to my module and renamed my tables to fit in with what is asked in the code, but how would I call this through a query?

All that code looks very scary
 
Hi I have found the following Function on one of the samples posted on here, but this runs via a click event. Unfortunately i need to run something like this through a query, but i'm not entirely sure how I call this to show me the results i need.

I have 2 tables one AnnualLeave and one tblHolidays

AnnualLeave just has StartDate and EndDate with ID contained therein.

tblHolidays just has HolidayDate contained.

I have input various sample dates in both tables.

On the query i have chosen the AnnualLeave and used the StartDate and EndDate in the query fields, and used the following expression to call the mod.

Expr1: BusinessDays(Date(),[tblHolidays]![HolidayDate])

but this seems to be asking me for a [tblHolidays]![HolidayDate] Parameter.

I have attached what i have so far, if anyone could have a look to see where i'm going wrong. Document is Access 2007.



Function BusinessDays(startdate As Date, enddate As Date) As Integer

'By Susan Sales Harkins and Doris Manning
'Inside Microsoft Access Feb 2004
' http://download.elementkjournals.com/access/200402/busdays.zip

Dim intHolidays As Integer
Dim intTotalDays As Integer
Dim intWeekendDays As Integer
'Dim rst As New ADODB.Recordset
Dim rst As DAO.Recordset
Dim strSQL As String
Select Case DatePart("w", startdate, vbMonday) 'Week starts on monday
Case 6
startdate = DateAdd("d", startdate, 2) 'Saturday
Case 7
startdate = DateAdd("d", startdate, 1) 'Sunday
End Select
Select Case DatePart("w", enddate, vbMonday) 'Week starts on monday
Case 6
enddate = DateAdd("d", enddate, -1) 'Saturday
Case 7
enddate = DateAdd("d", enddate, -2) 'Sunday
End Select
strSQL = "Select Count(*) as HolidayCount From tblHolidays " & _
"Where HolidayDate BETWEEN #" & startdate & "#" & _
" AND " & "#" & enddate & "#;"
'rst.Open strSQL, CurrentProject.Connection
Set rst = CurrentDb.OpenRecordset(strSQL)
intHolidays = rst!HolidayCount 'Count Holidays between dates
intTotalDays = DateDiff("d", startdate, enddate) + 1 'Calc dif in days
intWeekendDays = DateDiff("ww", startdate, enddate, vbMonday) * 2 'Calc dif in weeks and multiply by 2
BusinessDays = intTotalDays - intWeekendDays - intHolidays
Set rst = Nothing

End Function
 

Attachments

Can no-one help me with this... it's starting to drive me insane :mad:
 
Hi Raskew,

Thanks for the reply.

I have copied and pasted the Public Function into a standard module as instructed, I assume that this function creates the tables for you automatically, however how do i actually call this up? I have tried call through a query, but when i go into the expressions it doesn't show me the MakeDates function, it shows the mod name, but it wont select it. I'm guessing that this is not the way to call up this function in the first place, but where would i call it from?

Thanks for your help
 
1) After copying the code to a standard module and saving the module, ensuring that the module name is not the same as the procedure name.

2) After creating and popuating tblHolidates2 with the holidays for year 2009:

3) From the debug (immediate) window type:
Call MakeDates("tblDates", "2009", "23456") <enter>

4) This is going to create tblDates and populate it with all the work days (Monday - Friday) minus any holidays for year 2009.

I probably woud not call MakeDates() from a query, as there is no need to constantly recreate/repopulate the table unless something has changed, i.e. the weekdays ("23456") or the holidays.

Keep in mind that this merely creates a table containing the workdays for the specified year. It's up to you to figure out how you are going to use this table to accomplish whatever it is you're trying to do.

HTH - Bob
 
Last edited:
raskew said:
1) After copying the code to a standard module and saving the module, ensuring that the module name is not the same as the procedure name.

2) After creating and popuating tblHolidates2 with the holidays for year 2009:

3) From the debug (immediate) window type:
Call MakeDates("tblDates", "2009", "23456") <enter>

4) This is going to create tblDates and populate it with all the work days (Monday - Friday) minus any holidays for year 2009.

I probably woud not call MakeDates() from a query, as there is no need to constantly recreate/repopulate the table unless something has changed, i.e. the weekdays ("23456") or the holidays.

Keep in mind that this merely creates a table containing the workdays for the specified year. It's up to you to figure out how you are going to use this table to accomplish whatever it is you're trying to do.

HTH - Bob
Click to expand...


While it is possible to make a table of all the work days, it is not a common practice. It usually requires a lot more effort (disk reads, calculations,etc) so it is not nearly as efficient as doing the reverse.


I look at a what list is the shorted and easiest to maintain. In this case the holidays, and create a method that is either includes or excludes the desired records based on the type of list. In the case of holidays, it is a list of items to exclude. It will be a lot easier to create the short list of days that you do not work (holidays) and the larges list of all the days you do work. Also the holiday list will be a whole lot easier to maintain since it is a lot smaller.
 
Boyd -

Could you post a dumbed-down version (pre A2007) of your database.

Bob
 
HiTech Coach your a genius, this is exactly what i need. I was getting so confused with all the different variations and logic behind each one. Also big thanks for dumming this down to 2000 as well, i will be implementing this on Access 2003, but I only have 2007 at home and they won't allow us to access this forum at work :(.

One very last question: at the moment the query will return the number of days minus the first day, how do i change this to include the first day as well, ie Monday through Friday to be 5 days minus weekends and fixed holidays.

If I say entered 11th May to 22nd May, this would be 10 working days, however the query only returns 9.

Thanks again for your time and effort.

Mike
 
you could add 1, or you could turn set blIncludeStartdate to true.
 
HiTechCoach said:
Bob,

Here is the database in 2000 format.
Click to expand...

Good afternoon HiTexhcoach. This code is 90% perfect for an Issues database I am building to count workdays between the start of an issue and the end. However, need some help in tweeking it a bit. Would like it to return the number of days in DD/HH/MM instead of just days. For example,

Start Date - 3/15/10 @ 1:15pm
End Date - 3/17/10 @ 10:49am

Would return 1 day, 21 hours, and 34 minutes.

Is this possible? Any assistance is greatly appreciated.

Thanks
David V
 
You must log in or register to reply here.

Similar threads

A
FILTER PRINT REPORT
Replies
2
Views
254
arnelgp
arnelgp
A
Filter Records
Replies
1
Views
361
June7
June7
D
Apply variables to function
Replies
2
Views
897
Drand
D
B
Avoid duplicate records (overlapping dates)
Replies
18
Views
1,175
arnelgp
arnelgp
B
Overlapping dates with some null data
Replies
7
Views
897
bilakos93
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom