business day

S

slimjen1

Registered User.
Local time
Today, 06:29
Joined
Jun 13, 2006
Messages
562
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
 
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.
 

Attachments

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
 
slimjen1 said:
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
Click to expand...

Why can't you calculate the start date? ie 31/12/2015 in your example to supply to the function?
 
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.
 
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
Code: 
Me.txtBusinessDays = BusinessDays(dteStart, dteEnd)
 
slimjen1 said:
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.
Click to expand...

As Bob has explained, calculate the start date, the first of the month? and use both dates.
 
Ok. The Function in the db allows for the number of business days between two dates.

Code: 
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
 
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.
 
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 Then i = i + 1
            dtTemp = dtTemp + 1
        Wend
        fnBusinessDay = i
    
    End If
End Function
 
Thank you Arnelgp for your response. Your code works exactly the way I wanted. Thanks so much!
 
Last edited:
arnelgp said:
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 Then i = i + 1
            dtTemp = dtTemp + 1
        Wend
        fnBusinessDay = i
    
    End If
End Function
Click to expand...

What happens at Easter, August Bank Holiday? etc
 
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
 
arnelgp said:
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
Click to expand...
 
why february?
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
    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
 
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:
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:)
 
Last edited:
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

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), [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
 
Not sure if the end if is required, but I had that in my code

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
 
You must log in or register to reply here.

Similar threads

D
How to select most current date by month, day and year
Replies
15
Views
824
Design by Sue
D
J
Solved Caculate the business day based on date time field
Replies
19
Views
523
Gasman
Gasman
C
How to return null when summing values that include nulls
Replies
6
Views
392
cheekybuddha
cheekybuddha
P
LEAVE MANAGEMENT
Replies
13
Views
749
Solo712
Solo712
W
Hello, New here
Replies
2
Views
198
DHookom
DHookom

Users who are viewing this thread

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