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

    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
    Derbyshire, UK
    Provided Answers: 2

    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
    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
            iOffset = 2
        End If
        PreviousBusDay = DateAdd("d", -iOffset, Date)
    End Function
    for W/C Monday use
    MsgBox PreviousBusDay(vbMonday)
    and W/C Tuesday
    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

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

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



Posting Permissions

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