Calculate Working days ?

  • Thread starter Thread starter Martin \(Martin Lee\)
  • Start date Start date
M

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
 
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
 
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
 
Can it possible to make it as a formula in a QUERY's field, rather than use
a FUNCTION ?

Thanks!

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

Thanks!

Martin Lee
 
In a query use these

TotalDays:DateDiff("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
 
In a query use these

TotalDays:DateDiff("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
 
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
 
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
 
Back
Top