Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Help calculating business days.

    Hello, I am in need of some assistance.

    I have a date field on a form that I would like to specify a default value. The value I would like to use is the last business day. So, if today is Tuesday, 10/09/2012, I would like today's default value to be 10/08/2012 <-- The last business day before today.

    In addition to that, I have another need for the same type of field, only using Tuesday thru Saturday as business days. So if today is Tuesday, 10/09/2012, the default value for this new field would be 10/06/2012 <-- The last "business" day for this particular week.

    Any help would be appreciated!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    On the basis that the first requirement is business day are Monday to Friday (5 days) and the second is Tuesday to Faturday (5 day) then you couid use this function to return the previouse business day before to-day for either requirement
    Code:
    Function PreviousBusDay(ByVal iFirstDay As Integer) As Date
        Dim iDay As Integer
        Dim iOffset As Integer
        
        iDay = Weekday(Date, iFirstDay)
        If iDay = 1 Then
            iOffset = 3
        ElseIf iDay <= 6 Then
            iOffset = 1
        Else
            iOffset = 2
        End If
        
        PreviousBusDay = DateAdd("d", -iOffset, Date)
        
    End Function
    for W/C Monday use
    Code:
    MsgBox PreviousBusDay(vbMonday)
    and W/C Tuesday
    Code:
    MsgBox PreviousBusDay(vbTuesday)
    Or any W/C 5 day period using the relevant day constant!

    To use this as a Default for a text box, use the Form Load event like this

    Code:
    Private Sub Form_Load()
        Me.Text1.DefaultValue = "PreviousBusDay(" & vbMonday & ")"
    End Sub
    using the appropriate constant

    Or just Type
    =PreviousBusDay(2)
    in the Default property dialogue for the text box, but you will have to enter the value of the constant (2=vbMonday, 3=vbTuesday)

    HTH

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •