Solved: Access DateDiff

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

computerman29642

Thread Starter
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)?
 
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
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top