# How can I calculate weekdays eliminating holidays in access?

=?Utf-8?B?TWlraQ==?=
Guest
Posts: n/a

 1st Oct 2007
I already can calculate the weekdays; but, eliminating holidays has me stumped

=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a

 1st Oct 2007
You need a holiday table with an entry for every none working weekday. You
do not want to include a holiday that is on a Saturday or Sunday because it
will get reversed out twice, once for being a week end day and once for being
a holiday. Here is a function that will return the number of working days
between two dates including holidays and week end days.

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

--
Dave Hargis, Microsoft Access MVP

"Miki" wrote:

> I already can calculate the weekdays; but, eliminating holidays has me stumped

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post MagicBill Microsoft Excel Programming 2 17th Dec 2008 05:20 AM Spencer Hutton Microsoft Excel Worksheet Functions 1 11th Sep 2004 10:54 PM Pedro Microsoft Access Reports 0 22nd Mar 2004 06:07 PM Shadow Microsoft Excel Misc 2 22nd Jan 2004 02:46 AM Dave Microsoft Access Queries 1 9th Aug 2003 12:13 AM

Features