WorkDay Function from Excel

[Ryan]

In Excel, I often use the workday() function to calculate dates

for example
=WORKDAY(1/1/06,5)
gives the date for the next work day (or week day) 5 days after the date
1/1/06. It will exclude weekends (and holidays entered) in returning the
date.

I cannot seem to find this function in Access. Seems odd that two products
so similar would not share the same function libraries.
Is there a similar function in Access?
Can I add in this function?

Ryan

 

[Tom Wickerath]

Hi Ryan,

See these articles on the MVPS web site. You'll probably want to bookmark
this site (http://www.mvps.org/access) if you have not already done so:

Calculate Number of Working Days
http://www.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

In Excel, I often use the workday() function to calculate dates

for example
=WORKDAY(1/1/06,5)
gives the date for the next work day (or week day) 5 days after the date
1/1/06. It will exclude weekends (and holidays entered) in returning the
date.

I cannot seem to find this function in Access. Seems odd that two products
so similar would not share the same function libraries.
Is there a similar function in Access?
Can I add in this function?

Ryan

 

[Arvin Meyer [MVP]]

In addition to Tom's suggestions, have a look at:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[janschutz]

I copied your information and made a module in Access. I created the Holiday
table with a date field called Holidaydate. But when I run my access query
it reports #error in the cell that should have the positive or negative
number of workdays. What should I try next?

Thanks for all you help.

 

[janschutz]

Please ignore my previous message I was using the function incorrectly. I am
trying to use your workdays to show both positive and negative number for
early / late in a report. The current workdays show only the positive number
and 0 for the negative response. Anything you can do would be great.

Thanks for your help.

 

[James A. Fortune]

I copied your information and made a module in Access. I created the Holiday
table with a date field called Holidaydate. But when I run my access query
it reports #error in the cell that should have the positive or negative
number of workdays. What should I try next?

Thanks for all you help.

:

Here is an alternative solution:

In:

http://groups.google.com/group/comp.databases.ms-access/msg/e24ae77b88ce9f93

I posted a BusinessDateAdd function. I have modified
DirectDateFunctions.mdb so that the BusinessDateAdd function can handle
a negative value for the number of days to add. I consider this
preliminary code since I have not tested it much. The few examples I
tested worked correctly. I apologize for not including negative value
capability the first time around.

The links again:

Zipped:

http://www.oakland.edu/~fortune/DirectDateFunctions.zip

Text:

http://www.oakland.edu/~fortune/DirectDateFunctions.txt

Previous Version:

http://www.oakland.edu/~fortune/DirectDateFunctions7_21_06.txt

Syntax:

BusinessDateAdd(number, date, boolean)

number = number of business days to add/subtract
date = starting date
boolean = True when using observed holiday functions

Example:

BusinessDateAdd(10, #12/23/06#, False) => 1/9/2007

Sample calculation in reverse:

BusinessDateAdd(-10, #1/9/2007#, False) => 12/22/06

Note: 12/23/06 is a Saturday so the function had to go back to the
preceding Friday.

James A. Fortune
(e-mail address removed)

 

[yque]

If you're using the code from

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm

using the first function dhAddWorkDaysA(lngDays, dtmDate, adtmDates) if
instead of putting the field you're using for lngDays in the first section of
the function, you add it to the dtmDate, it will allow negative results. For
example, normally you would use this function like this:

dhAddWorkDaysA([NumberofDaysDifference],[StartDate], #01/01/07#)

I use the function for scheduling and ran into the same problem. I found
that I rearrange how the fields are entered it works. Like below.

dhAddWorkDaysA(0,[Start Date]+[NumberofDaysDifference],#01/01/07#)

Of course the 0 can be replaced with any integer for a standard date
deviation with the [NumberofDaysDifference] as the variable.

I'm not sure how clearly my explanation is, but it works regardless.