Solved Need to count the number of a specific day between two dates in access vba

M

Msquare

New member
Local time
Tomorrow, 02:00
Joined
Nov 7, 2022
Messages
4
Hi All. am in Djibouti for work. here in Djibouti, the weekend is different from other countries it's Thursday(half day) and Friday. what I want is a VBA or other code in access to calculate the working day and rest day from the input date interval.

example
start date:7/8/2022 end date:24/10/2022
the code should output counting working days(Saturday to Thursday(half-day)):60 and rest days(Thursday afternoon and Friday):19 days

I am running out of ideas

any advice will be highly appreciated.
 
make a table with ONLY the valid workdays:
tWorkDays
1 , Sun
2, Mon
etc

in the query Q1, add the Day Of Week field: Dow: Format([DateFld],"ddd")
then join tWorkDays tbl to DoW,

Add date range: where [DateFld] Between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

then make Q2, you can count the DoW from Q1.
 
Create a calendar table for a sufficient period of time.
First field: CalendarDay (Date) as PK
Second field: DayNr (Byte) from saved calculation Weekday(CalendarDay, vbSaturday), indexed

SQL: 
SELECT
   COUNT(*) AS WorkingDays
FROM
   tblData AS D
      INNER JOIN CalendarTable AS C
      ON D.AnyDate = C.CalendarDay
WHERE
   D.AnyDate BETWEEN #8/7/2022# AND #10/24/2022#
      AND
   C.DayNr < 6
A wide variety of solutions can be created with the support of a well-planned calendar table. Think of the inclusion of public holidays, company vacations and many other events.

Of course, you can and should only use the calendar table to calculate the working days. The instructions shown are only intended to stimulate the imagination.
 
Last edited:
Msquare said:
what I want is a VBA or other code in access to calculate the working day and rest day from the input date interval.
Click to expand...
for VBA:

Code: 
'_ arnelgp
'_ count workdays between dates
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0) As Single
Dim iDaysCount As Single, dteLoop As Date, n As Integer
Dim oWkEnd As Object
'_ check if dates are supplied
If Not IsDate(dte1) Then
    Exit Function
End If
Set oWkEnd = CreateObject("scripting.dictionary")
'_ note:
'_ i am using English calendar date
'_ 1 = sunday
'_ 2 = monday, etc.
'_
'_ current weekend is half day on thursday, whole day on friday
With oWkEnd
    .Add Item:=0.5, Key:=5      '5 = thursday,  0.5 day-off
    .Add Item:=1, Key:=6        '6 = friday,    1 day-off
End With
'_ check if dte2 is supplied
If IsNull(dte2) Then
    dte2 = Date
End If
If dte2 = 0 Then
    dte2 = Date
End If
For dteLoop = dte1 To dte2
    iDaysCount = iDaysCount + 1
    n = Weekday(dteLoop, vbSunday)
    If oWkEnd.Exists(n) Then
        iDaysCount = iDaysCount - oWkEnd(n)
    End If
Next
Set oWkEnd = Nothing
fncWorkDays = iDaysCount
End Function

To test:
Code: 
?fncWorkDays(#8/7/2022#,#10/24/2022#)

Result:

62.5 days
 
arnelgp said:
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0) As Single Dim iDaysCount As Single, dteLoop As Date, n As Integer Dim oWkEnd As Object '_ check if dates are supplied If Not IsDate(dte1) Then Exit Function End If Set oWkEnd = CreateObject("scripting.dictionary") '_ note: '_ i am using English calendar date '_ 1 = sunday '_ 2 = monday, etc. '_ '_ current weekend is half day on thursday, whole day on friday With oWkEnd .Add Item:=0.5, Key:=5 '5 = thursday, 0.5 day-off .Add Item:=1, Key:=6 '6 = friday, 1 day-off End With '_ check if dte2 is supplied If IsNull(dte2) Then dte2 = Date End If If dte2 = 0 Then dte2 = Date End If For dteLoop = dte1 To dte2 iDaysCount = iDaysCount + 1 n = Weekday(dteLoop, vbSunday) If oWkEnd.Exists(n) Then iDaysCount = iDaysCount - oWkEnd(n) End If Next Set oWkEnd = Nothing fncWorkDays = iDaysCount End Function
Click to expand...
Thank you very much it works. Another question what if I want to add holidays in addition with rest days
 
