Is there a DateDiff function that counts business days?

[Seth Schrock]

I am trying to create a report that tells us how long it takes work to get between stages. Since we don't work on Sundays or federal holidays, I was hoping to omit those days from the count. We do work Saturdays so they would need to be included. Basically, I want a count of 1 if it left stage 1 on Saturday and left stage 2 on Monday. Is this possible, or do I just need to tell management that the count includes Sundays and holidays?

[zmbd]

Counting the Number of Working Days in Access 2007

[ADezii]

I created a simple Function that will count the number of Workdays, including Saturdays, but not Sundays and Federal Holidays. Create a Table named tblHolidays with a single DATE/TIME Field named [Date]. Populate this Table with any and all work related Holidays, then execute the Code

Public Function fCalcWorkingDays(dteStartDate As Date, dteEndDate As Date) As Integer
Dim intCount As Integer
  
intCount = 0

Do While dteStartDate <= dteEndDate
  Select Case Weekday(dteStartDate)
    Case Is = 1
      'It's a Sunday, do nothing
    Case Is = 2, 3, 4, 5, 6, 7      'Monday thru Saturday
      'is it a Holiday as posted in tblHolidays?
      If DCount("*", "tblHolidays", "[Date] = #" & dteStartDate & "#") < 1 Then     'NOT Holiday
        intCount = intCount + 1
      End If
  End Select
    dteStartDate = dteStartDate + 1
Loop
  fCalcWorkingDays = intCount
End Function

Executing fCalcWorkingDay s(#12/1/2012#,#12/31/2012#) will return the Value 25 since there are 5 Sundays in December as well as Christmas.

[NeoPa]

Logically, Access cannot be programmed to know about federal holidays as holidays around the world cannot be accurately predicted in advance. They change for various reasons and Access would need to be able to handle all holidays for all countries if it were to offer that functionality. I wasn't involved in designing Access but I'd be prepared to bet that facility isn't available, as it wouldn't make sense.

That said, the number of days in a date range is pretty easily available, so starting with that is a decent basis to work from (in order to produce your own function to return this value). Knowing the number of days in the range and the start day of the week you can easily determine how many Sundays fall within that range. All that's left now is for the number of public holidays to be subtracted from the number. You have a date range, remember.

You now need to maintain a table of public holiday dates in your database (or even some central BE database that all your projects can share). With that available you can either look up all the values in the date range or, more efficiently, link that table into any query you're using to ensure the relevant dates are already available. Be careful of Sundays. They either need to be omitted completely from the table or the function will need to ignore them when it determines the public holidays to subtract.

So, this is certainly not as trivial as finding the number of days in a date range, but nor is it excessively complicated. It does, however, mean that there is a new responsibility of ensuring this new table is always up-to-date and adequately populated.

[Seth Schrock]

That is exactly what I was needing, but I certainly didn't expect it to be that complicated. Thankfully Copy/Paste worked pretty well. I just had to change the table name to fit my database, but other than that it worked perfectly.