[X]My Assistant

[AnneM]

Hi All,
know this subject seems to rear its head every so often, but I'm hoping you'll be generous enough to respond to my query.
I have a function that I use to calculate the number of work days between two dates. The function is set up (so I thought) to exclude weekends and public holidays, but I now find that it appears to only exclude weekends, and is including public holidays in error.
Can anyone tell me what I'm doing wrong, and how I can correct it, so that it excludes weekends and public holidays, please?
This is the function I currently use in an access 2003 environment:
Public Function WorkDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
'
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named PublicHoliday_tbl with a field named Holiday_Dte.
'....................................................................
On Error GoTo Err_WorkDays
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Holiday_Dte] FROM PublicHoliday_tbl", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[Holiday_Dte] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop
WorkDays = intCount
Exit_WorkDays:
Exit Function
Err_WorkDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkDays
End Select
End Function

[GroverParkGeorge]

My first thought is to ask if your holiday date table is up-to-date?

[Gustav]

We use this function which is somewhat different:

CODE

Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Long
#39; Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.
' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.
  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"
  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim lngDays               As Long
  Dim datDateTemp           As Date
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim lngHolidays           As Long
  Dim strFilter             As String
  
  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If
  
  ' Find ISO weekday for Sunday.
  bytSunday = WeekDay(vbSunday, vbMonday)
  
  ' Find weekdays for the dates.
  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
  
  ' Compensate weekdays' value for non-working days (weekends).
  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
  
  ' Calculate number of working days between the two weekdays, ignoring number of weeks.
  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
  ' Add number of working days between the weeks of the two dates.
  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
  
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
    lngHolidays = DCount("*", cstrTableHoliday, strFilter)
  End If
  
  ISO_WorkdayDiff = lngDays - lngHolidays
End Function

/gustav

[AnneM]

The holiday table is up to date to the beginning of next year (2012), so that shouldn't be the problem.

[AnneM]

Hi Gustav,
've tried using your code (re-naming the table and field name to match), but the field "booExcludeHolidays" seems to cause a problem. Not sure what field of data, if any, to assign to it. If I remove it, it only excludes weekends.
What have I done wrong?
Thanks

[Gustav]

You set it to False or True when calling the function. True in your case.
gustav

[AnneM]

Gustav,
Thanks for the clarification on the code. Unfortunately I'm still seeing problems with the final calculations.
For example 1 record has a start date of 8th Dec 2010 and an end date of 11th Jan 2011. To calculate correctly the function should exclude weekends and holidays as defined in the holiday table (x-mas is a weekend so those dates are not in the table. However the following dates should be removed 27th - 31st Dec 2010 - non work days. 1st and 2nd Jan 11 are also on a weekend, so not included in the table, and 3rd Jan 11 is also a non work day to be removed via the function).
Doing this manually gives me a work day calculation of 19 days. My original function gives me 20 work days, but Gustav's function only returns 11 work days.
A second example: record start date 22 Mar 2010 and end date 7 Apr 2010. Excluding weekends and the dates for Easter (2nd and 5th April). Calculating this manually brings back 11 work days. My function = 13 work days. Gustav's function = 8.
Holday dates that I use are all in UK date format e.g. 12/05/2010 (12 May 2010).
I have no idea why it doesn't work, why the errors are not consistant or what to do to correct.
Gustav? Anyone? Any ideas, please?

[Gustav]

The correct result is none of the mentioned - it is 18 days - which is returned by my function.
Please note that if start date and end date are identical, count of workdays is zero. If you always wish to include the start date, add 1 to the result count.
ount of non-weekend days is:
2010 December
Week 49: 2 days (2010-12-08 and -09)
Week 50: 5 days
Week 51: 5 days
Week 52: 5 days
Total December: 17
2011 January
Week 1: 5 days
Week 2: 2 days
Total January: 7
Total days: 24
Count of holidays is:
2010 week 52: 5 days
2011 week 1: 1 day
Total holidays: 6 days
Total workdays: 24 - 6 = 18
The second example should return 10 days and my function returns this.
2010 March:
Week 12: 4 days
Week 13: 3 days
Total March: 7 days
2010 April:
Week 13: 2 days
Week 14: 3 days
Total April: 5 days
Total days: 12
Count of holidays is:
2010 week 13: 1 day
2011 week 14: 1 day
Total holidays: 2 days
Total workdays: 12 - 2 = 10
HAs you mention a UK format for dates, I suspect the root for your issue to be your holiday table. The field for the holidates must be of data type Date and the applied format can be any (or none) as it is for display only.
You can include this debug line - before the last code line - to check you get it right:
Debug.Print "Days: "; lngDays, "Holidays:"; lngHolidays
ISO_WorkdayDiff = lngDays - lngHolidays
Finally, you do not need to exclude holidays that fall in weekends from the holiday table; the code filters out these dates to remove them from the final count of holidays.
/gustav

[AnneM]