here you supply the holiday_table and the field_name of the holiday.
Code: 
'  arnelgp
'  count workdays between dates
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0, Optional ByVal RestDay As Integer = 0) As Single

' NOTE: you put the name of the table and fieldname below
Const HOLIDAY_TABLE As String = ""     'put inside the quote
Const HOLIDAY_FIELD As String = ""      'put inside the quote

' Note:
' RestDay is again in English(US) calendar:
' 1, means restday is on Sunday
' 2, restday is monday
' 3, restday is tues, etc
'
Dim iDaysCount As Single, dteLoop As Date, n As Integer, nHoliday As Integer
Dim oWkEnd As Object
'  check if dates are supplied
If Not IsDate(dte1) Then
    Exit Function
End If
Set oWkEnd = CreateObject("scripting.dictionary")
'  note:
'  i am using English calendar date
'  1 = sunday
'  2 = monday, etc.
'
'  current weekend is half day on thursday, whole day on friday
With oWkEnd
    .Add Item:=0.5, Key:=5      '5 = thursday,  0.5 day-off
    .Add Item:=1, Key:=6        '6 = friday,    1 day-off
End With
'  check if dte2 is supplied
If IsNull(dte2) Then
    dte2 = Date
End If
If dte2 = 0 Then
    dte2 = Date
End If
For dteLoop = dte1 To dte2
    iDaysCount = iDaysCount + 1
    n = Weekday(dteLoop, vbSunday)
    ' check if it is holiday
    nHoliday = 0
    If Len(HOLIDAY_TABLE) <> 0 Then
        nHoliday = DCount("1", HOLIDAY_TABLE, HOLIDAY_FIELD & "=#" & Format$(dteLoop, "mm/dd/yyyy") & "#")
    End If
    If (nHoliday <> 0) Then
        iDaysCount = iDaysCount - 1
    Else
        If (RestDay = n) Then
            iDaysCount = iDaysCount - 1
        Else
            If oWkEnd.Exists(n) Then
                iDaysCount = iDaysCount - oWkEnd(n)
            End If
        End If
    End If
Next
Set oWkEnd = Nothing
fncWorkDays = iDaysCount
End Function
 
Last edited:
arnelgp said:
here you supply the holiday_table and the field_name of the holiday.
Code: 
'  arnelgp
'  count workdays between dates
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0, Optional ByVal RestDay As Integer = 0) As Single

' NOTE: you put the name of the table and fieldname below
Const HOLIDAY_TABLE As String = ""     'put inside the quote
Const HOLIDAY_FIELD As String = ""      'put inside the quote

' Note:
' RestDay is again in English(US) calendar:
' 1, means restday is on Sunday
' 2, restday is monday
' 3, restday is tues, etc
'
Dim iDaysCount As Single, dteLoop As Date, n As Integer, nHoliday As Integer
Dim oWkEnd As Object
'  check if dates are supplied
If Not IsDate(dte1) Then
    Exit Function
End If
Set oWkEnd = CreateObject("scripting.dictionary")
'  note:
'  i am using English calendar date
'  1 = sunday
'  2 = monday, etc.
'
'  current weekend is half day on thursday, whole day on friday
With oWkEnd
    .Add Item:=0.5, Key:=5      '5 = thursday,  0.5 day-off
    .Add Item:=1, Key:=6        '6 = friday,    1 day-off
End With
'  check if dte2 is supplied
If IsNull(dte2) Then
    dte2 = Date
End If
If dte2 = 0 Then
    dte2 = Date
End If
For dteLoop = dte1 To dte2
    iDaysCount = iDaysCount + 1
    n = Weekday(dteLoop, vbSunday)
    ' check if it is holiday
    nHoliday = 0
    If Len(HOLIDAY_TABLE) <> 0 Then
        nHoliday = DCount("1", HOLIDAY_TABLE, HOLIDAY_FIELD & "=#" & Format$(dteLoop, "mm/dd/yyyy") & "#")
    End If
    If (nHoliday <> 0) Then
        iDaysCount = iDaysCount - 1
    Else
        If (RestDay = n) Then
            iDaysCount = iDaysCount - 1
        Else
            If oWkEnd.Exists(n) Then
                iDaysCount = iDaysCount - oWkEnd(n)
            End If
        End If
    End If
Next
Set oWkEnd = Nothing
fncWorkDays = iDaysCount
End Function
Click to expand...
Thank you very much...your idea is much helpful.it calculates perfectly
 
