Business day formula for Excel VBA RRS feed

  • Question

  • Hello,

    I have a macro which creates a report M-F, the file is always saved under the prior business days date. Included in the file is a day to day variance table which is copied from the prior business days file. The problem I'm having is specifying the correct business day when I run it on Tuesdays.

    The file I ran today was saved with the date of 07-25-16, when it was time to open the prior business days file (which would be 07-22-16) the formula I have is not selecting the correct date. The whole macro is rather lengthy so I'll just include the part pertaining to business day formula and the open file portion.

    The formula works fine every other day of the week, but not on Tuesdays.

    Dim PrevBusDay As Date
    Dim PriorTwoDays As Date
    Dim FileName As String
    Dim OpenPrior As Boolean
    Dim SumCell As Range
    Sub DBDailyReports()
    ' DBunallocatedCleanup Macro
    Dim CurrentDBfile As Workbook
    Set CurrentDBfile = ActiveWorkbook
    OpenPrior = True
    Application.DisplayAlerts = False
    PrevBusDay = Date - 1
    Select Case Weekday(PrevBusDay)
        Case vbSunday
            PrevBusDay = PrevBusDay - 2
        Case vbSaturday
            PrevBusDay = PrevBusDay - 1
    End Select
    PriorTwoDays = Date - 2
    Select Case Weekday(PriorTwoDays)
        Case vbSunday
            PriorTwoDays = PriorTwoDays - 3
        Case vbSaturday
            PriorTwoDays = PriorTwoDays - 2
    End Select
    <<<<<<<<<Open Prior Day file code>>>>>>>>>>>>>>
    If OpenPrior Then Workbooks.Open FileName:=(" Bill CMS/Direct Bill Unallocated Report/DB_Unallocated as of " & Format(PrevBusDay, "mm-dd-yy") & ".xls")


    Tuesday, July 26, 2016 2:01 PM

All replies

  • Aren't you really looking for the date two business days previous to today's date?

    PrevBusDay = Application.WorkDay(Date, -2)

    Tuesday, July 26, 2016 2:23 PM
  • Hello, when it comes to opening the prior file yes it would be two previous business days. I'll give this a try.


    Tuesday, July 26, 2016 2:51 PM
  • Hi Bernie,

    I'm getting an invalide number of arguments error when I use the above. I assumed it was ok to comment out the Select case portion which reference weekday not workday.


    Tuesday, July 26, 2016 3:02 PM
  • The reason is what day the week starts on. Here's some code you can experiment with:

    Option Explicit
    Sub test()
        Dim PrevBusDay As Date
        Dim testDate As Date
        Dim thisday As Integer
        For testDate = Date To Date + 7
            PrevBusDay = testDate - 1
            thisday = Weekday(PrevBusDay, FIRSTDAYOFWEEK)
            Select Case thisday
            Case vbSunday
                PrevBusDay = PrevBusDay - 2
            Case vbSaturday
                PrevBusDay = PrevBusDay - 1
            End Select
    Debug.Print "Current date = " & testDate & ", today is " & WeekdayName(Weekday(testDate, FIRSTDAYOFWEEK), False, FIRSTDAYOFWEEK) & ". Result = " & PrevBusDay & " -> "; WeekdayName(Weekday(PrevBusDay, FIRSTDAYOFWEEK), False, FIRSTDAYOFWEEK)
        Next testDate
    End Sub

    Will print:
    Current date = 2016-07-26, today is Tuesday. Result = 2016-07-25 -> Monday
    Current date = 2016-07-27, today is Wednesday. Result = 2016-07-26 -> Tuesday
    Current date = 2016-07-28, today is Thursday. Result = 2016-07-27 -> Wednesday
    Current date = 2016-07-29, today is Friday. Result = 2016-07-28 -> Thursday
    Current date = 2016-07-30, today is Saturday. Result = 2016-07-29 -> Friday
    Current date = 2016-07-31, today is Sunday. Result = 2016-07-29 -> Friday
    Current date = 2016-08-01, today is Monday. Result = 2016-07-29 -> Friday
    Current date = 2016-08-02, today is Tuesday. Result = 2016-08-01 -> Monday

    Also see the table in:

    Best regards, George

    Tuesday, July 26, 2016 3:24 PM
  • It has the correct number of arguents - what do you get with this code?

    Sub TestMacro()
        Dim PrevBusDay As Date
        PrevBusDay = Application.WorkDay(Date, -2)
        MsgBox "File needed from business day " & Format(PrevBusDay, "dddd mm/dd/yy")
    End Sub

    Tuesday, July 26, 2016 3:44 PM
  • Hi Lorac1969,

    can you tell us when you run the above mentioned code . what is your desired output and what output you are getting.

    please share the output will help us to understand the situation better.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 27, 2016 3:04 AM