NetworkDays Function 2

[jaaret]

Does Access have a function similar to Excel's NetWorkDays function? I would like to have a calculated field that displays the number of work days between now and a due date.

Thanks,
Jaaret

 

[RuralGuy]

The answer to your question is no so try this:
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'-- Return the number of WorkingDays between StartDate and EndDate
On Error GoTo err_workingDays

Dim intCount As Integer

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate >= StartDate Then
'-- Force Incoming Dates into consistant format
StartDate = CDate(Format(StartDate, "Short Date"))
EndDate = CDate(Format(EndDate, "Short Date"))

intCount = 0
Do While StartDate < EndDate
StartDate = StartDate + 1
If Weekday(StartDate, vbMonday) <= 5 Then
'-- Use the following code if you have a "Holiday" table
' If Weekday(StartDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & StartDate & "#")) Then
intCount = intCount + 1
End If
Loop
WorkingDays = intCount
Else
WorkingDays = -1 '-- To show an error
End If
Else
WorkingDays = -1 '-- To show an error
End If

exit_workingDays:
Exit Function

err_workingDays:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume exit_workingDays

End Function


HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[jaaret]

I'm approaching this a little differently. I've created a table with workdays (Saturdays, Sundays and Holidays omitted) with a sequential number corresponding with each date. I'm attempting to look up the sequential number with the DLookUp function:

DLookUp("DayNum","tblWorkDays","WorkDay =" & FormatDateTime([due],2))

The WorkDay field is in ShortDate format. The DLookUp function is returning a zero length string (not an error). Why is it not returning the DayNum value?

 

[lespaul]

The WorkDay field is in ShortDate format

actually that's just the way access shows you the date. It is actually something like:

34125

and that's the number of days since 12/31/1899

have you tried:

DLookUp("DayNum","tblWorkDays","WorkDay = #" & [due] & "#")



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lespaul]

and just as an aside, you may want to change the information in the table.

You can determine if a date is a Saturday or Sunday with built in functions, so you don't need those in your table. Plus if you only have to enter the 12 or so holidays each year that's a lot less data entry than entering ALL the work days each year.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jaaret]

Thank you - the adding the "#" symbols did the trick.

Darrell