Solved

Last business day of prior month in Excel VBA

Posted on 2011-03-07
4
1,048 Views
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.
0
Comment
Question by:ssmith94015
  • 2
4 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:ssmith94015
Comment Utility
Thank you!  I thought this was going to more of a struggle and I was definitly going at it from the wrong direction.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

18 Experts available now in Live!

Get 1:1 Help Now