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

Data Analysis

  • Subtotal function in Excel
  • Working With Tables in Excel
  • How to Create Gantt Chart in Excel
  • Chart Axes in Excel
  • How To Load Analysis ToolPak in Excel

References

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Find closest match in Excel
  • How to use Excel MATCH Function
  • How to get last row in mixed data with blanks in Excel
  • Create hyperlink with VLOOKUP in Excel

Data Validations

  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation allow uppercase only
  • Excel Data validation with conditional list

Get last day of month in Excel

by

If you want to calculate the last day of a month based on a given date, then this tutorials is for you.

The example below shows two different ways to achieve this.

First, use the EOMONTH function.

Formula

=EOMONTH(date,0)

Explanation

In the example shown, the formula in cell B5 is:

=EOMONTH(B5,0)

The 2nd argument (months) of the EOMONTH function allows you to get the last day of the month in future or past months. When you use zero for months, EOMONTH will return the last day of the month in the same month.

To get the last day of the prior month, use:

=EOMONTH(date,-1)

To get the last day of the next month, use:

=EOMONTH(date,1)

Although EOMONTH is a more convenient function, it won’t accept a range of dates in an array formula. In that case, you can also use the alternative below.

 Alternative formula

You can also write a formula using the DATE, YEAR and MONTH functions to return the last day of the month:

=DATE(YEAR(date),MONTH(date)+1,0)

The trick with this formula is supplying zero for the day. When you supply zero as the day argument to DATE, the date function will “roll back” one day to the last day of the previous month. So, by adding 1 to the month, and using zero for day, DATE returns the last day of the “original” month.

 

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

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

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • How to return blank in place of #DIV/0! error in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert time to time zone in Excel
  • Get days, months, and years between dates in Excel
  • Add days to date in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Find Last Day of the Month in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Map text to numbers in Excel

General

  • How to create dynamic worksheet reference in Excel
  • Creating and Opening an existing file in Excel
  • Currency vs Accounting Format in Excel
  • How to generate random date between two dates in Excel
  • How to calculate percent of goal in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning