Working Days Sample Database (1 Viewer)

[thedeadzeds]

Guys,

Does anyone have a sample 2016 database with a working days function in it? I have found the below link but i can't download as i have 2016

I need to be able to subtract dates from dates but exclude public holidays. I have found several examples online but can't quite figure it out. If i could get my hands on a sample database i think i could sort.

Thanks

https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx#Conclusion

 

[jdraw]

See this sample for ideas. It should work with 2016 desktop.

 

[Mark_]

As a note, if you wish to include "Public Holidays", you may need to include a "Holiday" table with the dates some holidays are celebrated on within your area. This will allow you to include dates that would otherwise not be tracked, especially if you need to include "Organization Specific" events.

 

[thedeadzeds]

Hi Guys,

So i have found a working sample but the calculation between the dates in qryExample seems to be one day out. Not sure if its to do with the below code. Database attached. Could someone help? Thanks

Option Compare Database
Option Explicit

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

 

[isladogs]

I haven't read the code in detail.
However, a quick scan of the code indicates that the first day of the week is being set as Monday. If you want the week to start on Sunday, try replacing vbMonday with VbSunday.

 

[thedeadzeds]

I've tried that but unfortunately no joy but thanks for looking Ridders

 

[isladogs]

3 questions before I test the code myself.
Where did you find the code?
Which day do you want your week to start?
Are your results one day early or late?

 

[thedeadzeds]

Thanks Ridders,

Where did you find the code? Sorrily i cant remember what site it was on i think it was from utteracess

Which day do you want your week to start? Weekdays should be Mon to Fri and weekend should be Saturday and Sunday so Monday I guess

Are your results one day early or late? My results are counting one too many days. For example,

datDatefrom = 26/03/18 (uk dates 26th March 18)
datDatefrom = 06/04/18
Result should be 8 days

tblHoliday shows the following as public holidays so the difference between the dates should be 8 days not 9

tblHoliday
ID Description HolidayDate
1 New years day 01/01/2018
2 Good Friday 30/03/2018
3 Easter Monday 02/04/2018
4 Early May bank holiday 07/05/2018
5 Spring bank holiday 28/05/2018
6 Summer bank holiday 27/08/2018
7 Christmas Day 25/12/2018
8 Boxing Day 26/12/2018


qry results ie working days column is Working days: ISO_WorkdayDiff([datDateFrom],[datDateTo])

ExampleID datDatefrom datDateto Working days
5 26/03/2018 06/04/2018 9
6 26/03/2018 03/04/2018 6
7 25/12/2018 27/12/2018 2
8 30/03/2018 30/03/2018 0
9 25/02/2018 25/02/2018 0

One thing you will notice is 25th Dec and 30th March is a public holiday and the count is correctly showing no days ExampleID's 8 and 9

 

[isladogs]

Your query doesn't EXCLUDE the holidays!

The function has an optional boolean clause booExcludeHolidays.
You need to set that as true in order to exclude them

ISO_WorkdayDiff([datDateFrom],[datDateTo],True)

Using your example dates in UK date format
datDatefrom = 26/03/2018 Monday
datDatefrom = 06/04/2018 Friday of following week
So 06/04/2018 is the 9th weekday AFTER 26/03/2018
But two of those dates are Good Friday & Easter Monday
So if we exclude them, 06/04/2018 is the 7th working day AFTER 26/03/2018

To get 8 days as your result, you must be counting the start date as well!
So you need to add 1

Amended query with 2 extra fields:
WorkingDaysEXCHolidays uses the function with the boolean field
UPDATEDWorkingDays adds 1 to that.

Choose the version that fits your requirements

SELECT tblExample.ExampleID, tblExample.datDatefrom, tblExample.datDateto, 
ISO_WorkdayDiff([datDateFrom],[datDateTo]) AS ALLWorkingDays, 
ISO_WorkdayDiff([datDateFrom],[datDateTo],True) AS WorkingDaysEXCHolidays, 
ISO_WorkdayDiff([datDateFrom],[datDateTo],True)+1 AS UPDATEDWorkingDays
FROM tblExample;
;

Results:

The WorkingDaysEXCHolidays column USUALLY gives the correct answer AS LONG as you realise it is counting the working days after the start
BUT there are some oddities as well e.g. ID 8 gives the expected result in the final column but only because the result from the function is -1

I think the function you used needs VERY careful checking with a variety of test dates before you trust it completely

 

[jdraw]

Here is a function that should do what you are asking.
The Holiday table is called tblHolidays.
I made a copy of your tblHoliday and set a reference to DAO.

Here is the query I used

SELECT tblExample.ExampleID
, tblExample.datDatefrom
, tblExample.datDateto
, workingdays2([datDateFrom],[datDateTo]) AS [Working days]
FROM tblExample;

and the result (I added few test cases to the table)

ExampleID	datDatefrom	datDateto	Working days
5	26-Mar-2018	06-Apr-2018	8
6	26-Mar-2018	03-Apr-2018	5
7	01-Jan-2018	03-Jan-2018	2
8	01-Jan-2018	05-Jan-2018	4
9	01-Jan-2018	06-Jan-2018	4
10	01-Jan-2018	07-Jan-2018	4
11	01-Jan-2018	08-Jan-2018	5

