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

Data Analysis

  • How to create Checklist in Excel
  • Randomize/ Shuffle List in Excel
  • Understanding Pivot Tables in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Excel Bar Chart

References

  • Two-column Lookup in Excel
  • How to get last row in numeric data in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to get address of first cell in range in Excel
  • Perform case-sensitive Lookup in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation exists in list
  • Excel Data validation must not contain
  • Excel Data validation must contain specific text
  • Excel Data validation date in next 30 days

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.

Worked Example:   Average by month in Excel

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.

Worked Example:   How to calculate workdays per month in Excel

 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.

Worked Example:   Steps to create Dynamic calendar grid in Excel

 

Post navigation

Previous Post:

Get last weekday in month in Excel

Next Post:

Get fiscal year from date 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

  • Invoice status with nested if in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • IF with boolean logic in Excel

Date Time

  • Display Days in month in Excel
  • Add decimal minutes to time in Excel
  • Convert Excel time to decimal hours in Excel
  • Convert time to time zone in Excel
  • Get project midpoint in Excel

Grouping

  • Categorize text with keywords in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Group times into unequal buckets in Excel
  • Calculate conditional mode with criteria in Excel

General

  • How to password protect excel sheet?
  • Count cells that do not contain errors in Excel
  • Mark Workbook as Final in Excel
  • How to calculate project complete percentage in Excel
  • How to calculate percent sold in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning