Below is a function that does exactly what you want. It also omits holidays
in a holidays table. My holiday table has two fields holdate (Date/Tme) and
holdate_desc (text)
You can create your own holidays table or remove the code that checks for
holidays.
You can use it like this:
no days: CalcWorkDays([setup],[closing date])
Paste the function into a standard module so you can use it from anywhere in
your application.
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, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] 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
dlb1228 said:
awesome great thank you one other thing is there a way to not include
weekends for example saya the closing date is 7/21/2006 and set up date
is 7/24/2006 i dont want to count sat and sun
KARL DEWEY said:
Omit the Count.
dlb1228 said:
i have 2 dates closing date and setup date no days
1/23/2006 1/24/2006 1
i thought i could do something like this but its not working why?
count(datediff('d',[setup],[closing date])) its giving me wrong numbers
any idea what im doing wrong
