My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 44 Joined: 11-August 05 ![]() | 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 |
![]() Post#2 | |
![]() UA Admin Posts: 36,172 Joined: 20-June 02 From: Newcastle, WA ![]() | My first thought is to ask if your holiday date table is up-to-date? |
![]() Post#3 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | 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 |
![]() Post#4 | |
Posts: 44 Joined: 11-August 05 ![]() | The holiday table is up to date to the beginning of next year (2012), so that shouldn't be the problem. |
![]() Post#5 | |
Posts: 44 Joined: 11-August 05 ![]() | 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 |
![]() Post#6 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | You set it to False or True when calling the function. True in your case. gustav |
![]() Post#7 | |
Posts: 44 Joined: 11-August 05 ![]() | 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? |
![]() Post#8 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | 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 |
![]() Post#9 | |
Posts: 44 Joined: 11-August 05 ![]() | 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 |
![]() Post#10 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | 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 |
![]() Post#11 | |
Posts: 44 Joined: 11-August 05 ![]() | 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 |
![]() Post#12 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | 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 |
![]() Post#13 | |
Posts: 44 Joined: 11-August 05 ![]() | 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 |
![]() Post#14 | |
Posts: 3 Joined: 15-October 12 ![]() | 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. |
![]() Post#15 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | 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 |
![]() Post#16 | |
Posts: 3 Joined: 15-October 12 ![]() | 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) |
![]() Post#17 | |
Posts: 3 Joined: 15-October 12 ![]() | Hi Gustav, pologies just noted a typo in my original post, the project concludes on the 30th April 2013. |
![]() Post#18 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | Here they are: CODE Public Function DateSkipWeekend( _ /gustavByVal 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 |
![]() Post#19 | |
Posts: 1 Joined: 24-October 12 ![]() | 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] |
![]() Post#20 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 From: Copenhagen ![]() | Well, you could use DateDiff to count the total days and deduct the working days. gustav |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 8th December 2019 - 03:36 AM |