Forum Discussion
Problem with the EDATE function
If you use EDATE(DATE(2024,12,1)-1,1) it returns 30/12/2024 but there are 31 days in that date,
What should I do or who should I report about this problem?
2 Replies
Crossposted to answers.microsoft.com (click here), where I posted the following response on Jan 4, 2025.
EDATE(date, n) returns the same day of the n-th month, or the last day when there is no corresponding date.
I believe EDATE follows US law, if not international law. For example, "European Convention on the Calculation of Time-Limits" (click here), Article 4 paragraph 2.
And yes, that has the unfortunate consequence that EDATE is not "invertible" (mathematically).
That is, EDATE(EDATE(date), 1), -1) is not always "date"; for example, for DATE(2023, 10, 31).
DATE(2024,12,1)-1 is the 30th of November.
EDATE leaves the day unchanged unless it would be after the end of the target month.
So EDATE(DATE(2024,12,1)-1,1) returns the 30th of December.
If you want to return the last day of the month, use
=EOMONTH(DATE(2024,12,1)-1,1)
or shorter
=EOMONTH(DATE(2024,12,1),0)
