Last Business Day of Month / Year – Excel & Google Sheets

This tutorial will demonstrate how to find the last business day of a Month or Year in Excel and Google Sheets.

 

WORKDAY Function

The WORKDAY Function returns the nearest working day, n days before or after the starting date. Its syntax is:

We can use the WORKDAY Function along with other functions to find the last business day of the month or year.

Last Business Day of Month

The EOMONTH Function can be nested in the WORKDAY Function to find the last business day of the month like this:

=WORKDAY(EOMONTH(B3,0)+1,-1)

Here the EOMONTH Function returns the last day of the current month. We add one day to the result, to get the first day of the next month.

Then we use the WORKDAY Function to return the closest business day before the first of the next month.

Note: The WORKDAY Function uses Saturday and Sunday as weekends. You can use the WORKDAY.INTL Function to customize weekend days.

Last Business Day of Year

Similarly, the WORKDAY Function can also be used to find the last business day of the year.

Given a year, the following example finds the last business day of the year:

=WORKDAY("1JAN"&(B3+1),-1)

First, 1 is added to the given year to get the next year. Then the ‘&’ Operator is used to merge and return the first day of the next year.

The WORKDAY Function then returns the closest business day before the first day of the next year, in effect the last business day of current year.

Note: In the function, 1 has been added to the year. Hence, to depict the order of operations ‘B3+1’ has been enclosed in parentheses.

Last Business Day of Month / Year in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.