Hi Gustav,
've amended the penultimate line of code and amended the holiday date to date/time format. Unfortunately I'm getting exactly the same results as before.
The example you believe should output to 18 work days is still coming back as 11 work days.
The example you believe should output to 10 work days is still coming back as 8 work days.
Just to be clear, in case I've missed something crucial, I'm working in an Access 2003 environment. My holiday table is named tblHoliday, the field name is called, HolidayDate, the data type is Date/Time with no format stipulation.
The entire function code now in use is:
Public Function ISO_WorkdayDiff( _
ByVal datDateFrom As Date, _
ByVal datDateTo As Date, _
Optional ByVal booExcludeHolidays As Boolean) _
As Long
' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.
' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
' Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.
Const cbytWorkdaysOfWeek As Byte = 5
' Name of table with holidays.
Const cstrTableHoliday As String = "tblHoliday"
' Name of date field in holiday table.
Const cstrFieldHoliday As String = "HolidayDate"
Dim bytSunday As Byte
Dim intWeekdayDateFrom As Integer
Dim intWeekdayDateTo As Integer
Dim lngDays As Long
Dim datDateTemp As Date
Dim strDateFrom As String
Dim strDateTo As String
Dim lngHolidays As Long
Dim strFilter As String

' Reverse dates if these have been input reversed.
If datDateFrom > datDateTo Then
datDateTemp = datDateFrom
datDateFrom = datDateTo
datDateTo = datDateTemp
End If

' Find ISO weekday for Sunday.
bytSunday = Weekday(vbSunday, vbMonday)

' Find weekdays for the dates.
intWeekdayDateFrom = Weekday(datDateFrom, vbMonday)
intWeekdayDateTo = Weekday(datDateTo, vbMonday)

' Compensate weekdays' value for non-working days (weekends).
intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)

' Calculate number of working days between the two weekdays, ignoring number of weeks.
lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
' Add number of working days between the weeks of the two dates.
lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))

If booExcludeHolidays And lngDays > 0 Then
strDateFrom = Format(datDateFrom, "dd\/mm\/yyyy")
strDateTo = Format(datDateTo, "dd\/mm\/yyyy")
strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
lngHolidays = DCount("*", cstrTableHoliday, strFilter)
End If

Debug.Print "Days: "; lngDays, "Holidays:"; lngHolidays
ISO_WorkdayDiff = lngDays - lngHolidays
End Function
The query environment is: ISO_WorkdayDiff («datDateFrom», «datDateTo», «booExcludeHolidays») ...ExcludeHolidays set to True.
For whatever reason, it still isn't working correctly.
Your function is not currently returning the days you believe it should. Is there something I've done or missed which is messing with your code?
Anne

[Gustav]

And what did the Debug line print? Correct days' count and incorrect holidays' count, I'm sure.
That's what may happen if you change these lines:
strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
/gustav

[AnneM]

Gustav,
mending those final date formats in the function code seems to have done the trick. The function is calculating correctly now. Finally!
Thank you so much for all of your help and for your patience with me, Gustav. You're a genius and a life saver. ;-)
Annie x

[Gustav]

Thanks. You are welcome!
One additional note which may not be relevant for your scenario:
The small trick of always adding one day to include both start and end date may fail if one of these falls in a weekend.
/gustav

[AnneM]

I'll have to be wary of that, although start and end dates should always be on a weekday for my organisation.
Thanks again.
Annie

[BarryM]

Hi Gustav,
any thanks for the code you have provided this has assisted me in my task, substantially.
I am currently using Access 2010 and the calculations are not performing correctly. While Saturdays and Sundays are excluded from the calculations, the function is not excluding the holidays from the calculations. The following is the scenario I have:
I have a project which commenced on the 3rd September 2012 and concludes on the 30th April 2012. The total amount of working days is 153, which excludes all week-ends and 19 holidays. When I execute the function ISO_WorkdayDiff it presents me with a return of 172 working days, the difference being 19. I have tried other calculations with the same result, i.e. the holidays are excluded.
In addition to the above, I have tasks in my table which have a start date in the future. When I run the function I need this to provide me with a return of 0. The function is currently calculating the difference between TodaysDate () and the future start date. It would be greatly appreciated if you could assist me with the coding here.

[Gustav]

I don't know. Here it returns 170 days (= 34 weeks of 5 days)
1=#9/3/2011#
d2=#4/30/2012#
intDays = ISO_WorkdayDiff(d1, d2, False)

