Sandra Smith
asked on
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! I thought this was going to more of a struggle and I was definitly going at it from the wrong direction.
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.
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
Sid