Get Previous Business Day in VBA

In Create Followup Task Reminders with VBA and Using Excel VBA to set up Task Reminders in Outlook there is a function for calculating the next business day, N days ahead.

It is used to create task reminders programmatically by adding N days to the current date, then adjusting the resulting date forward to a weekday.

But we have no matching function for going back in time to calculate business days prior to a given date. So I've written a function that does so, as well as improved the previous function for calculating the next business day.

Previous Business Day Function

The following function accepts a date and (optional) the number of days to go back. If no amount is specified, it is assumed that you want the previous business day. No adjustment is made for holidays.

Function PreviousBusinessDay(dateFrom As Date, _
    Optional daysBack As Long = 1) As Date
  Dim currentDate As Date
  Dim previousDate As Date

  ' convert pos to neg
  If daysBack > 0 Then
    daysBack = -daysBack
  End If

  ' determine previous date
  currentDate = dateFrom
  previousDate = DateAdd("d", daysBack, currentDate)

  ' was previous date a weekend day?
  Select Case Weekday(previousDate, vbUseSystemDayOfWeek)
  Case vbSunday
    previousDate = DateAdd("d", -2, previousDate)
  Case vbSaturday
    previousDate = DateAdd("d", -1, previousDate)
  End Select

  PreviousBusinessDay = CDate(Int(previousDate))

End Function

To specify a date for the function, use any native function, variable or constant that returns a Date type.

Usage

PreviousBusinessDay(#5/9/2012#)
PreviousBusinessDay(CDate("5/9/2012"))
PreviousBusinessDay(Now)

You can specify either a positive or negative number to go back. In case you forget, the function changes positive to negative numbers for you.

No assumption is made about the first day of your week — the work week as specified locally is used to determine if the previous business days falls on the "weekend". (If you are having trouble falling asleep you can read more about the NLS API here). I have not tested this, and I have no idea where to change the local work-week setting, but it is supposed to be locale-independent so I would love to know if it works properly in other locales. You can of course change this from vbUseSystemDayOfWeek to whatever is the first day of your work week.

A better Next Business Day Function

Following is my improved version of the next business day function I linked to earlier.

Function NextBusinessDay(dateFrom As Date, _
    Optional daysAhead As Long = 1) As Date
  Dim currentDate As Date
  Dim nextDate As Date

  ' convert neg to pos
  If daysAhead < 0 Then
    daysAhead = Abs(daysAhead)
  End If

  ' determine next date
  currentDate = dateFrom
  nextDate = DateAdd("d", daysAhead, currentDate)

  ' is next date a weekend day?
  Select Case Weekday(nextDate, vbUseSystemDayOfWeek)
  Case vbSunday
    nextDate = DateAdd("d", 1, nextDate)
  Case vbSaturday
    nextDate = DateAdd("d", 2, nextDate)
  End Select

  NextBusinessDay = CDate(Int(nextDate))

End Function

This function has several advantages over the previous, er, next function:

  • You can specify number of business days to advance, or call the function with just a date to get the very next business day
  • Uses DateAdd consistently instead of waffling between DateAdd and simple math
  • Should automatically adjust to local settings
  • Drops that silly variable prefixing habit

Usage

NextBusinessDay(#5/9/2012#)
NextBusinessDay(CDate("5/9/2012"))
NextBusinessDay(Now)

One thing I am not happy with, however, is the hardcoding of the date math, if adjustment be necessary. I feel like there should be some way to programmatically calculate how many days to add, but I can't figure out how. I'll settle for hardcoding for now.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 6 Comment(s) on Get Previous Business Day in VBA:

  1. Ok, I only got seven lines in and have to ask, why not:

    If daysBack > 0 Then
        daysBack = -daysBack
    End If
    
  2. David Onder writes:

    To replace the hardcoding, how about the following:

    nextDate = DateAdd("d", -(intWeekDay = vbSunday Or intWeekDay = vbSaturday) * (2 - (intWeekDay Mod vbSaturday)), nextDate)

    The first part –

    -(intWeekDay = vbSunday Or intWeekDay = vbSaturday)

    – of the calculation is just a flag to use the second part.

    The second part –

    (2 - (intWeekDay Mod vbSaturday)), nextDate)

    – converts Saturday to a 0 (Sunday is already a 1).

    I hope this is helpful.

  3. If by "hard-coding" you're talking about Saturday plus two, for example, I think that's the way to go. It's clear, and any fancy formula with Mods and whatever would be harder to follow, and probably as long.

    You got rid of the "silly variable prefixing," but kept the camelBack. I'd go with something like "NextDate." I'm stuck in a limbo where I don't use prefixes except for objects, globals and names that would be a reserved word without one, e.g., "strSql."

  4. Suriya writes:

    hi

    After copying it, iam getting an error like Nextbusiness date sub or function not defined.

    then i tried copying the Nextbusinessdate function from your previous post, but then i get error byref. argument mismatch intdaysback

    what iam missing here?

This article is closed to any future comments.
Excel School