# Thread: calculate working days between 2 dates

1. Registered User
Join Date
Sep 2003
Location
Washington, DC
Posts
116

## Unanswered: calculate working days between 2 dates

Is there a way to calculate the number of working days between 2 dates in MSAccess? Excel has this funtionality (NETWORKDAYS).

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
does excel count holidays? if so, which country's holidays?

the "best practices" answer to this question (and it does seem to come up all over the place) is to join your data table to a calendar table

3. Registered User
Join Date
Sep 2003
Location
Washington, DC
Posts
116
Excel will calculate holidays if you tell it what the holidays are. If you do a search in Excel's help file for NETWORKDAYS, you'll see what I'm talking about.

4. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
If you just want the number of normal "working" days, i.e. Monday thur Friday, use DateDiff with the set to "w". Counting holidays, as indicated above, would be much more problematical; what country, what kind of holidays?

WorkDaz = DateDiff("w", StartingDate, EndingDate)

5. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
I wrote this a long time ago. I've since converted them to MSSQL sprocs, so I'm not entirely sure they still work as is, but...

It counts workdays in a given month, and excludes the holidays that
my company takes. The date passed to the CountWorkDays_Total
function can be any date, and the function finds the first day of the month,
and the total days in the month. You would have to make some changes,
where you'd pass in the begin and end dates.

The second function checks to see if the current date in the loop is a holiday.

Code:
```Function CountWorkDays_Total(dtmDate) As Byte
Dim bCntDay As Byte, dtmTemp As Date, bDaysInMnth As Byte, bLastDay As Byte, dtmFirstDay, intCntWDay As Integer
intCntWDay = 0
For bCntDay = 0 To bLastDay - 1
Select Case DatePart("w", dtmTemp)
Case vbSunday, vbSaturday
'make sure Xmas or New Years or Jul 4th don't fall in the weekend
If DatePart("m", dtmTemp) = 1 And DatePart("d", dtmTemp) = 1 Then
intCntWDay = intCntWDay - 1
End If
If DatePart("m", dtmTemp) = 12 And DatePart("d", dtmTemp) = 25 Then
intCntWDay = intCntWDay - 1
End If
If DatePart("m", dtmTemp) = 7 And DatePart("d", dtmTemp) = 4 Then
intCntWDay = intCntWDay - 1
End If
Case Else
If Not IsHoliday(dtmTemp) Then
intCntWDay = intCntWDay + 1
End If
End Select
Next bCntDay
CountWorkDays_Total = intCntWDay
End Function

Function IsHoliday(dtmDate) As Boolean
'determines if the passed in date is a holiday
Dim dtmXmas As Date, dtmNwYrs As Date, dtmJLY4 As Date
dtmXmas = CDate("12/25/" & Year(dtmDate))
dtmNwYrs = CDate("1/1/" & Year(dtmDate))
dtmJLY4 = CDate("7/4/" & Year(dtmDate))
IsHoliday = False
If dtmDate = dtmXmas Then
IsHoliday = True
ElseIf dtmDate = dtmNwYrs Then
IsHoliday = True
ElseIf dtmDate = dtmJLY4 Then
IsHoliday = True
Else
'check for monday holidays
If DatePart("w", dtmDate) = 2 Then
'check for May and Sep
If DatePart("m", dtmDate) = 9 Then
'check if it's the first Monday
If DatePart("d", dtmDate) < 8 Then
IsHoliday = True
End If
ElseIf DatePart("m", dtmDate) = 5 Then
'check if it's the last monday
If DatePart("d", dtmDate) > 24 Then
IsHoliday = True
End If
End If
ElseIf DatePart("w", dtmDate) = 5 Then
If DatePart("m", dtmDate) = 11 Then
If DatePart("d", dtmDate) >= 21 Then
'4th Thurs in November=Thanksgiving
IsHoliday = True
End If
End If
End If
End If
End Function```

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by PatrickFAO
If you do a search in Excel's help file for NETWORKDAYS, you'll see what I'm talking about.
okay, i did, and i am seriously underimpressed

apparently when calling the function, you would have to feed it a list of holiday dates to exclude!!

how wonderfully useful is that, eh

