Last business day of prior month in Excel VBA

Posted on 2011-03-07
Last Modified: 2012-05-11
I have a range in Excel, ss_Holidays, that lists all the holidays for the current year.  I need to, with VBA, determine the last business day of the prior month - the actual date.   So, how could this be done in an Excel VBA module?  It CANNOT be done on a worksheet as the entire applications is controlled from user forms.
Question by:ssmith94015
  • 2
LVL 30

Expert Comment

ID: 35060922
This will give you the last date of the last month

Sub Sample()
    Debug.Print Date - Day(Date) - _
    Application.WorksheetFunction.Max(0, _
    Application.WorksheetFunction.Weekday(Date - Day(Date), 2) _
    - 5)
End Sub

Open in new window

LVL 30

Accepted Solution

SiddharthRout earned 500 total points
ID: 35061004
And this is the code that you finally want, I guess.

Sub Sample()
    Dim LastDate As Date
    Dim rng As Range
    LastDate = Date - Day(Date) - _
    Application.WorksheetFunction.Max(0, _
    Application.WorksheetFunction.Weekday(Date - Day(Date), 2) _
    - 5)
    For Each rng In Range("ss_Holidays")
        If rng.Value = LastDate Then
            LastDate = LastDate - 1
            GoTo startAgain
        End If
    Debug.Print LastDate
End Sub

Open in new window


Author Closing Comment

ID: 35061147
Thank you!  I thought this was going to more of a struggle and I was definitly going at it from the wrong direction.
LVL 22

Expert Comment

ID: 35061332
Good job, Sid.

and here's another version in case you don't like the Goto statement :)

The function uses the desired day (such as Now() or any valid date such as one located in cell C1 on my sheet, and returns the last non-weekend/non-holiday.

Sub ShowLastDay()
    Dim dtLastDay As Date
    dtLastDay = GetLastDay(Range("C1").Value)
    MsgBox "Last day of prev month is " & dtLastDay
End Sub

Function GetLastDay(theDate As Date) As Date
    Dim dtFirstDay As Date
    Dim dtLastDay As Date
    Dim objHoliday As Range
    Dim dtHoliday As Date
    Dim bNoHolidays As Boolean
    ' Build the first day of the month (assuming mm/dd/yyyy format)
    dtFirstDay = CDate(DatePart("m", theDate) & "/1/" & DatePart("yyyy", theDate))
    ' Get last day of previous month
    dtLastDay = dtFirstDay - 1
    bNoHolidays = False
    Do Until bNoHolidays
        bNoHolidays = True
        ' Check for weekends
        If Weekday(dtLastDay) = 1 Then
            ' If last day is Sunday, use previous Friday
            dtLastDay = dtLastDay - 2
        End If
        If Weekday(dtLastDay) = 7 Then
            ' If last day is Saturday, use previous Friday
            dtLastDay = dtLastDay - 1
        End If
        For Each objHoliday In Range("ss_Holiday").Cells
            dtHoliday = objHoliday.Value
            If dtHoliday = dtLastDay Then
                dtLastDay = dtLastDay - 1
                bNoHolidays = False
            End If
    GetLastDay = dtLastDay
End Function

Open in new window


Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Formula 6 47
Excel VBA When using VLookup 6 28
Vlookup formula error 15 11
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now