Calculating Business Days Problem

[algecan]

I have a database where I need to calculate the business days taken between the start date and finish date of jobs. This calculation also needs to incorporate holidays.

To get started I implemented the modules from http://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx#Access2007CountingWorkingDays_CalculatingWorkingDays. All was working fine until I noticed a few anomalies with some of the calculations. I picked on one and tried to find the problem with it - this was Job Start 1/4/14 and Job Finish 8/4/14.

Going through the modules I noticed that the Dcount function in the Workdays() procedure was returning 4 days instead of zero. I messed around with the tables and tried assigning dates straight to the relevant variables rather than getting them from the tables in case they were stored incorrectly. I noticed that if I removed 4 certain dates from the holiday table, the calculation was correct even though these holiday dates did not fall between 2 dates passed to the procedure. I then created a new database to make sure there wasn't any issue with the way the dates were being stored but found the same problem. I have attached this database as an example including the 4 dates that cause the issue. You can change these dates around and it has different effects, for example change the years to 2011 and it works from.

I then had a search on this forum and found the following page, http://www.access-programmers.co.uk/forums/showthread.php?t=238821&highlight=Holiday.
I downloaded this database and added the dates stored in my database into this one (Job start 1/4/14, Job Finish 8/4/14, Holidays; 18/4/14, 21/4/14, 5/5/14, 26/5/14) and this also incorrectly calculates the difference. Any idea why these dates are causing problems? Any help would be greatly appreciated.

 

[pbaldy]

Watch out for the date format:

http://allenbrowne.com/ser-36.html#Format

 

[algecan]

I have considered that and messed around with the dates as much as I can. The dates in the holiday table are stored as dd/mm/yyyy and i also use the DateValue function to ensure the dates being used are in the correct format. Not sure if I can do any more than that though.

 

[pbaldy]

If it's a date/time field, the dates are NOT stored as dd/mm/yyyy, they are just formatted for you that way. Anywhere in code you're passing a date you need to take Allen's advice to heart. Since you haven't posted your code, I can't say exactly where that may be.

 

[algecan]

OK. The code I use is

Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays.
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
   strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

I'm not sure if I am understanding Allen's site correctly but I have started changing things like this with no success.

   strWhere = "[Holiday] >= #" & Format(startDate, "dd/mm/yyyy") _
        & "# AND [Holiday] <= #" & Format(endDate, "dd/mm/yyyy") & "#"

 

[pbaldy]

You've missed the point that you have to use mm/dd/yyyy not dd/mm/yyyy when in code.

 

[algecan]

Ah sorry, I thought it meant I could specify how I want to format and use the date.

I've just changed it around to the below which seems to sort the issue out.

   strWhere = "[Holiday] >= #" & Format(startDate, "mm/dd/yyyy") _
        & "# AND [Holiday] <= #" & Format(endDate, "mm/dd/yyyy") & "#"

Many thanks for your assistance.

 

[pbaldy]

No problem.