Calculate Working days ?

[Martin \(Martin Lee\)]

I have two fields: one is begining date, the other is ending date.

I want to make it calculate automatically for the working days between date1
and date2. ( Working days: Monday to Friday, except Saturday and Sunday)

In EXCEL, there is a function called =NETWORKDAYS(date1,date2) which can
calculate the working days. Is there any method in ACCESS?

Thank you.


Martin Lee

 

[Wayne-I-M]

Press F1 (help) and search on "DateDiff"

 

[Terry Kreft]

Function NetWorkDays(date1, date2) As Long
Dim lngRet As Long
Dim lngFullWeeksDays As Long
Dim lngOddDays As Long
Dim lngCount As Long

Const WORK_DAYS = 5

If Not IsDate(date1) Then Exit Function
If Not IsDate(date2) Then Exit Function

lngRet = DateDiff("d", date1, date2)
lngFullWeeksDays = (lngRet \ 7) * WORK_DAYS
lngOddDays = lngRet Mod 7

For lngCount = 1 To lngOddDays
Select Case DatePart("w", DateAdd("d", lngCount, date1))
Case vbSaturday, vbSunday
lngFullWeeksDays = lngFullWeeksDays - 1
End Select
Next
lngRet = lngFullWeeksDays + lngOddDays
NetWorkDays = lngRet
End Function

 

[Martin \(Martin Lee\)]

Can it possible to make it as a formula in a QUERY's field, rather than use
a FUNCTION ?

Thanks!

Martin Lee

 

[Douglas J Steele]

I've got an expression that can be used in a query in my September, 2004
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Wayne-I-M]

In a query use these

TotalDaysateDiff("d", [StartDate], [EndDate])

To get rid of Sat and Sun use this
WorkingDay:
[EndDate]-[StartDate]-(DateDiff("ww",[StartDate],[EndDate],7)-(Weekday([StartDate])=7))-(DateDiff("ww",[StartDate],[EndDate],1)-(Weekday([StartDate])=1))

Hope this helps

 

[Ron2006]

Similar Method:

Compute the number of working days between two dates:

WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))


First datediff is total number of days between dates.
Second datediff is number of Saturdays between dates
Third datediff is number of Sundays between dates.
If you also want to count BOTH the first AND last day as working days
then add 1 to result (ie. if start date is today and end date is
tomorrow is that 2 working days or 1 working day. If two days and start
date is always a workday then add 1 to working days, probably.)

Ron