Adding Working Days

[Leadparsons]

Hi all,

is the a formula that I can add 10 working days to a date. I have used =WORKDAY(E65,10) in excel but can not work it out in access.

Your help is always appreciated

 

[Joe4]

Access does not have any such native function, so you would have to use a User Defined Function. Luckily, other people have already written them, so you can just copy their code. Here is a link to a bunch of date UDFs created for Access: Date/Time: Doing WorkDay Math in VBA

 

[Leadparsons]

How do I create a UDF, have tried entering the expression in the query and it states "Undefined function 'dhAddWorkDaysA' in expression.

 

[Joe4]

Google is a great tools for researching topics like that. If you search on "Create a User Defined Function in Access", you will see many good posts. Here is one: VBA Tips: Build Custom Functions for Your Access Applications

 

[Leadparsons]

Have tried that at VBA Tips: Build Custom Functions for Your Access Applications and with the code on Date/Time: Doing WorkDay Math in VBA, nothing makes sense. Going to sack it, im not that good.

 

[Joe4]

Its really easier than you think. You do not actually need to know any VBA to use it.

Just hit ALT-F11 to bring up the VB Editor.
If the Project Explorer is not already open on the left side of the screen, hit the Project Explorer button to bring it up.
Right-click anywhere within the Project Explorer, select Insert, then Module.
Copy and paste the VBA code from that link with all the UDFs into your empty Module and save.
You should then be able to use those Functions in that code like you would any other native Access function.

 

[Leadparsons]

Tried that, not sure if I need to copy all of the code or just some.

 

[Leadparsons]

I have a field called "Pre Req date" I want another date to show 10 working days from that date, where do I put this in the code:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function

 

[Joe4]

Notice this line here:

[COLOR=#333333]dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)[/COLOR]

So a line from this UDF calls another UDF (some of them work in conjunction with one another). So you need to have that one too.

The best thing to do is copy ALL of it. Even if you don't use all of them, it does not hurt anything to have them all in there (and isn't a whole lot of extra burden on you or your database).

 

[Leadparsons]

I have copied the lot, when I run it I get Compile Error: Sub or Function not defined and highlights dhNextWorkdayA.