CODE

Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Long
' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.
' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.
' 2011-06-08. Rewrite using Skip functions.
  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"
  Dim booReverse            As Boolean
  Dim booSameWeekend        As Boolean
  Dim lngWeeks              As Long
  Dim lngDays               As Long
  Dim lngHolidays           As Long
  Dim lngWorkdays           As Long
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim strFilter             As String
  
  If Weekday(datDateFrom, vbMonday) > cbytWorkdaysOfWeek And _
    Weekday(datDateTo, vbMonday) > cbytWorkdaysOfWeek Then
    ' Both dates are of the same weekend.
    booSameWeekend = True
  End If
  
  Select Case DateDiff("d", datDateFrom, datDateTo)
    Case 0
      ' Zero days.
      Exit Function
    Case 1
      If booSameWeekend Then
        ' Both dates are of the same weekend.
        Exit Function
      End If
    Case -1
      If booSameWeekend Then
        ' Both dates are of the same weekend.
        Exit Function
      Else
        booReverse = True
      End If
    Case Is < -1
      ' Negative count.
      booReverse = True
    Case Else
      ' Positive count.
  End Select
    
  ' Adjust dates to skip weekends and holidays.
  datDateFrom = DateSkipNoneWorkingday(datDateFrom, booReverse)
  datDateTo = DateSkipNoneWorkingday(datDateTo, Not booReverse)
  
  ' Find count of full weeks.
  lngWeeks = DateDiff("w", datDateFrom, datDateTo)
  ' Calculate number of working days between the two weekdays ignoring holidays.
  lngDays = Weekday(datDateTo, vbMonday) - Weekday(datDateFrom, vbMonday) _
    + cbytWorkdaysOfWeek * DateDiff("ww", DateAdd("ww", lngWeeks, datDateFrom), datDateTo, vbMonday)
  
  If booExcludeHolidays Then
    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
    lngHolidays = IIf(booReverse, -1, 1) * DCount("*", cstrTableHoliday, strFilter)
  End If
  ' Add number of working days between the weeks of the two dates.
  ' Deduct count of holidays.
  lngWorkdays = lngDays + cbytWorkdaysOfWeek * lngWeeks - lngHolidays
  
  ISO_WorkdayDiff = lngWorkdays
End Function

To ignore future days you can use:
intDays = IIf(DateDiff("d", d1, Date) < 0, 0, ISO_WorkdayDiff(d1, d2, False))
/gustav

[BarryM]

Hi Gustav,
any thanks for the promt response.
Unfortunately I am now receiving a compile error when using the code you supplied earlier, I have attached the error I am receiving.
I have noted that the code differs from that provided on Mar 24 2011, 04:59 PM, was this done to cater for Access 2010?

Attached File(s)

 workdays.jpg ( 170.25K )Number of downloads: 36

 

[BarryM]

Hi Gustav,
pologies just noted a typo in my original post, the project concludes on the 30th April 2013.

[Gustav]

Here they are:

CODE

Public Function DateSkipWeekend( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date
#39; Purpose: Calculate first working day equal to or following/preceding datDate.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 1999-07-03, Gustav Brock, Cactus Data ApS, Copenhagen
  
  Const cintWorkdaysOfWeek As Integer = 5
  Dim bytSunday   As Byte
  Dim bytWeekday  As Byte
  
  bytSunday = Weekday(vbSunday, vbMonday)
  bytWeekday = Weekday(datDate, vbMonday)
  
  If bytWeekday > cintWorkdaysOfWeek Then
    ' Weekend.
    If booReverse = False Then
      ' Get following workday.
      datDate = DateAdd("d", 1 + bytSunday - bytWeekday, datDate)
    Else
      ' Get preceding workday.
      datDate = DateAdd("d", cintWorkdaysOfWeek - bytWeekday, datDate)
    End If
  End If
  DateSkipWeekend = datDate
End Function
Public Function DateSkipHoliday( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date
' Purpose: Calculate first day following/preceding datDate if this is holiday.
'
' 2009-04-12. Gustav Brock, Cactus Data ApS, Copenhagen
  ' Adjust to fit your table of holidays.
  Const cstrHolidayTable  As String = "tblHoliday"
  Const cstrHolidayField  As String = "HolidayDate"
  
  While Not IsNull(DLookup(cstrHolidayField, cstrHolidayTable, cstrHolidayField & " = " & Format(datDate, "\#m\/d\/yyyy\#")))
    datDate = DateAdd("d", 1 - Abs(2 * booReverse), datDate)
  Wend
  DateSkipHoliday = datDate
End Function
Public Function DateSkipNoneWorkingday( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date
' Purpose: Calculate first working day following/preceding datDate.
'
' 2009-04-12. Gustav Brock, Cactus Data ApS, Copenhagen
  
  Dim datNext As Date
  Dim datTest As Date
    
  datNext = datDate
  Do
    datTest = datNext
    datNext = DateSkipHoliday(datTest, booReverse)
    datNext = DateSkipWeekend(datNext, booReverse)
  Loop Until DateDiff("d", datTest, datNext) = 0
  
  DateSkipNoneWorkingday = datNext
End Function

/gustav

[dvibe]

On the oppsite side of the coin is there a way to calculate ONLY weekends (Sat & Sun) and holidays between two dates?
I have a "Holidays" table with dates in the column [HolDate]

[Gustav]

Well, you could use DateDiff to count the total days and deduct the working days.
gustav