Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get relative column numbers in a range in Excel
  • How to use Excel VLOOKUP Function
  • Count rows with at least n matching values
  • How to get last row in mixed data with blanks in Excel
  • Convert text string to valid reference in Excel using Indirect function

Data Analysis Examples

  • How to count table rows in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to Use Solver Tool in Excel
  • Subtotal function in Excel
  • Conditional Formatting Icon Sets Examples in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation unique values only
  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days

Get last working day in month in Excel

by

If you need to determine the last working day of a given month and year, then this tutorials is for you.

To get the last working day in a month, you can use the WORKDAY function together with the EOMONTH function. See example below:

Worked Example:   How to generate random date between two dates in Excel

Formula

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

>

In the example, the formula in C4 is:

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

Explanation

Working from the inside out, the EOMONTH function gets the last day of month of any date. To this result, we add 1, which results in the first day of the next month.

Worked Example:   Get project midpoint in Excel

This date goes into WORKDAY function as the “start date”, along with -1 for “days”. The WORKDAY function automatically steps back 1 day, taking into account any weekends. The result is the last workday of the month.

Holidays

To get the last working day of the month, taking into account holidays, just add the range that contains holiday dates to the formula like this:

=WORKDAY(EOMONTH(B4,0)+1,-1,holidays)

Custom weekends

The WEEKDAY function assumes weekends are Saturday and Sunday. If you need to customize weekend days, you can use the WEEKDAY.INTL function.

Worked Example:   Add business days to date in Excel

Post navigation

Previous Post:

Get day name from date in Excel

Next Post:

Get days, hours, and minutes between dates in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to create email address from name in Excel
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • How to get first word in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • Get last weekday in month in Excel
  • How to join date and text together in Excel
  • Calculate days remaining in Excel
  • Get days between dates in Excel
  • How to calculate next scheduled event in Excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • How to calculate annual compound interest schedule in Excel
  • Calculate periods for annuity in Excel
  • Future value vs. Present value examples in Excel
  • Calculate loan interest in given year in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles