Brasilo,
Here is a function and test procedure to calculate the number of nonworking days in the interval between StartDate and Enddate. Weekend days are Saturday and Sunday. Holidays are recorded in tblHolidays.
In this set up calculate the Total number of seconds between date1 and date2. [DateDiff("s",Date1,Date2)]
Calculate the number of workdays and nonworkdays using function [NonWorkingdays2(Date1,Date2)]
Calculate the number of seconds in the non workdays in the interval.
Total seconds you seek = TotalSecondsInInterval -NumberOfSecondsInNonWorkDaysInInterval
NOTE: NonWorkingDays2
'-------------------------------------------------------------------------------------------------------------------------------
' RETURNS: Decimal number where whole number is Working Days and decimal is NonWorkdays
'----------------------------------------------------------------------------------------------------------------------------------
This is my sample tblHolidays
Code:
| ID |
Description |
HolidayDate |
|---|
| 9 |
BrasiloDay |
31-Oct-2017 |
| 7 |
aprilfools |
01-Apr-2016 |
| 6 |
march day |
17-Mar-2016 |
| 5 |
Tuesday special |
01-Feb-2016 |
| 3 |
holidayOnWeekend |
30-Jan-2016 |
| 2 |
AnotherHoliday |
29-Jan-2016 |
| 1 |
MadeUpHoliday |
26-Jan-2016 |
| 4 |
xxxMonday |
18-Jan-2016 |
| 8 |
NewYears |
01-Jan-2016 |
Here is the function
NonWorkingDays2
Code:
Public Function NonWorkingDays2(ByVal StartDate As Date, ByVal enddate As Date) As Single
'....................................................................
' Name: NonWorkingDays2
' Purpose: To find the number of Holidays and weekend days in an interval
' Inputs: StartDate As Date
' EndDate As Date
'--------------------------------------------------------------------------------------
' RETURNS: Decimal number where whole number is Working Days and decimal is NonWorkdays
'--------------------------------------------------------------------------------------
' where non work days are Holidays or weekend days.
' This uses Saturday and Sunday as weekend days
' Author: JED/mellon
' Date: Oct 31 2017
' Comment: Accepts two dates and returns the number of nonworkingdays between them
' Note that this function accounts for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'This uses default start of week vbSunday 1__________________JED
'
' I have used a boolean ShowDebug to identify weekendday or workday or holiday. You can turn it off by setting ShowDebug to false.
'
' I also used a ByVal so as not to destroy te 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.
'
'....................................................................
Dim Showdebug As Boolean '--------jed
10 On Error GoTo NonWorkingDays2_Error
20 Showdebug = True '------------------jed used to show workdayholiday for each date in range
Dim WendOrHoliday As Integer
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim db As DAO.Database
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 WendOrHoliday = 0
70 Do While StartDate <= enddate
80 rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
90 If WeekDay(StartDate) <> vbSunday And WeekDay(StartDate) <> vbSaturday Then
100 If rst.NoMatch Then 'is a workday
110 intCount = intCount + 1
120 Else
130 WendOrHoliday = WendOrHoliday + 1 'its a holiday
140 End If
150 Else
160 WendOrHoliday = WendOrHoliday + 1 'its a weekend day
170 End If
180
'jed debuggng tool '********************************************************************************************
190 If Showdebug Then _
Debug.Print StartDate & " " & _
IIf(WeekDay(StartDate) = vbSaturday Or WeekDay(StartDate) = vbSunday, "weekend day", _
IIf(DCount("*", "tblHolidays", "holidaydate = #" & StartDate & "#") = 1, "*HOLIDAY*", "workday"))
200 StartDate = StartDate + 1
210 Loop
'NOTE: This returns a decimal number where
' the whole number is Working Days in the interval and
' the decimal number represents the weekend + holiday days
220 NonWorkingDays2 = intCount + (WendOrHoliday / 10)
NonWorkingDays2_Exit:
230 Exit Function
NonWorkingDays2_Error:
240 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure NonWorkingDays2"
250 Resume NonWorkingDays2_Exit
End Function
Here is the
test routine using your sample data from the post.
Code:
'---------------------------------------------------------------------------------------
' Procedure : TestBrasilo
' Author : mellon
' Date : 31-Oct-2017
' Purpose : To demonstrate the use of function NonWorkingDays2,
'providing a StartDate and an Enddate to get the number of workdays and
'the number of hlidays+ weekend days in the interval between the 2 dates.
'And to use this info to get the number of seconds in the interval that
'does not include weekend days or holidays.
'
' The calculation to convert seconds to days/hrs/min/sec etc is not provided
'---------------------------------------------------------------------------------------
'
Sub TestBrasilo()
'This is a test procedure to show the logic to get
'test for Brasilo Oct 31 2017 based on thread
'https://www.accessforums.net/showthread.php?t=68807&p=376165#post376165
' As per OP
'10/26/2017 01:31PM - 11/01/2017 10:00AM
'1 holiday(10/31) and a weekend (10/28 - 10/29)
Dim result As Single 'Result is the Raw return from the function NonWorkingDays2(StartDate, enddate)
Dim StartDate As Date
Dim enddate As Date
10 Dim StartDateWithTime As Date: StartDateWithTime = #10/26/2017 1:31:00 PM#
20 Dim EndDateWithTime As Date: EndDateWithTime = #11/1/2017 10:00:00 AM#
30 Dim SecondsPerDay As Long: SecondsPerDay = 86400
Dim WeekEndOrHolidays As Long
Dim TotalSecondsInInterval As Long
Dim SecondsToRemove As Long
40 On Error GoTo TestBrasilo_Error
50 TotalSecondsInInterval = DateDiff("s", StartDateWithTime, EndDateWithTime)
60 StartDate = #10/26/2017# '#1/13/2016#
70 enddate = #11/1/2017# ' #1/31/2016#
'Display some parameters
80 Debug.Print "StartDate: " & StartDate & vbTab & "StartDateWithTime: " & StartDateWithTime
90 Debug.Print "EndDate : " & enddate & vbTab & "EndDateWithTime: " & EndDateWithTime
100 result = NonWorkingDays2(StartDate, enddate)
110 WeekEndOrHolidays = Int((result - Int(result)) * 10)
120 Debug.Print StartDate & " " & enddate & " workdays.weekend or holiday days " & result
130 Debug.Print "Non workdays in the interval " & WeekEndOrHolidays
'Calculate Seconds to be removed (weekend and holiday).
140 SecondsToRemove = WeekEndOrHolidays * SecondsPerDay
150 Debug.Print "Total seconds in interval " & TotalSecondsInInterval
160 Debug.Print "seconds to remove " & SecondsToRemove
170 Debug.Print "Number of seconds in working days in the interval is " _
& TotalSecondsInInterval - SecondsToRemove
TestBrasilo_Exit:
180 Exit Sub
TestBrasilo_Error:
190 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure TestBrasilo"
200 Resume TestBrasilo_Exit
End Sub
And here is the immediate window displaying the Debug.print statements
Code:
StartDate: 26-Oct-2017 StartDateWithTime: 26-Oct-2017 1:31:00 PM
EndDate : 01-Nov-2017 EndDateWithTime: 01-Nov-2017 10:00:00 AM
26-Oct-2017 workday
27-Oct-2017 workday
28-Oct-2017 weekend day
29-Oct-2017 weekend day
30-Oct-2017 workday
31-Oct-2017 *HOLIDAY*
01-Nov-2017 workday
26-Oct-2017 01-Nov-2017 workdays.weekend or holiday days 4.3
Non workdays in the interval 3
Total seconds in interval 505740
seconds to remove 259200
Number of seconds in working days in the interval is 246540
Note the
4 workdays,
3 weekend or holidays
Windows 10 
Access 2010 32bit
Reply With Quote
Originally Posted by orange
