How can I use Google Sheets to find the last business day of the month?

By /

Google Sheets is a powerful spreadsheet tool that can be used to efficiently manage and analyze data. One useful feature of Google Sheets is the ability to find the last business day of the month. This can be achieved by utilizing the WORKDAY function, which calculates a given number of working days from a specified start date. By setting the start date to the first day of the following month and specifying a negative number of working days, the function will return the last business day of the month. This feature can be helpful for businesses and individuals in planning and organizing their financial and operational activities.

Google Sheets: Find Last Business Day of Month

You can use the following formula to find the last business day of the month for a given date in Google Sheets:

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

This particular formula will return the last business day of the month for the date in cell A2.

The following example shows how to use this formula in practice.

Example: How to Find Last Business Day of Month in Google Sheets

Suppose we have the following column of dates in Google Sheets:

Suppose that we would like to find the last business day of the month associated with each date in column A.

To do so, we will type the following formula into cell B2 to find the last business day of the month for the date in cell A2:

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

We can then click and drag this formula down to each remaining cell in column B:

Google Sheets last business day of month

Column B shows the last business day of the month that the date in column A belongs to.

For example, the first date in the Date column of 1/25/2023 belongs to the month and year of January 2023.

If we refer to a calendar, we can see that the last business day of January 2023 is on Tuesday the 31st:

Thus, our formula correctly returns 1/31/2023.

How This Formula Works

Recall the formula that we used to find the last business day of the month for the date in cell A2:

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

Here is how this formula works:

First, the EOMONTH function finds the last day of the month for the date in cell A2.

Next, we add 1 to get the first day of the next month.

Lastly, we use the WORKDAY function with an argument of -1 to go back one business day, which will be the last business day of the month for the date in cell A2.

We repeat this process for every other date in column A.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. How can I find the last business day of the month on Google Sheets?
  2. How to Find the Last Business Day of a Month in Excel
  3. How do I find the first business day of month in Excel?
  4. How do I show the month name in Google Sheets?
  5. How to convert date to month and year format in Google Sheets?
  6. How to use Query in Google Sheets using a month number?
  7. How to Group Data by Month in Google Sheets (With Example)
  8. How to convert between month name & number in Google Sheets?
  9. “Can the IF Function in Google Sheets be Used According to the Month?”
  10. How can I filter dates by month in Google Sheets?