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

Lookup and Reference Examples

  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Vlookup Examples in Excel
  • Find closest match in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel

Data Analysis Examples

  • How to Create Gantt Chart in Excel
  • How to perform a t-Test in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to create Gauge Chart in Excel
  • Error Bars in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number
  • Excel Data validation specific characters only

Get last weekday in month in Excel

by

This tutorial shows you how to find the last weekday of the month in Excel.

To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions.

Formula

=EOMONTH(date,0)+1-WEEKDAY(EOMONTH(date,0)+1-dow)

Explanation

First, this formula determines the first day of the next month *after* a given date. It does this my using EOMONTH to get the last day of the month, then adding one day:

=EOMONTH(B5,0)+1

In the example shown, the formula in D5 is:

=EOMONTH(B5,0)+1-WEEKDAY(EOMONTH(B5,0)+1-C5)

 

Next, the formula calculates the number of days required to “roll back” to the last requested weekday in the month prior (i.e. the month of the original date):

WEEKDAY(EOMONTH(B5,0)+1-C5)

Inside WEEKDAY, EOMONTH is again used to get the first day of the next month. From this date, the value for day of week is subtracted, and the result is fed into WEEKDAY, which returns the number of days to roll back.

Worked Example:   How to get Holiday Date from Year in Excel

Last, the roll back days are subtracted from the first day of the next month, which yields the final result.

Worked Example:   How to calculate next day of week in Excel

Other weekdays

In the general form of the formula at the top of the page, day of week is abbreviated “dow”. This is a number between 1 (Sunday) and 7 (Saturday) which can be changed to get a different day of week. For example, to get the last Thursday of a month, set dow to 5.

Post navigation

Previous Post:

Basic timesheet formula with breaks in Excel

Next Post:

Get last day of month 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
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • Get last name from name with comma — Manipulating NAMES in Excel
  • How to count total words in a cell in Excel
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • How to extract name from email address in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Count holidays between two dates in Excel
  • How to calculate quarter from date in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Get first Monday before any date in Excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Example of Future value of annuity in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles