WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Last Day of the Month in Excel

Last Day of the Month in Excel

How to Find Last Day of the Month in Excel?

To find the last date of the month we will use the inbuilt function of excel that is called the EOMONTH. This function will help us to return the last date of the month. The functionality of this function is not only restricted to know the last date of the current month but we can choose to know the last day of the previous month, next month and we can also choose to know the last day of the month at the Custom defined gap of months.

Syntax

Eomonth Formula

Examples to use EOMONTH in Excel

You can download this Last Day of the Month Excel Template here – Last Day of the Month Excel Template

Example #1 – Last Day of the Same Month

  • Step 1 – Insert the Date incorrect format; remember the default format is “MM_DD_YY.”

last day of month step 1

  • Step 2 – Enter the formula of EOMONTH.

last day of month step 2

  • Step 3 – After the EOMONTH function is inserted, we will get the last day in the vale format.

last day of month step 3

  • Step 4 – You can now use drag and drop options for the remaining cells.

last day of month step 3-1

  • Step 5 – Now, we need to correct the format for this value and set that to date format. For that first, select the cell B3, then you can go to the Home Tab, then go to general and select a short date option, as shown in the below screenshot.

last day of month step 4

  • Step 6 – After the format is corrected, we will get the last day of the month in excel.

last day of month step 5

Example #2 – Last Date of Next Month

To calculate the last date of next month.

Last Date of Next Month step 1

  • Step 1 – we just need to change the formula and mention “1” instead of “0”.

Last Date of Next Month step 2

  • Step 2 – After the EOMONTH function is inserted, we will get the last day of next month.

Last Date of Next Month step 3

  • Step 3 – You can now use drag and drop options for the remaining cells.

Last Date of Next Month step 4

Example#3 – Last Day of Previous Month

In this example, we need to specify the last day of the previous month

Example3 step 1

  • Step 1 – We just need to change the formula and mention “month” as “-1” instead of “0”.

Example3 step 2

  • Step 2 – After the EOMONTH function is inserted, we will get the last day of the previous month.

Example3 step 3

  • Step 3 – You can now use drag and drop options for the remaining cells.

Example3 step 4

Example #4 – Last Day of the Month as Per Custom Month Gap

In this case, we need to make the “month “Argument as dynamic instead of a static one, and all the arguments will be the same as mentioned in the above steps.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example4 step 1

  • Step 1 – To calculate the last day of the month in excel, we just need to change the formula.

Example4 step 2

  • Step 2 – After the EOMONTH function is inserted, we will get the last day of the month as per the custom month Gap.

Example4 step 3

  • Step 3 – You can now use drag and drop options for the remaining cells.

Example4 step 4

Things to Remember

  • The result of EOMONTH will not be formatted as a date; the result needs to be formatted as a date.
  • The “Date” should be in the “MM_DD_YY” format.
  • If the “Date” is not an incorrect format, there will be a #NUM error.
  • We can calculate the day that falls on the last date of the month by using the “Text” function of Excel.

Recommended Articles

This has been a guide to find the Last Day of the Month. Here we discuss how to Find Last Day of the Month in Excel using the EOMONTH function along with examples and a downloadable template. You may learn more about excel from the following articles –

  • Date Function in VBA
  • How to Convert Date to Text in Excel?
  • EDATE Function in Excel
  • Using DATE Function in Excel
  • Exponents in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Last Day of the Month Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More