# Solved: Access DateDiff

Discussion in 'Business Applications' started by computerman29642, Oct 10, 2008.

Not open for further replies.

Joined:
Dec 4, 2007
Messages:
2,895
I have setup a query to use the DateDiff function.

Code:
```Expr1: DateDiff("d",date1,date2)
```
As you can see, I am using the Days interval. Is there a way to limit that to only work days (M-F)?

2. ### Ziggy1

Joined:
Jun 17, 2002
Messages:
2,551
Check this out... http://www.techonthenet.com/access/modules/workdays.php

Here is the text for anyone else...links sometimes change over time ( for anyone looking at this in the future).

Access: Calculate the number of workdays between two dates in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I need to calculate the number of workdays between two dates (ie: elapsed days excluding Saturdays and Sundays).

For example, if I have 3/4/2005 and 3/29/2005, the number of workdays is 17. How can I calculate this?

Answer: You can calculate the number of workdays between two dates using our function below:

Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:

Function CalcWorkdays(StartDate, EndDate) As Integer

Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer

On Error GoTo Err_Execute

CalcWorkdays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate <= StartDate Then
CalcWorkdays = 0
Else
LTotalDays = DateDiff("d", StartDate - 1, EndDate)
LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkdays = LTotalDays - LSaturdays - LSundays

End If
End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkdays = 0

End Function

#### Attached Files:

• ###### ScreenShot_002.jpg
File size:
32.3 KB
Views:
508

Joined:
Dec 4, 2007
Messages:
2,895
Thanks Ziggy. I wiil take a look at th elink provided.

As Seen On