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

M

Msquare

New member
Local time
Today, 10:48
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.
 
Ranman256

Ranman256

Well-known member
Local time
Today, 03:48
Joined
Apr 9, 2015
Messages
4,337
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.
 
E

ebs17

Well-known member
Local time
Today, 09:48
Joined
Feb 7, 2020
Messages
1,946
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:
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:48
Joined
Feb 19, 2002
Messages
43,275
You can also make use of the First Day of the Week property of several functions. In the US, we generally define Monday as the first day of the week. You could define Saturday as the first day of the week. That way, days 6 and 7 would always be the weekend.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:48
Joined
May 7, 2009
Messages
19,243
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
 
M

Msquare

New member
Local time
Today, 10:48
Joined
Nov 7, 2022
Messages
4
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
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:48
Joined
May 7, 2009
Messages
19,243
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:
M

Msquare

New member
Local time
Today, 10:48
Joined
Nov 7, 2022
Messages
4
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
 
B

BAntar

New member
Local time
Today, 09:48
Joined
Aug 2, 2018
Messages
8
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!​

 
B

BAntar

New member
Local time
Today, 09:48
Joined
Aug 2, 2018
Messages
8
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

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
238
kjbrack
K
S
Limit the number of entries to a table with a specific value (preventing overbooking in airline)
Replies
16
Views
394
Sassaglas12
S
A
Solved Looking For Loop To Count Total Actual Days From 2 Dates
2
Replies
38
Views
1,656
Ashfaque
A
A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
415
ebs17
E
G
EXACT NUMBER OF HOURS BETWEEN TWO DATES AND TIME
Replies
13
Views
531
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

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