How to get the last business day of previous month based on today in Excel?

In this article, the user will understand the concept of generating the last business day of the previous month in Excel. The first example will guide the user through the process of using the formula, while the second example performs the same task by using the VBA code. For example, if today is May 6, 2023, then the date April 29, 2023, would be the last working day of the preceding month, assuming that weekends and holidays are not considered business days.

Example 1: By using the user-defined formula:

Step 1:

To understand the process of using the example. Consider the below-provided worksheet. Please note that the provided Excel sheet only contains a column header in the E2 cell, along with a space for E3.

Step 2:

Go to the E3 cell and type formula “=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1)”.

Explanation for the above formula:

• TODAY() method will return the current date in the Excel date format.

• YEAR(TODAY()) method will return the year component of the current date.

• MONTH(TODAY()) method will return the month according to the current date.

• DATE(YEAR(TODAY()), MONTH(TODAY()),1) method will create a new date by combining the year and month of the current date and setting the day to the first day of the month. For example, if the current date is May 6, 2023, this function will return on May 1, 2023.

• -1 is subtracted from the date returned by the DATE function, which moves the date back one day.

• WORKDAY() method will calculate the last working day before the date obtained from the above step. The WORKDAY function takes into account any holidays or weekends, which are specified by optional arguments.

Step 3:

The above-provided formula will display the below-generated result. To convert the generated number to a valid date, the user needs to change the respective formula from number to date type.

Step 4:

Go to the “Number” option displayed below, click on the drop-down arrow, and select the option “Date”. Consider the below-depicted image for proper reference −

Step 5:

The above change will convert the number to date data type. Consider the below-provided image for reference −

Example 2: By using the VBA code:

Step 1:

In this example, we will take the same Excel sheet, as chosen for the above example,

Step 2:

In this example, the user will understand the process of generating and executing the VBA code, and to execute the VBA code user need to open the VBA code editor. To do so, go to the “Developer” tab, and under the “Code” section, choose the option for “Visual Basic”. Consider the snapshot provided below −

Step 3:

The above step will open a “Microsoft Visual Basic for Applications” dialog box, as highlighted below −

Step 4:

In the appeared dialog box, click on the “Insert” tab, and select the option for “Module”.

Step 5:

The above step will open an empty code editor. In the opened blank area, type the below-given code −

' define function header
Function LastBusinessDayOfPrevMonth() As Date
    ' declaring required variables
    Dim lDy As Integer
    Dim prMnth As Integer
    Dim p_year As Integer
    Dim lastBusinessDay As Date
    ' calling day() method, with required data values
    lDy = Day(DateSerial(Year(Date), Month(Date), 0)) ' Get the last day of the current month
    prMnth = Month(Date) - 1 ' Get the previous month
    p_year = Year(Date) ' Get the year of the previous month
    ' if block to check value
    If prMnth = 0 Then ' If the previous month is December, adjust the year and month accordingly
        ' processing step for execution of if block
        prMnth = 12
        p_year = Year(Date) - 1
    ' end of if block
    End If
    ' calling last business day
    lst_BnsDay = DateSerial(p_year, prMnth, lDy) ' Combine the previous year, previous month and last day of the current month
    ' while loop
    While Weekday(lst_BnsDay, vbMonday) = 6 Or Weekday(lst_BnsDay, vbMonday) = 7 ' Check if the last day of previous month is a weekend day
        ' processing step
        lst_BnsDay = DateAdd("d", -1, lst_BnsDay) ' If the last day is a weekend day, move it back by one day until a weekday is reached
    Wend
    
    LastBusinessDayOfPrevMonth = lst_BnsDay ' Return the last business day of previous month
' end of function definition
End Function 

Step 6:

After that use key combination for “Alt +Q” to exit the sheet, and call the method “=LastBusinessDayOfPrevMonth()”, as depicted below −

Step 7:

Press the “Enter” key to display the required result in date format. Please found below attached final snapshot for output −

Conclusion

With the help of this article, users can obtain the last working/business day based on today's date. Two examples are demonstrated in this article. The user-defined formula is used in the first formula to retrieve the last business day of the previous month and in the second example, the program code is written in the VBA to generate the same result.