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

By /

To find the last business day of the month on Google Sheets, you can use the WEEKDAY function to determine the day of the week for the last day of the month. Then, you can use the IF function to check if the day is a weekend (Saturday or Sunday) and subtract accordingly to get the last business day of the month. You can also use the EOMONTH function to get the last day of the month and then use the SAMEWEEKDAY function to find the last business day. This will help you to easily identify the last business day of any month on Google Sheets.

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 to Find the Last Business Day of a Month in Excel
  2. How do I find the first business day of month in Excel?
  3. How do I show the month name in Google Sheets?
  4. How to get the first day of month 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. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  10. Business Communication