# Thread: Calculating Holidays and Workdays (97)

1. ## Calculating Holidays and Workdays (97)

Below is a copy of the Module I am currently using to calculate the total number of days between ReqDate and CompDate, excluding weekends. The end result is then put onto a report which shows how many items were completed (CompDate) in 1 day, 2 days, 3 days, etc...I cannot figure out how to insert some type of code into this existing module to also exclude weekends. I have a table called Holidays and the field name for the actual holiday is called Holidate. Does anyone have any experience with this? I've looked at the "Doing WorkDay Math in VBA" and can't make heads nor tails of it. I am not a programmer so I'm looking for something simple. Can anyone help me create some code?

Function DateDiffW(ReqDate, CompDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If ReqDate > CompDate Then
DateDiffW = 0

Else
Select Case WeekDay(ReqDate)
Case SUNDAY: ReqDate = ReqDate + 1
Case SATURDAY: ReqDate = ReqDate + 2
End Select
Select Case WeekDay(CompDate)
Case SUNDAY: CompDate = CompDate - 2
Case SATURDAY: CompDate = CompDate - 1
End Select

NumWeeks = DateDiff("ww", ReqDate, CompDate)
DateDiffW = NumWeeks * 5 + WeekDay(CompDate) - WeekDay(ReqDate)
End If
End Function

2. ## Re: Calculating Holidays and Workdays (97)

----------------
I have a table called Holidays and the field name for the actual holiday is called Holidate
---------------------
Why not identify your weekends in this table? The same way you identify holidays. That would keep it simple.

3. ## Re: Calculating Holidays and Workdays (97)

First of all, you need to understand what the value returned by "ww" signifies. It DOESN'T mean the number of 7-day intervals! It stands for the difference between the week# of the first date and the week# of the 2nd date. So a 2-day date difference between Tuesday and Thursday is 0 weeks; but between Saturday and Monday is 1 week!

So, you can really think of the # of weeks as the number of weekends. Your formula will fail because multiplies #Weeks by 5. You need to adjust this by comparing the day of the week of the 2 dates. Use the WordDay( ) function get the day of the week of each date, if the day of 2nd date is < day of 1st date, then a full week hasn't transpired.

4. ## Re: Calculating Holidays and Workdays (97)

After giving it more thought, I think you can use this function:
<pre>public function gfWorkingDays (Beginning as Date, Ending as Date) as Integer
'assumptions:
' Working days are M-F
' Day started and Day ended are counted a full days.
' tblHolidays contains only holidays that fall on M-F

dim intDays as Integer

if Beginning>Ending then
gfWorkingDays=0
exit function
end if

intDays = datediff("d",Beginning,Ending) + 1
intDays = intDays - 2 * DateDiff("ww", Beginning, Ending)
intDays = intDays + (Weekday(Beginning)=vbSunday)
intDays = intDays + (Weekday(Ending)=vbSaturday)
intDays = intDays - DCount("*","tblHolidays", _
"Holiday BETWEEN " & Beginning & " AND " & Ending)
gfWorkingDays = intDays
end Function
</pre>

5. ## Re: Calculating Holidays and Workdays (97)

Mark has to add an assumption for his function: Beginning and Ending has to be in the same year.
To avoid this, and avoid the holidays has to be M-F, try the following function.
If your Holidays table in not attached, you can use a Seek instead of FindFirst to speed up the function.
<pre>Function WorkDays(Beginning As Date, Ending As Date) As Integer
Dim db As Database
Dim rst As Recordset
Dim LoopDay As Date
Dim strCriteria As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblHolidays", dbOpenDynaset)
WorkDays = 0
LoopDay = Beginning
Do While LoopDay <= Ending
If Weekday(LoopDay) <> vbSaturday And Weekday(LoopDay) <> vbSunday Then
strCriteria = "holidate = #" & Format(LoopDay, "mm/dd/yyyy") & "#"
rst.FindFirst strCriteria
If rst.NoMatch = True Then
WorkDays = WorkDays + 1
End If
End If
LoopDay = LoopDay + 1
Loop
Set rst = Nothing
Set db = Nothing
End Function
</pre>

Up to you to choose between the two functions. Mark's function is much faster.

Francois

6. ## Re: Calculating Holidays and Workdays (97)

Thanks you guys for you help!!! I'm OK using the FindFirst. I had to setup my master query so that it uses an expression to get to the Workdays function! I can't thank you enough!!!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

7. ## Re: Calculating Holidays and Workdays (97)

I am needing to do the same thing. How did you setup your query to use an expression to get to the Workdays function?

Thanks,

Patty

8. ## Re: Calculating Holidays and Workdays (97)

My only guess is to link your data to Excel and then run it through the workday function that accounts for both weekends and holidays. Then import the data back into Access. I am currently working on the same problem. Let me know if this helps

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•