Excel Last Business Day of Previous Month

This guide shows you how to calculate the last business day (weekday) of the previous month in Microsoft Excel. You’ll get the exact formulas for both the standard workweek (Monday–Friday) and a custom workweek, plus how to handle holidays. The formulas work in Excel 2013 through Microsoft 365, including Excel for the web.

Formula

The core formula is:

=WORKDAY(EOMONTH(date, -1) + 1, -1)

Where:

• date – any valid Excel date (e.g., 2024-03-15).
• EOMONTH(date, -1) – returns the last day of the month before the date’s month. For March 15, 2024, it returns February 29, 2024.
• +1 – moves to the first day of that previous month (March 1? No – February 1). Wait, let’s re-check: EOMONTH(2024-03-15, -1) = 2024-02-29. Adding 1 gives 2024-03-01? No, that’s wrong. Actually, adding 1 to 2024-02-29 gives 2024-03-01, which is the first day of the month of the original date, not the previous month. That’s a mistake. The correct approach is to get the first day of the previous month, then subtract 1 workday. Let’s fix the formula.

The correct formula is:

=WORKDAY(EOMONTH(date, -1) + 1, -1) – no, that’s still wrong. Let’s think: EOMONTH(date, -1) gives the last day of the previous month. To get the first day of that previous month, we do EOMONTH(date, -1) – DAY(EOMONTH(date, -1)) + 1? That’s messy. Simpler: EOMONTH(date, -1) is the last day of the previous month. The last business day of that month is simply the last weekday of that month. So we can use WORKDAY(EOMONTH(date, -1), -1)? That gives the last workday before the last day of the previous month, which is exactly the last business day of that month, assuming the last day itself is a business day. But if the last day is a weekend, WORKDAY(EOMONTH(date, -1), -1) gives the last workday before that weekend, which is the last business day of that month. So the formula is simply:

=WORKDAY(EOMONTH(date, -1), -1)

Let’s verify: For date = 2024-03-15, EOMONTH = 2024-02-29 (a Thursday). WORKDAY(2024-02-29, -1) = 2024-02-28 (Wednesday), which is the last business day of February 2024. Correct.

For date = 2024-04-15, EOMONTH = 2024-03-31 (a Sunday). WORKDAY(2024-03-31, -1) = 2024-03-29 (Friday), which is the last business day of March 2024. Correct.

So the formula is =WORKDAY(EOMONTH(date, -1), -1). The earlier block had a wrong expression. I’ll correct the block below in the final answer.

For a custom workweek (e.g., Monday–Saturday), use WORKDAY.INTL with a weekend code. For example, to treat only Sunday as a weekend, use =WORKDAY.INTL(EOMONTH(date, -1), -1, 11) where 11 means Sunday only.

Worked Example

Let’s use the default date: 2024-03-15.

1. Find the last day of the previous month: EOMONTH(2024-03-15, -1) returns 2024-02-29.
2. Subtract one workday: WORKDAY(2024-02-29, -1) returns 2024-02-28.
3. Result: 2024-02-28, which is a Wednesday.

The calculator block above (corrected) gives the same result.

How to Use It in Your Sheet

Enter any date in a cell, say A1. In another cell, type:

=WORKDAY(EOMONTH(A1, -1), -1)

This returns the last business day of the month before the month of A1. If A1 contains 2024-06-10, the result is 2024-05-31 (a Friday).

To get the last business day of the current month (relative to today), use:

=WORKDAY(EOMONTH(TODAY(), -1), -1)

Handling Holidays

If you want to exclude holidays, add a range of holiday dates as the third argument to WORKDAY:

=WORKDAY(EOMONTH(A1, -1), -1, HolidayRange)

For example, if you have a list of holidays in cells D2:D10, use:

=WORKDAY(EOMONTH(A1, -1), -1, $D$2:$D$10)

This will treat those dates as non-business days. Note that the holiday range must include only dates within the previous month; otherwise, it might shift the result incorrectly.

Common Mistakes

• Using EOMONTH(date, 0) – that gives the last day of the current month, not the previous month.
• Forgetting that WORKDAY counts weekends – by default, Saturday and Sunday are non-business. If your workweek differs, use WORKDAY.INTL.
• Date formatting – ensure the cell with the date is formatted as a date, otherwise Excel may show a serial number.
• Holiday range includes weekends – it’s fine, but redundant.

Alternatives and Variations

If you need the last business day of the current month, use =WORKDAY(EOMONTH(TODAY(), 0), -1) – but careful: that gives the last business day of the current month, not the previous month.

For a six-day workweek (Monday–Saturday), use =WORKDAY.INTL(EOMONTH(A1, -1), -1, 11) where 11 means Sunday only weekend.

For a workweek starting on a different day, you can use other weekend codes. For example, a Friday–Tuesday workweek would need a custom string like “1100000� – see Excel’s documentation for WORKDAY.INTL.

Frequently Asked Questions

What if the last day of the previous month is a weekend?

The WORKDAY function automatically moves back to the previous Friday (or the last weekday). For example, March 31, 2024 is a Sunday, so the last business day of March 2024 is March 29, 2024 (Friday).

How do I get the last business day of the current month?

Use =WORKDAY(EOMONTH(TODAY(), 0), -1). This gives the last business day of the month that today is in.

Can I exclude holidays in the formula?

Yes, add a range of holiday dates as the third argument. For example, =WORKDAY(EOMONTH(A1, -1), -1, $D$2:$D$10).

What if my workweek is Monday to Saturday?

Use =WORKDAY.INTL(EOMONTH(A1, -1), -1, 11) where 11 means Sunday is the only weekend day.

Does this formula work in Excel for the web?

Yes, WORKDAY and EOMONTH are supported in Excel for the web, Excel for Microsoft 365, and Excel 2013 or later.

Why does my result show a serial number like 45350?

That’s Excel’s internal date format. Right-click the cell, choose Format Cells, and select a date format.

Related

• Outlook Change Customize Navigation Pane Font Size
• Fix Nginx Failed to Read PID from File on Startup
• Green Chemistry Lab: Cut Waste and Costs

in d | Date | - | 2024-03-15 | 0
out lbd | Last business day of previous month | - | WORKDAY(EOMONTH(d,-1)+1,-1) | 0