Exclude weekends from date calculation

[CMD]

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

 

[Douglas J. Steele]

Check the Date/Time section of "The Access Web" http://www.mvps.org/access/
for a couple of solutions.

 

[CMD]

Hi Douglas,

I've been trying for 3 days to get the suggested code to work, but I keep
getting one error message after another. The latest is "compile error:
user-defined type not defined". I have no idea what this means.
Any suggestions?

CMD

 

[Randy]

I have a report that calculates the difference (in no. of days) between two
dates, but need to figure out a way to exclude weekend dates if they occur
between those dates, without having to sift through hundreds of records
and
make the changes manually.
Is there any to write an expression that could do this?
I'm using an unbound text box (calculated field) in the report for this.

Hi again CMD. Access does not offer a function that returns the number of
weekdays between two dates. However users can use Access' open-programming
model to accomplish this. I have developed a function called NumWeekdays
(see below). This function is the fastest ever. Just copy the all the code
below this paragraph, and place it in a module. Save the module and then put
the following code (between the quotes) into the Report's textbox as follow:
"=NumWeekdays([Field1], [Field2])". For more information about this function
please visit: http://randyb.home.att.net/docs/NumWeekdays.html
Regards -Randy

'FUNCTION NUMWEEKDAYS (..)
'This function returns the number of weekdays (Mon-Fri) between
'two (2) given dates. The order of the parameters does not matter.
'A Weekday is defined as any of the five (5) days starting Monday
'through Friday. The function excludes Weekends (Saturdays and
'Sundays), but do not exclude Weekdays that are Holidays.
'The function uses the same algorithm as NumWeekdays()

'Function Limits: Based on Win32 data type limits.
'Valid date range from 1/1/100 to 12/31/9999

'Version 1.0.0 2004/11/20
'Developed by Randy Balbuena (randyb @att.net). United States.

'Copyright © 2004 by Randy Balbuena. All rights reserved.

'TERMS AND CONDITIONS OF USE

'You can freely use this code for your own work (personal or
'commercial), including distribution in executable form, only
'under the following conditions: Permission is granted to use
'this code as long as the author and its email is cited as the
'source, the copyright, and this notice are NOT removed. You
'cannot distribute modified versions of the source code. You
'cannot use this code in printed media (Books, Web, etc) without
'the express permission of the author. This code has been
'examined against logic and code defects, however its author,
'Randy Balbuena, provides the code "as is" without warranties of
'any kind. Its author shall not be liable for any damages
'suffered by you or any third party as a result of using this
'code. In no event will the author be liable for any lost
'revenue, profit, or data, or for direct, indirect, special,
'consequential, incidental, or punitive damages. Support is
'available, but with no formal responsibility from its author.
'You may contact the author for questions, permissions of use,
'comments, issues or donations to Randy Balbuena (randyb @att.net).
'http://randyb.home.att.net/docs/NumWeekdays.html

Public Function NumWeekdays(d1 As Date, d2 As Date) As Long
If (d1 > d2) Then: Dim aux As Date: aux = d1: d1 = d2: d2 = aux
NumWeekdays = ((Fix((Fix(d2 - d1 + 1)) / 7) * 5) + _
IIf(((Fix(d2 - d1 + 1)) Mod 7), IIf(((Weekday(d1) > 1) And _
(Weekday(d2) < 7)), (Weekday(d2) - Weekday(d1)) + _
IIf(((Weekday(d2) - Weekday(d1)) < 0), 6, 1), _
(Weekday(d2) - Weekday(d1))), 0))
End Function

 

[Douglas J. Steele]

It would help if you indicated what code you're trying to use (there's more
than one things at that site that you could be using).

First thing to check is that your references are okay. Open any code module.
Select Tools | References from the menu bar. Examine all of the selected
references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by compiling your application: the options
available on the Debug menu), go back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

 

[KCi]

Hi Randy, another question for you gurus your calculation works awesome by
the way! But, in my database I have transactions that are not closed and
need to be worked within 2 business days, so there is no Resolved Date. Is
there a way to modify your code to calculate with the second date being null
to show the number of business days currently outstanding?

Thanks