Solved

Last business day of prior month in Excel VBA

Posted on 2011-03-07
4
1,058 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
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


Sid
0
 
LVL 30

Accepted Solution

by:
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)
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
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.
0
 
LVL 22

Expert Comment

by:rspahitz
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
        Next
    Loop
    GetLastDay = dtLastDay
End Function

Open in new window

0

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

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 …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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