RE: Calculating Date Fields Excluding Holidays and Weekends

Tech-Archive recommends: Fix windows errors by optimizing your registry

Here is a function that will perform the calculation for your.

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

You would call it like this:

CalcWorkDays([dtmCodingAssignedDate], [dtmCodingCompleteDate])

You will need a table of holidays that fall on weekdays( Monday-Friday).
For this function, it expects the name of the table to be [holdate] and the
table name to be holidays. If you choose different names, change the code to
suit your table and field names. My table has two fields:
[holdate] - date of the holiday
[Description] - The name of the holiday.

Put the function in a standard module so it can be used anywhere in your
application.


"JS" wrote:

I created a report in my database that has many date fields. I need to find
the Total Work Days from Receipt between Date Assigned and Date Completed
fields. I created a text box and typed the following expression in the
control source: =[dtmCodingAssignedDate]-[dtmCodingCompleteDate]. This is
great however I need to extract the weekends and holidays.

I tried searching the Internet and Discussion Group but found nothing that I
can understand fully. I realize a holidate table will need to be created
however I am not well versed in VBA. Please be gentle if code is the only
solution. So much easier in Excel!

Many thanks to those who are willing to help!!
.

Relevant Pages