business day

[slimjen]

Hi All, I'm using ver 2010 of access. I have a database that in one of my fields; I have the business day auto populate based on another fields date. Using the following code:

Public Function fnBusinessDay(dt As Variant) As Integer
    Dim i  As Integer
    Dim dtTemp As Variant
    i = 0
    If IsNull(dt) Then
        fnBusinessDay = 0
        
    Else
        dtTemp = DateSerial(Year(dt), 1, i)
        While dtTemp < dt
            If InStr("Sat/Sun", Format(dtTemp, "ddd")) = 0 Then i = i + 1
            dtTemp = dtTemp + 1
        Wend
        fnBusinessDay = i
    
    End If

It worked in the month of January but continues on basically for the business day of the year instead of by the month. For example; January 28th should have been business day 18. It didn't account for the holidays. And the first day of February populated 22 for the business day. How do I modify this code to start over for the current month and exclude holidays? Thanks

[CJ_London]

rather than modifying you need different code and a reference table to list your holidays. Plenty of examples on this and other forums. Look to the bottom of this thread for some examples.

Also look at using the weekday function - you'll find it a lot simpler and faster than your date formatting

[orange]

slimjen,

Here is a function to get working days between 2 dates.

'---------------------------------------------------------------------------------------
' Procedure : fWorkingDays
' Author    : --adapted from Bytes by A Dezii
' Date      : 16/7/2015
' Purpose   : to get weekdays/business days between 2 dates.
'
' A simple Function to count the number of Workdays between 2 dates,
'Result does not include Saturdays, Sundays nor Federal/Stat Holidays.
'Create a Table named tHolidays with a single DATE/TIME Field named [HolidayDate].
'Populate this Table with any/all work related Holidays.
'
'Inputs: StartDate, and EndDate
'Returns: an Integer representing the number of Workdays/Business
'---------------------------------------------------------------------------------------
'
Public Function fWorkingDays(dteStartDate As Date, dteEndDate As Date) As Integer
    Dim intCount As Integer

10  On Error GoTo fWorkingDays_Error

20  intCount = 0

30  Do While dteStartDate <= dteEndDate
40      Select Case WeekDay(dteStartDate)   'what is the weekday(1=Sun....7=Sat)
        Case Is = 1 Or 7                  'If it's a Saturday or Sunday, do nothing
50      Case Is = 2, 3, 4, 5, 6           'A weekday but
                                          '  is it a Holiday as posted in tblHolidays?
60          If DCount("*", "tHoliday", "HolidayDate  = #" & dteStartDate & "#") < 1 Then     'NOT Holiday
70              intCount = intCount + 1   ' so increment if weekday and not a holiday
80          End If
90      End Select
100     dteStartDate = dteStartDate + 1
110 Loop
120 fWorkingDays = intCount

130 On Error GoTo 0
140 Exit Function

fWorkingDays_Error:

150 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure fWorkingDays."
End Function

[slimjen]

Thanks. I'll try this when I get back in the office tomorrow.