business day

[slimjen1]

All, does anyone have a calculation for getting the monthly business day based on the date logged in another field by a user? Ex. completed date field is 1/12/2016 business day of the month is 8. Thanks

 

[bob fitz]

The attached db may be of help. It has many useful date functions.
I take no credit for it. It was created and offered by Pat Hartman.

 

[slimjen1]

I appreciate the response; but this calculation just count the business days between dates selected. I need the actual business day based on the date in the field like in my example. Thanks

 

[Gasman]

I appreciate the response; but this calculation just count the business days between dates selected. I need the actual business day based on the date in the field like in my example. Thanks

Why can't you calculate the start date? ie 31/12/2015 in your example to supply to the function?

 

[slimjen1]

Sorry; I'm not following you. If the date field contains 1/12/2016; the business day for that month would be 8. This is counting from the 1st of the month only Monday - Friday. Exclude weekends.

 

[bob fitz]

The function used in the supplied db is called BusinessDays(dteStart, dteEnd).
You would just need to supply the function with the two parameters and assign the result to a text box on your form

Me.txtBusinessDays = BusinessDays(dteStart, dteEnd)

 

[Gasman]

Sorry; I'm not following you. If the date field contains 1/12/2016; the business day for that month would be 8. This is counting from the 1st of the month only Monday - Friday. Exclude weekends.

As Bob has explained, calculate the start date, the first of the month? and use both dates.

 

[slimjen1]

Ok. The Function in the db allows for the number of business days between two dates.

Private Sub cmdCalculate_Click()
Dim dteStart As Date
Dim dteEnd As Date
    If Not IsDate(Me.txtStart) Then
        MsgBox "Please enter a start date", vbOKOnly, "error"
        Me.txtStart.SetFocus
        Exit Sub
    End If
    If Not IsDate(Me.txtEnd) Then
        MsgBox "Please enter an ending date", vbOKOnly, "Error"
        Me.txtEnd.SetFocus
        Exit Sub
    End If
    dteStart = Me.txtStart
    dteEnd = Me.txtEnd
    Me.txtBusinessDays = BusinessDays(dteStart, dteEnd)
End Sub

I want the business day based on the completeddte field. For Ex. On the form; dtMonth field 1/1/2016 and completeddte field is 1/12/2016 txtbusinessday field should auto update to 8 which is the business day number. How do I modify the code to do give me just the business day of the month based on the completeddte. Thanks

 

[Gasman]

Well I would probably just use the function direct

Me.txtBusinessDays = BusinessDays(dtmonth,completeddte)

Assuming your field is called Me.txtBusinessDays and that you have primed the holiday table with all your holidays.

His form allows you to enter your own dates which is what most of the code is doing, the key bit is his function BusinessDays. Just copy that and the holiday table into your DB and use where you need it.

At the end of the day, you must calculate the start date, which in your case appears to be the number of days from the start of the month.?

Where you put the code is entirely up to you.

 

[arnelgp]

Public Function fnBusinessDay(dt As Variant) As Integer
    Dim i  As Integer
    Dim dtTemp As Variant
    i = 0
    If IsNull(dt) Then
        fnbusiness = 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
End Function

 

[slimjen1]

Thank you Arnelgp for your response. Your code works exactly the way I wanted. Thanks so much!

 

[Gasman]

Public Function fnBusinessDay(dt As Variant) As Integer
    Dim i  As Integer
    Dim dtTemp As Variant
    i = 0
    If IsNull(dt) Then
        fnbusiness = 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
End Function

What happens at Easter, August Bank Holiday? etc

 

[arnelgp]

that would be easy just add, a Holiday table and modify the code:

If InStr("Sat/Sun", Format(dtTemp, "ddd")) = 0 And _
Nz( _
DCount("*","HolidayTable","[DateField] = #" & Format(dtTemp, "mm/dd/yyyy") & "#"), 0) = 0 Then i = i + 1

 

[slimjen1]

that would be easy just add, a Holiday table and modify the code:

If InStr("Sat/Sun", Format(dtTemp, "ddd")) = 0 And _
Nz( _
DCount("*","HolidayTable","[DateField] = #" & Format(dtTemp, "mm/dd/yyyy") & "#"), 0) = 0 Then i = i + 1

This code worked. But users just informed me that instead of the business day starting over each month; it continues as if it counts the business day of the year i.e. February 1 should be business day 1; instead it comes up as business day 20. How do I modify this to start over when the month change please. Thanks

 

[arnelgp]

why february?

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

 

[slimjen1]

February was just when it started and I was using it as an example of the problem. I guess since January was the beginning of the year it would have the correct business day except for the holidays which I'm also trying to correct with your modification in #13. But I get an error "overflow". When I debug; it points to i=i +1. When I hover; i=32767.
this is the code which is your code:

Public Function fnBusinessDay(dt As Variant) As Integer
    Dim i  As Integer
    Dim dtTemp As Variant
    i = 0
    If IsNull(dt) Then
        fnbusiness = 0
        
    Else
        dtTemp = DateSerial(Year(dt), 1, i)
        While dtTemp < dt
            If InStr("Sat/Sun", Format(dtTemp, "ddd")) = 0 And _
Nz( _
DCount("*", "tblHolidays", "[HolidayDte] = #" & Format(dtTemp, "mm/dd/yyyy") & "#"), 0) = 0 Then i = i + 1
        Wend
        fnBusinessDay = i
    
    End If
End Function

Was I suppose to change something else?

Thanks for helping me with this

 

[slimjen1]

What can I change to make this work please?

 

[Gasman]

I was waiting for for Arnelgp to respond, but I'll try and help.

For the overflow, you would change
i as Integer to i as Long

but the overflow is due to a bug where the start date is never incremented.

If you want the business days from the beginning of the month then change as below. Also you need to increment the start date to test for each day until your passed date regardless of when it starts. Whether you include the passed date in the calculation is up to you, adjust to suit.

I did not test with holiday table as I did not have that available.

I passed todays date 29/03/2016 and got 21 working days.


HTH

Public Function fnBusinessDay(dt As Variant) As Integer
    Dim i  As Long
    Dim dtTemp As Variant
    i = 0
    If IsNull(dt) Then
        fnbusiness = 0
        
    Else
        dtTemp = DateSerial(Year(dt), [b]Month(dt)[/b], i)
        While dtTemp < dt
            If InStr("Sat/Sun", Format(dtTemp, "ddd")) = 0 And _
Nz( _
DCount("*", "tblHolidays", "[HolidayDte] = #" & Format(dtTemp, "mm/dd/yyyy") & "#"), 0) = 0 Then i = i + 1
        [b]dtTemp = dtTemp +1[/b]
        Wend
        fnBusinessDay = i
    
    End If
End Function

 

[slimjen1]

thanks so much! I'll try this and post back.

 

[Gasman]

Not sure if the end if is required, but I had that in my code

Public Function fnBusinessDay(dt As Variant) As Integer
    Dim i  As Long
    Dim dtTemp As Variant
    i = 0
    If IsNull(dt) Then
        fnbusiness = 0
        
    Else
        dtTemp = DateSerial(Year(dt), Month(dt), i)
        While dtTemp < dt
            If InStr("Sat/Sun", Format(dtTemp, "ddd")) = 0 And _
Nz( _
DCount("*", "tblHolidays", "[HolidayDte] = #" & Format(dtTemp, "mm/dd/yyyy") & "#"), 0) = 0 Then i = i + 1
        [b]End If[/b]
        dtTemp = dtTemp +1
        Wend
        fnBusinessDay = i
    
    End If
End Function