How to Get Last Weekday In Month in Excel

Why This Task Matters in Excel

In corporate finance, payroll, project management, and compliance reporting, schedules are almost never built around calendar days alone. Deadlines frequently refer to “the last business day of the month” or “the final weekday before month-end.” For example, payroll teams often need to release payments on the last weekday so employees are not paid late when the month ends on a weekend. Treasury departments schedule fund transfers on the last weekday to avoid idle cash over the weekend. Even marketing teams time social media campaigns to avoid launching on a Saturday if month-end falls then.

Excel remains the de-facto calendar engine for countless analysts because it blends flexible calculations with tabular data, is universally available, and integrates seamlessly with accounting, ERP, and BI tools. Mastering a robust formula for “last weekday in month” lets you automate time-sensitive workflows: bank cut-off trackers, invoice due dates, production cutoff sheets, or any process that rolls up monthly results but excludes weekends. When you know how to do this reliably, you eliminate manual calendar checks, reduce the risk of missing deadlines, and create dynamic workbooks that adjust automatically as months and years change.

Failing to generate the correct last weekday can have costly consequences. Imagine a macro that triggers a file export one day late because February ends on a Sunday; or a dashboard KPI that labels a weekend date as “actual month end” and confuses leadership. The skill intersects with many other Excel techniques: dynamic date ranges for charts, conditional formatting based on time periods, advanced financial modeling, and Power Query transformations that rely on accurate date stamps.

Therefore, being able to calculate the last weekday in any month is a foundational capability. It is more than a formula trick—it is a building block for robust, calendar-aware spreadsheets across industries from banking to manufacturing.

Best Excel Approach

The most reliable strategy combines two powerful date functions: EOMONTH, which finds the end of any month, and WORKDAY, which navigates forward or backward across weekdays (optionally considering holidays). By pushing one day past month end and stepping back one business day, you always arrive at the correct last weekday—regardless of whether the final calendar day is a Saturday or Sunday.

Syntax for the recommended approach:

=WORKDAY(EOMONTH(start_date,0)+1,-1, [holidays])

Explanation of parameters:

• start_date – any valid date in the target month (usually the first of the month, but any date works).
• 0 – tells EOMONTH to stay in the same month; use offsets like 1 or ‑1 to shift into the next or previous month.
• +1 – moves one day forward to the first day of the next month.
• -1 – instructs WORKDAY to step back one weekday, landing on the final weekday of the original month.
• [holidays] – optional range containing company holidays. If supplied, WORKDAY skips both weekends and the listed holiday dates.

Why this approach is best:

• Simple, readable, and requires only two functions.
• Handles all weekend patterns automatically.
• Easily enhanced by passing a holiday list, making it suitable for payroll or finance where local holidays matter.
• Compatible with Excel 2007 and later (including Microsoft 365).

Alternative approach (when WORKDAY is unavailable, such as in very old Excel versions or certain embedded systems):

=EOMONTH(A1,0)-CHOOSE(WEEKDAY(EOMONTH(A1,0),2),0,0,0,0,0,1,2)

This uses WEEKDAY to calculate how many days to subtract. It is shorter but less flexible because it does not account for holidays without additional logic.

Parameters and Inputs

To guarantee accurate results, pay attention to these input details:

• start_date (required)

  • Must be a serial date recognized by Excel.
  • Avoid text dates like \"2023-12-01\" unless they are properly converted.

• holidays (optional)

  • Supply a vertical range such as [H2:H20] containing valid dates.
  • Do not mix blank cells with non-date entries; use only true date values.
  • Holidays can be dynamic (e.g., NETWORKDAYS functions) as long as the range resolves to dates.

Data preparation:

• Align start_date cells with the same regional date formatting.
• If pulling from external systems, convert numeric text to dates using DATEVALUE or Power Query.
• Confirm that the workbook’s weekend definition matches Monday-Friday; if your locale treats Friday-Saturday or Saturday-Sunday differently, use WORKDAY.INTL, discussed later.

Edge cases:

