[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1147
  • Last Modified:

Last business day of prior month in Excel VBA

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.
0
ssmith94015
Asked:
ssmith94015
  • 2
1 Solution
 
SiddharthRoutCommented:
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


Sid
0
 
SiddharthRoutCommented:
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)
startAgain:
    For Each rng In Range("ss_Holidays")
        If rng.Value = LastDate Then
            LastDate = LastDate - 1
            GoTo startAgain
        End If
    Next
    
    Debug.Print LastDate
End Sub

Open in new window


Sid
0
 
ssmith94015Author Commented:
Thank you!  I thought this was going to more of a struggle and I was definitly going at it from the wrong direction.
0
 
rspahitzCommented:
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
        Next
    Loop
    GetLastDay = dtLastDay
End Function

Open in new window

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now