DateDiff help

[wjmjr]

I need to caculate the difference in days between to dates. I would like to include only Business days but would settle for any help at this point

Thanks in advance
-WM

 

[RuralGuy]

Here's a function you may use:

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

'-- 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

exit_workingDays:
   Exit Function

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

End Function

 

[cooperwright]

I have a question on using this code... I'm using it to run a query on elapsed business days from date A to B, but date B is sometimes null. It's returning a "#Error" for the records that have a null date B, which is fine -- but when I ask it to return only the records that have over 5 elapsed days, it gives me a data type mismatch error.

Right now all I have in the field is
SS Elapsed: BizDays([CN Activity Date],[SS date])

I've also used the code for another query with no null fields, so I'm sure that this is what's causing the problem.

I'm very new to using Access for anything more than extremely simple functions, so I'm sure there is an easy fix for this! I've looked on various message boards, tutorials, etc, but haven't come across anything that specifically addresses this.

Thanks for any help...

Cooper

 

[RuralGuy]

You can fix this in either the query or this code. The big question is what to do if both dates are not date values! I would probably return with an error value:

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
   '-- 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

exit_workingDays:
   Exit Function

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

End Function

I also do not check if EndDate is > than Startdate.

 

[cooperwright]

a very late reply, but thanks so much, rural guy. the code worked perfectly and my query is now running beautifully!

 

[RuralGuy]

Thanks for taking the time to post back with your success and glad I could help.