EOMONTH function is used to calculate the last day of a month before and after a specified date. Its basic syntax is =EOMONTH(start_date, months), where start_date is the starting date and months is the number of months (positive numbers represent the month afterwards, and negative numbers represent the previous one). For example, =EOMONTH("2024-05-15", 1) returns 2024-06-30, =EOMONTH(A1, -1) If A1 is 2024-05-15, it returns 2024-04-30. This function can be used to calculate the end of the quarter, the end of the sixth year or the end of the year. If you use =EOMONTH(A1, MOD(3 - MONTH(A1), 3)) to obtain the end of the quarter. Combining the TODAY function can realize dynamic calculations, such as =EOMONTH(TODAY(), 0) to obtain the last day of the month. When using it, you need to ensure that the date format is correct, no add-in support is required, and the New Year's Eve situation can be handled correctly.
The EOMONTH function is a very practical date function in Excel, which is mainly used to calculate the last day of a month before or after a date. This function is especially useful if you need to process data related to the end of the month, such as financial statements, project cycles, accounting period statistics, etc.
Basic syntax and usage
The basic format of EOMONTH is:
=EOMONTH(start_date, months)
-
start_dateis the start date, which can be a date value or a reference to a cell containing a date. -
monthsis a number that indicates the number of months to move forward or backward. Positive numbers represent the month afterwards, and negative numbers represent the month before.
For example:
=EOMONTH("2024-05-15", 1)The result is 2024-06-30 , that is, from May 2024 to the end of the month after.
Another example:
=EOMONTH(A1, -1)
If A1 is 2024-05-15, the result will be 2024-04-30 , the end of the previous month.
How to calculate the end of quarter, half year or year end using EOMONTH
Sometimes we need to find out the quarter, semi-annual or last working day of the year where a date is, and at this time we can use EOMONTH with some simple mathematical operations to achieve it.
For example, if you want to know the end of a certain date, you can write the formula like this:
=EOMONTH(A1, MOD(3 - MONTH(A1), 3))
What this means here:
Divide each month by 3 to take the remainder, and you can determine which quarter it belongs to. Then, based on the current quarter, it is calculated that there are still several months before the end of the quarter, and then passed to the EOMONTH function.
Similar ideas can also be used to find the end of the six months (every six months) or the end of the year (fixed as the 12th month).
Use flexibly in combination with TODAY or other functions
EOMONTH is ideal for use with other functions, especially dynamic functions like TODAY().
For example, if you want to know "the last day of this month", you can write directly:
=EOMONTH(TODAY(), 0)
If you want to automatically count "the last day of next month", you can write it as:
=EOMONTH(TODAY(), 1)
This combination is particularly convenient when making automated reports, such as automatically generating sales summary for the month, inventory inventory and other scenarios.
Notes and FAQs
- The date format must be correct : EOMONTH is sensitive to the input date format. If you type text instead of a real date, it may return an incorrect or incorrect result.
- Loading the analysis tool library? No need : Some materials may mention that you need to enable the "Analysis Tool Library" to use EOMONTH, but in fact, this function has been built in since Excel 2007.
- There is no problem with the New Year’s Eve : For example, you typed 2023-12-15, and adding 1 month can also return to 2024-01-31 correctly, and there will be no logical errors.
Basically that's it. Master EOMONTH and cooperate with a little trick to easily solve many calculation tasks related to the end of the month.
The above is the detailed content of How to use the EOMONTH function in Excel. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
What is the meeting time limit for the free version of Teams?
Jul 04, 2025 am 01:11 AM
MicrosoftTeams’freeversionlimitsmeetingsto60minutes.1.Thisappliestomeetingswithexternalparticipantsorwithinanorganization.2.Thelimitdoesnotaffectinternalmeetingswhereallusersareunderthesameorganization.3.Workaroundsincludeendingandrestartingthemeetin
how to group by month in excel pivot table
Jul 11, 2025 am 01:01 AM
Grouping by month in Excel Pivot Table requires you to make sure that the date is formatted correctly, then insert the Pivot Table and add the date field, and finally right-click the group to select "Month" aggregation. If you encounter problems, check whether it is a standard date format and the data range are reasonable, and adjust the number format to correctly display the month.
How to Fix AutoSave in Microsoft 365
Jul 07, 2025 pm 12:31 PM
Quick Links Check the File's AutoSave Status
How to change Outlook to dark theme (mode) and turn it off
Jul 12, 2025 am 09:30 AM
The tutorial shows how to toggle light and dark mode in different Outlook applications, and how to keep a white reading pane in black theme. If you frequently work with your email late at night, Outlook dark mode can reduce eye strain and
how to repeat header rows on every page when printing excel
Jul 09, 2025 am 02:24 AM
To set up the repeating headers per page when Excel prints, use the "Top Title Row" feature. Specific steps: 1. Open the Excel file and click the "Page Layout" tab; 2. Click the "Print Title" button; 3. Select "Top Title Line" in the pop-up window and select the line to be repeated (such as line 1); 4. Click "OK" to complete the settings. Notes include: only visible effects when printing preview or actual printing, avoid selecting too many title lines to affect the display of the text, different worksheets need to be set separately, ExcelOnline does not support this function, requires local version, Mac version operation is similar, but the interface is slightly different.
How to Screenshot on Windows PCs: Windows 10 and 11
Jul 23, 2025 am 09:24 AM
It's common to want to take a screenshot on a PC. If you're not using a third-party tool, you can do it manually. The most obvious way is to Hit the Prt Sc button/or Print Scrn button (print screen key), which will grab the entire PC screen. You do
Where are Teams meeting recordings saved?
Jul 09, 2025 am 01:53 AM
MicrosoftTeamsrecordingsarestoredinthecloud,typicallyinOneDriveorSharePoint.1.Recordingsusuallysavetotheinitiator’sOneDriveina“Recordings”folderunder“Content.”2.Forlargermeetingsorwebinars,filesmaygototheorganizer’sOneDriveoraSharePointsitelinkedtoaT
how to find the second largest value in excel
Jul 08, 2025 am 01:09 AM
Finding the second largest value in Excel can be implemented by LARGE function. The formula is =LARGE(range,2), where range is the data area; if the maximum value appears repeatedly and all maximum values ??need to be excluded and the second maximum value is found, you can use the array formula =MAX(IF(rangeMAX(range),range)), and the old version of Excel needs to be executed by Ctrl Shift Enter; for users who are not familiar with formulas, you can also manually search by sorting the data in descending order and viewing the second cell, but this method will change the order of the original data. It is recommended to copy the data first and then operate.