arnelgp said:
here you supply the holiday_table and the field_name of the holiday.
Code: 
'  arnelgp
'  count workdays between dates
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0, Optional ByVal RestDay As Integer = 0) As Single

' NOTE: you put the name of the table and fieldname below
Const HOLIDAY_TABLE As String = ""     'put inside the quote
Const HOLIDAY_FIELD As String = ""      'put inside the quote

' Note:
' RestDay is again in English(US) calendar:
' 1, means restday is on Sunday
' 2, restday is monday
' 3, restday is tues, etc
'
Dim iDaysCount As Single, dteLoop As Date, n As Integer, nHoliday As Integer
Dim oWkEnd As Object
'  check if dates are supplied
If Not IsDate(dte1) Then
    Exit Function
End If
Set oWkEnd = CreateObject("scripting.dictionary")
'  note:
'  i am using English calendar date
'  1 = sunday
'  2 = monday, etc.
'
'  current weekend is half day on thursday, whole day on friday
With oWkEnd
    .Add Item:=0.5, Key:=5      '5 = thursday,  0.5 day-off
    .Add Item:=1, Key:=6        '6 = friday,    1 day-off
End With
'  check if dte2 is supplied
If IsNull(dte2) Then
    dte2 = Date
End If
If dte2 = 0 Then
    dte2 = Date
End If
For dteLoop = dte1 To dte2
    iDaysCount = iDaysCount + 1
    n = Weekday(dteLoop, vbSunday)
    ' check if it is holiday
    nHoliday = 0
    If Len(HOLIDAY_TABLE) <> 0 Then
        nHoliday = DCount("1", HOLIDAY_TABLE, HOLIDAY_FIELD & "=#" & Format$(dteLoop, "mm/dd/yyyy") & "#")
    End If
    If (nHoliday <> 0) Then
        iDaysCount = iDaysCount - 1
    Else
        If (RestDay = n) Then
            iDaysCount = iDaysCount - 1
        Else
            If oWkEnd.Exists(n) Then
                iDaysCount = iDaysCount - oWkEnd(n)
            End If
        End If
    End If
Next
Set oWkEnd = Nothing
fncWorkDays = iDaysCount
End Function
Click to expand...

Thank a lot you are the best!​

 
arnelgp said:
for VBA:

Code: 
'_ arnelgp
'_ count workdays between dates
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0) As Single
Dim iDaysCount As Single, dteLoop As Date, n As Integer
Dim oWkEnd As Object
'_ check if dates are supplied
If Not IsDate(dte1) Then
    Exit Function
End If
Set oWkEnd = CreateObject("scripting.dictionary")
'_ note:
'_ i am using English calendar date
'_ 1 = sunday
'_ 2 = monday, etc.
'_
'_ current weekend is half day on thursday, whole day on friday
With oWkEnd
    .Add Item:=0.5, Key:=5      '5 = thursday,  0.5 day-off
    .Add Item:=1, Key:=6        '6 = friday,    1 day-off
End With
'_ check if dte2 is supplied
If IsNull(dte2) Then
    dte2 = Date
End If
If dte2 = 0 Then
    dte2 = Date
End If
For dteLoop = dte1 To dte2
    iDaysCount = iDaysCount + 1
    n = Weekday(dteLoop, vbSunday)
    If oWkEnd.Exists(n) Then
        iDaysCount = iDaysCount - oWkEnd(n)
    End If
Next
Set oWkEnd = Nothing
fncWorkDays = iDaysCount
End Function

To test:
Code: 
?fncWorkDays(#8/7/2022#,#10/24/2022#)

Result:

62.5 days
Click to expand...

Thank a lot you are the best!​

 
You must log in or register to reply here.

Similar threads

jwcolby54
The Garbage collector in VBA
Replies
18
Views
2,626
DickyP
DickyP
K
Solved Report Group by Day with a Start time of 4:00:00am, End time 3:59:59AM the next Day
Replies
5
Views
971
kjbrack
K
S
Need to create an alias for the totals of a field in query
Replies
3
Views
794
sheckay
S
J
Code to change Date in a field if the checkbox is checked
Replies
5
Views
628
LarryE
LarryE
R
Merge two tables in query by date/time, preserving hourly time in one of the tables
Replies
12
Views
724
Ken Sheridan
Ken Sheridan

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom