Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How To Compare Two Lists in Excel
  • Understanding Pivot Tables in Excel
  • How To Load Analysis ToolPak in Excel
  • How to do a t-Test in Excel?

References

  • How to get address of first cell in range in Excel
  • Last row number in range
  • How to use Excel ROW Function
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Merge tables with VLOOKUP in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Prevent invalid data entering in specific cells
  • Excel Data validation don’t exceed total
  • Excel Data validation allow uppercase only
  • How To Create Drop-down List in Excel

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:

Formula

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

>

Worked Example:   How to calculate Next working/business day in Excel

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:   WORKDAY function: Description, Usage, Syntax, Examples and Explanation

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.

Post navigation

Previous Post:

Number and Text Filters Examples in Excel

Next Post:

Convert date string to date time 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

Logical Functions

  • How to use IFS function in Excel
  • Check multiple cells are equal in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel FALSE Function

Date Time

  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Display Date is same month in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • How to calculate Next working/business day in Excel
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel

General

  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • Check if range contains a value not in another range in Excel
  • How to calculate percentage discount in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • Delete Blank Rows at Once in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning