Solved

Last business day of prior month in Excel VBA

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

Title # Comments Views Activity
Formula 6 47
Excel VBA When using VLookup 6 28
NEED TO UPDATE DATA IN EXCEL 18 32
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