Public Function WorkingDays2(ByVal StartDate As Date, ByVal Enddate As Date) As Integer
      '....................................................................
      ' Name:     WorkingDays2
      ' Inputs:   StartDate As Date
      '   EndDate As Date
      ' Returns: Integer
      ' Author: Arvin Meyer
      ' Date:     May 5,2002
      ' 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 tblHolidays with a field named HolidayDate.
      'This uses default start of week vbSunday 1__________________JED
      '
      'adjustment: Feb 2/2016 JED
      ' I have added a ShowDebug boolean that allows you to test the days within your range and show them as
      ' weekend day or workday or holiday. You can turn it off by setting ShowDebug to false.
      '
      ' I also used a  ByVal so  as not to destroy the original startdate since the code keeps moving
      ' the startdate to the enddate during calculations.
      '
      'I also added a sort to the Holidays recordset to get oldest to newest dates.
      '
      '....................................................................
10        On Error GoTo Err_WorkingDays2
          
          Dim Showdebug As Boolean   '--------jed
20        Showdebug = True '------------------jed used to show workdayholiday for each date in range
          
          Dim OrigStart As Date
          Dim intCount As Integer
          Dim rst As DAO.Recordset
          Dim db As DAO.Database
          Dim adj As Integer
30        Set db = CurrentDb
40        Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays Order By HolidayDate desc;", dbOpenSnapshot)

          'sorting a date oldest to newest is descending??
          
50        intCount = 0
         
          
60        Do While StartDate <= Enddate

70            rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
80            If WeekDay(StartDate) <> vbSunday And WeekDay(StartDate) <> vbSaturday Then
90                If rst.NoMatch Then intCount = intCount + 1
100           End If
              
              'jed debuggng tool '********************************************************************************************
110           If Showdebug Then _
               Debug.Print StartDate & "  " & _
                IIf(WeekDay(StartDate) = vbSaturday Or WeekDay(StartDate) = vbSunday, "weekend day", _
                IIf(DCount("*", "tblHolidays", "holidaydate = #" & StartDate & "#") = 1, "*HOLIDAY*", "workday"))
              
120           StartDate = StartDate + 1

130       Loop

140       WorkingDays2 = intCount
Exit_WorkingDays2:
150       Exit Function

Err_WorkingDays2:
160       Select Case err

          Case Else
170           MsgBox err.Number & "  in line " & Erl & " (" & err.Description & ")"
180           Resume Exit_WorkingDays2
190       End Select

End Function

 

[thedeadzeds]

Thanks jdaw but when i run the query it shows the following error. Is this something i have not done my end?

'Compile error; user defined type not defined

 

[isladogs]

Jack's code requires an additional VBA reference:
For older MDB files, add Microsoft DAO 3.6 Object Library

I've added the function by Arvin Meyer that Jack posted but modifying the table name back to tblHoliday in the code

I then added that to the query I showed before as a field called WorkdaysJD
I also added 3 more records - all Mon-Fri in same week
ID=12 - this week - no holidays
ID=13 - includes Good Friday
ID=14 - includes Easter Monday

Prepare to be confused:

Updated database attached with query & added reference

You will need to decide which (if any) give the results you expect!

 

[thedeadzeds]

Firstly thank you everyone for helping with this. After doing a bit of testing it seems some dates are being classed as a holiday correctly and some are not.

Just to clarify and i'm not sure if this is the issue but datdatefrom is essentially the first day of the annual and datdateto is the last day of the annual leave.

I've test a few dates over 2018 and 2019 and get the following anomalies highlighted in yellow.

 

[isladogs]

Why do the first two numbered columns mainly show zeroes?

From your description of its purpose, each non holiday date in the range should count as 1 (including the two specified dates) UNLESS it's a public holiday.

So if both dates are today, the result should be 1
Today and tomorrow =2

Without checking all your examples, the final one should be 2 as 24/12/2018 and 27/12/2018 are both working days

The formulas are designed to count date intervals I.e days from... Until... which isn't the same thing. The UPDATED column is the nearest.

Think you either need a different formula or a different approach.

Try this instead.
Have a table tblCalDates with fields
ID autonumber PK
CalDate Date
CalDay Text
WorkDay yes/no with default= yes
Notes Text .... Optional for entering e.g. Easter Monday

Populate the table for the next 2 years or more
Use an update query to set all weekend dates to WorkDay=False
Similarly for public holidays.
If it helps, I have code to calculate all of those including Easter.
Let me know if you want that

Then use a DCount formula for your result

=DCount("*","tblCalDates","CalDate Between #" & datDateFrom & "# And #" & datDateTo & "#")

Pi

 

[thedeadzeds]

Thank you Colin, I have followed the above and the query results is throwing out some strange results (I suspect this is something i have done). Example database attached, would you mind having a look? Many thanks

 

[jdraw]

Colin,thedeadzeds;
Shouldn't you be checking for workday also

DCount("*","tblCalDates","CalDate Between #" & [datDateFrom] & "# And #" & [datDateTo] & "# and [COLOR="Blue"]workday = true[/COLOR]")

 

[isladogs]

Amended version attached which is now working correctly

Changes made:
1. Added query for working dates only & used that as source for qryExampleDCount
2. Changed qryExampleDCount so dates use mm/dd/yyyy format
3. Created function GetDaysLeave to do the same thing
3. New query qryExampleFunction based on function

Both queries give the same correct results.

Personally, I would use the function version but its your choice

 

[Pat Hartman]

Here's a sample with several date functions.

 

[isladogs]

Colin,thedeadzeds;
Shouldn't you be checking for workday also

DCount("*","tblCalDates","CalDate Between #" & [datDateFrom] & "# And #" & [datDateTo] & "# and [COLOR="Blue"]workday = true[/COLOR]")

I could have done that but chose to use a query with that as the filter.
Same outcome

 

[thedeadzeds]

You you Colin and everyone else who helped with this. Very much appreciated