• Months where the last calendar day is already a weekday will return that exact date.
• Leap years are inherently handled because EOMONTH recognizes February 29.
• If the optional holiday list makes the final weekday a holiday, WORKDAY backs up further to the preceding non-holiday weekday.

Step-by-Step Examples

Example 1: Basic Scenario

Scenario: You have a simple list of months beginning in cell B2 (formatted as 01-Jan-2024, 01-Feb-2024, etc.) and want to place the last weekday of each month in column C.

1. Enter the header “Month Start” in B1 and “Last Weekday” in C1.
2. In C2, type:

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

3. Press Enter, then fill the formula down.

What happens:

• EOMONTH(B2,0) returns the last calendar day (e.g., 31-Jan-2024).
• Adding +1 advances to 01-Feb-2024.
• WORKDAY steps back one weekday to 31-Jan-2024 because that date is a Wednesday. For February, 2024, EOMONTH returns 29-Feb-2024 (Thursday), +1 gives 01-Mar-2024, and stepping back lands on 29-Feb-2024—still a weekday. If you reach April 2024 where the month ends on Tuesday the 30th, the same logic applies.

Variations:

• Change the cell formatting of column C to “ddd dd-mmm-yyyy” to display both day name and date.
• Use absolute references for a single holiday list so you can drag without changing the holiday range.
  Troubleshooting:
• If you see a number rather than a formatted date, adjust the cell format.
• A #VALUE error indicates B2 is not a recognized date—use DATEVALUE or re-enter.

Example 2: Real-World Application

Scenario: A payroll analyst must build a schedule of payment dates for 2024–2025, avoiding weekends and ten company holidays stored in range [H2:H11]. Payments must be released on the last weekday before month end.

1. In B2, list the first day of each month from 01-Jan-2024 to 01-Dec-2025.
2. In C1 type “Pay Date”. In C2 enter:

=WORKDAY(EOMONTH(B2,0)+1,-1,$H$2:$H$11)

3. Copy down through all months.

4. Add conditional formatting:

• Select C2:C25, choose Home > Conditional Formatting > New Rule > Use a formula.
• Formula: =C2<TODAY()
• Set fill to light gray so past pay dates are muted visually.

Business context explanation:
The analyst can now link C2:C25 to ACH submission templates, pivot tables, or Power Query loads. If a holiday like Thanksgiving falls on a Friday, WORKDAY automatically backs up to Wednesday because Thursday is a holiday and Friday is excluded by the weekend definition.

Integration with other features:

• Use the dates as lookup keys in XLOOKUP to match timesheet batches.
• Feed them into Power Automate triggers by exporting the range to a SharePoint list.
  Performance considerations:
• A holiday range with hundreds of rows has negligible impact, but in extremely large models consider storing holidays in a dedicated sheet and defining a named range to avoid volatile offset references.

Example 3: Advanced Technique

Scenario: An international company operates in Dubai where weekends are Friday–Saturday, and local holidays should be skipped. They need a dynamic report that computes the last working day for each month in 2024 using the United Arab Emirates weekend pattern.

1. Populate B2:B13 with 01-Jan-2024 to 01-Dec-2024.
2. Save UAE holidays in [HolidayList] named range.
3. In C2 enter this advanced formula:

=WORKDAY.INTL(EOMONTH(B2,0)+1,-1,"0000110",HolidayList)

Explanation:

• WORKDAY.INTL allows a custom weekend mask with seven characters (Monday through Sunday).
• \"0000110\" marks Friday (character 6) and Saturday (character 7) as weekend days.
• The formula steps back across any combination of holiday or weekend until reaching a valid weekday.

Edge case management:

• If the last calendar day is Thursday but a local holiday, the formula backs up to Wednesday.
• Should Friday be a working day in a future policy change, update the weekend mask string without rewriting the entire formula.

Professional tips:

• Store the mask string in a named cell (e.g., WeekendMask) and reference it: =WORKDAY.INTL(EOMONTH(B2,0)+1,-1,WeekendMask,HolidayList) for greater maintainability.
• For large multi-region dashboards, construct a lookup table of weekend masks keyed by country code and retrieve them via XLOOKUP, then pass to WORKDAY.INTL using the result.