7. Registered User
Join Date
Apr 2006
Posts
157
I think I used this when workdays included saturdays

Code:
```Function NetWorkDays(STARTerDATE As Date, ENDerDATE As Date) As Integer

Dim TESTDATE As Date

NetWorkDays = 0

TESTDATE = STARTerDATE

While TESTDATE <= ENDerDATE

If Not (Weekday(TESTDATE) = 7) Then
NetWorkDays = NetWorkDays + 1
End If

TESTDATE = TESTDATE + 1

Wend

End Function```

if you exclude saturdays too

turn this

Code:
` If Not (Weekday(TESTDATE) = 7) Then`
into this

Code:
` If Not ((Weekday(TESTDATE) = 7) Or (Weekday(TESTDATE) = 1)) Then`
anyway, you still have the weekday function to emulate the networkdays of excel

you may even add an additional array parameter to the function containing holidays, loop through

8. Registered User
Join Date
Jul 2004
Location
UK
Posts
24
This is a function I use (based on Mon-Fri working week). It works quicker than cycling through every day, especially if the two dates are quite a way apart.

Code:
```Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'Return number of working days between two dates
Dim Count As Integer

StartDate = StartDate + 1 'Don't count the first day!

Count = Int((EndDate - StartDate) / 7) 'number of whole weeks
StartDate = StartDate + (Count * 7)
Count = Count * 5
Do While StartDate <= EndDate
Select Case Weekday(StartDate)
Case 2 To 6 'Mon - Fri
Count = Count + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = Count

End Function```
Hope it's useful!

Qpid.

9. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Originally posted by r 937:
apparently when calling the function, you would have to feed it a list of holiday dates to exclude!! how wonderfully useful is that, eh
And exactly how would you expect any code to exclude holidays without being told what the holidays were, eh? I don't know how things are done in Canada, but in the US we have national, state, local and religious holidays! Some are observed by some companies and others are not. Some occur on the same date each year but most don't. Intelligent software can only can only do so much!

10. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Qpid,

In the days before Vb gave us great date handling functions your hack posted above was short and sweet, but why would you want to use now, when

Workdaz = DateDiff("w", StartingDate, EndingDate) does the same thing?

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by Missinglinq
And exactly how would you expect any code to exclude holidays without being told what the holidays were, eh?
this is my point exactly!!!

why would you want to feed this list to every query which needs to calculate working days? why wouldn't you store this very valuable knowledge in a central place where it's easy to see, easy to validate, and easy to maintain? in a calendar table!!

the calendar table has the added advantage that joining to it is way more efficient than looping in a function

12. Registered User
Join Date
Sep 2003
Location
Washington, DC
Posts
116
I've tried using this function:

Workdaz = DateDiff("w", StartingDate, EndingDate)

But this only returns the number of times that specific day (e.g. StartingDate=Monday) has occurred in this time sequence.

13. Registered User
Join Date
Oct 2007
Posts
127
I have just enterered the following code in MS Access - Macro and saved it...

Code:
```Function NetWorkDays(STARTerDATE As Date, ENDerDATE As Date) As Integer

Dim TESTDATE As Date

NetWorkDays = 0

TESTDATE = STARTerDATE

While TESTDATE <= ENDerDATE

If Not ((Weekday(TESTDATE) = 7) Or (Weekday(TESTDATE) = 1)) Then
NetWorkDays = NetWorkDays + 1
End If

TESTDATE = TESTDATE + 1

Wend

End Function```
In the Immediate tab at the bottom I enter the following to check if the code works.

?NetWorkDays(01/01/2007,30/09/2007)
0

It brings back 0

What am I doing wrong? Why returning the correct date difference?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by PatrickFAO
I've tried using this function:

Workdaz = DateDiff("w", StartingDate, EndingDate)

But this only returns the number of times that specific day (e.g. StartingDate=Monday) has occurred in this time sequence.
what happend if you try to calculate the difference in days rather than weeks?

15. Registered User
Join Date
Oct 2007
Posts
127
Originally Posted by healdem
what happend if you try to calculate the difference in days rather than weeks?

Workdaz = DateDiff("d", StartingDate, EndingDate)

#### Posting Permissions

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