IsLastBusinessDayOfMonth() Function

Mike Wolfe -

One of our Access applications integrates with a reporting system that releases certain reports on the last business day of each month.  Thus, I wrote the not-so-cleverly named IsLastBusinessDayOfMonth() function to test that condition.

For most purposes, a business day is any day except a Saturday, Sunday, or federal holiday.  This function builds off of previous functions that take each of those exceptions into consideration.  Those other functions are IsBusinessDay(), IsEndOfMonth(), and MonthEnd().  

Note that the unusual '>>> comment syntax in the code below is used to create Python-inspired doc tests, which both verify the correctness of the function and document its usage.

The Code

'---------------------------------------------------------------------------------------
' Procedure : IsLastBusinessDayOfMonth
' Author    : Mike Wolfe (https://nolongerset.com)
' Date      : 11/21/2014
' Purpose   : Returns whether the given date is the last business day of the month.
'>>> IsLastBusinessDayOfMonth(#9/11/2001#)
' False
'>>> IsLastBusinessDayOfMonth(#5/31/2010#) Or IsLastBusinessDayOfMonth(#5/30/2010#) Or IsLastBusinessDayOfMonth(#5/29/2010#)
' False
'>>> IsLastBusinessDayOfMonth(#5/29/2010#, True)
' True
'>>> IsLastBusinessDayOfMonth(#5/28/2010#, True)
' False
'>>> IsLastBusinessDayOfMonth(#5/28/2010#)
' True
'>>> IsLastBusinessDayOfMonth(#5/27/2010#)
' False
'>>> IsLastBusinessDayOfMonth(#6/30/2010#)
' True
'---------------------------------------------------------------------------------------
'
Function IsLastBusinessDayOfMonth(AsOf As Date, Optional CountSatAsBusDay As Boolean = False) As Boolean
    'Is it even a business day?
    If IsBusinessDay(AsOf, CountSatAsBusDay) Then
        If IsEndOfMonth(AsOf) Then
            IsLastBusinessDayOfMonth = True
        Else
            Dim CurDay As Date
            For CurDay = MonthEnd(AsOf) To AsOf Step -1
                If IsBusinessDay(CurDay, CountSatAsBusDay) Then
                    If AsOf = CurDay Then
                        IsLastBusinessDayOfMonth = True
                    Else
                        Exit For
                    End If
                End If
            Next CurDay
        End If
    End If
End Function

Image by Susanne Sailer from Pixabay