Tips and Best Practices

1. Define named ranges like Pay_Holidays to avoid hard-coding [A2:A20] ranges into formulas, improving readability.
2. Combine your last weekday dates with TEXT or TEXTJOIN functions to generate human-friendly labels such as “Salary cutoff on Fri 28-Jun-2024.”
3. Store holiday calendars in separate sheets and reference them with dynamic names so year-to-year updates require only pasting new dates.
4. When building templates, lock the formula cell with sheet protection but leave the holiday sheet editable for administrators.
5. Use array formulas or Spill ranges (Excel 365) with SEQUENCE to auto-generate entire year schedules without manual lists, reducing keying errors.
6. Document your weekend mask choices in cell comments or a legend so future users understand regional weekend logic.

Common Mistakes to Avoid

1. Treating text strings like \"2024-01-01\" as dates: Excel may interpret them differently across regional settings, leading to #VALUE errors. Convert via DATE, DATEVALUE, or paste as dates.
2. Forgetting holidays: If you omit the holiday parameter, payroll could land on Christmas Day. Always pass a range—even if currently empty—to future-proof your sheet.
3. Hard-coding the +1 and ‑1 without parentheses: Writing =WORKDAY(EOMONTH(A1,0)+1-1) accidentally cancels the offset and returns the month end, not the last weekday. Keep the parameters separate.
4. Using TODAY inside complex array operations: Volatile functions recalculate on every change, potentially slowing large models. Consider static start_date lists for better performance.
5. Copy-pasting formulas between workbooks with different regional weekend definitions without adjusting the WORKDAY.INTL mask, causing silent errors.

Alternative Methods

Below is a comparison of popular techniques:

Pick WORKDAY for mainstream, WORKDAY.INTL for regional variants, Power Query for refreshable data models, or VBA if you require highly specialized calendars (e.g., stock exchange trading days).

FAQ

When should I use this approach?

Whenever you need a deadline, cutoff, or anchor date at the end of each month that must never fall on a weekend or holiday. Typical use cases: payroll, billing cycles, inventory counts, and regulatory filings.

Can this work across multiple sheets?

Absolutely. Place the formula in one sheet and reference start_date lists or holiday ranges stored elsewhere. Named ranges make cross-sheet references cleaner: =WORKDAY(EOMONTH(Calendar!B2,0)+1,-1,Holidays!A2:A50).

What are the limitations?

WORKDAY assumes Monday-Friday unless you switch to WORKDAY.INTL. If you need to exclude only Sundays, or treat every second Friday differently, consider a custom VBA function or a lookup against a detailed workday calendar.

How do I handle errors?

• Use IFERROR to catch non-date inputs: =IFERROR(WORKDAY(...),"Invalid date")
• Wrap holiday lookups with IF to ignore blank holiday ranges.
• For debugging, break the formula: put EOMONTH result in one helper column, then WORKDAY referencing that helper to see intermediate values.

Does this work in older Excel versions?

WORKDAY and EOMONTH exist from Excel 2007 onward. Excel 2003 users must enable the Analysis ToolPak or fall back to the WEEKDAY + CHOOSE method. WORKDAY.INTL is available only in Excel 2010 and later (plus Microsoft 365).

What about performance with large datasets?

Both WORKDAY functions are efficient even on tens of thousands of rows. To optimize, minimize volatile inputs like TODAY, avoid array-entered formulas where possible, and use structured tables so formulas auto-fill without manual copying.

Conclusion

Knowing how to calculate the last weekday in any month turns Excel into a smart calendar assistant. Whether you manage payroll for hundreds of employees, coordinate global project timelines, or simply want accurate month-end labels on a chart, the techniques in this tutorial empower you to automate date logic, reduce manual errors, and future-proof your workbooks. Master the standard WORKDAY + EOMONTH pattern first, then explore WORKDAY.INTL, Power Query, or VBA for specialized needs. By integrating these formulas into your daily workflow you will elevate both the accuracy and professionalism of your Excel solutions—freeing up your time for deeper analysis and strategic work.