How to Fix EDATE Returning December 30 Instead of 31 in Excel
Question details
The user is trying to calculate a month-end date but notices that adding one month to November 30 using the EDATE function results in December 30 rather than the actual last day of December (December 31).

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating the end-of-month date or projecting timelines using date functions.
- Observed behavior
- The EDATE function preserves the exact day number (30) from the original date, outputting December 30 instead of the last day of the target month.
Verify your original date format and determine whether your calculation strictly requires the exact same calendar day in the next month, or if you actually need the final day of the target month.
Use the EOMONTH Function to Get the Last Day of the Month
The best way to guarantee a month-end date calculation is to replace the EDATE function with the EOMONTH function.
The EDATE function is designed to preserve the original day number. Since November 30 has the day '30', shifting it forward by one month returns the 30th day of the target month, which is December 30.
To get the final day of a given month, you should use the EOMONTH (End Of Month) function instead. EOMONTH specifically calculates the last day of a target month, completely ignoring the starting date's specific day number.
Click on the cell where you want the final, corrected date to appear.
To shift a specific date like November 30 forward by one month, type `=EOMONTH(DATE(2024,11,30), 1)`. If referencing a cell, type `=EOMONTH(A1, 1)`.
If you want the last day of the current month based on a November 30 date, use `=EOMONTH(DATE(2024,11,30), 0)`.
Press Enter. If the result appears as a plain serial number (e.g., 45657), select the cell, right-click and choose 'Format Cells', then apply your preferred Date format.

Understand the Limitations of the EDATE Function
Use EDATE only when you need the exact matching day number in future or past months, while being aware of how it handles non-existent days.
Solve Advanced Date Calculations Easily in WPS Spreadsheet
WPS Spreadsheet fully supports all standard Excel date functions, including EDATE and EOMONTH. It provides an intuitive formula interface to help you manage financial models and project timelines with pinpoint accuracy.
- 1. Download and Install: Get WPS Office from the official website and launch WPS Spreadsheet.
- 2. Open Your Workbook: Load your existing Excel file (.xlsx) containing the problematic date formulas.
- 3. Apply Date Functions: Navigate to the 'Formulas' tab to insert or edit EDATE and EOMONTH functions effortlessly using the built-in function wizard.

Frequently Asked Questions
Why does using EDATE on January 31 return February 28?
Since February does not have 31 days, EDATE cannot preserve the exact day number. Instead, it automatically rounds down to the last valid day of the target month, returning February 28 (or February 29 during a leap year).
What is the exact difference between EDATE and EOMONTH?
EDATE returns the exact same day of the month for the target month (e.g., the 15th stays the 15th). EOMONTH, on the other hand, always forces the result to be the final day of the target month, regardless of what day of the month you started with.
How do I calculate the first day of the next month?
You can use EOMONTH to find the last day of the current month, and then simply add 1 to the result. For example, `=EOMONTH(A1, 0) + 1` will return the first day of the following month.




