Expiration Date with Edate accounting for leap years

**Chris69** · 04-01-2021, 09:17 AM

Using excel to indicate expiration dates by X # of months form the date of manufacture, but it does not accurately account for leap years.

Formula is =EDATE(G3,18) (Where G3 is the manufacture date, and we want to add 18 months to get expiry). This usually works, however....

March 30 2021 should yield an Expiry of Sept 30 2021 - which works with the EDATE function above.

March 31 2021 should yield an Expiry of Oct 1 2021 (leap year!) - BUT it doesn't! It still yields Sept 30 2021 (so short 1 day)

What am I doing wrong? Or how do we account for leap years when calculating expiration?

**Bernie Deitrick** · 04-01-2021, 10:19 AM

That's not how date math works in Excel. The logic for figuring out the day return by EDATE is to use the minimum of

=DAY(G3)

And the last day of the new month

=DAY(DATE(YEAR(G3),MONTH(G3)+19,0))

So, if 18 months in the future is February of a non-leap year, any starting date day that is 28, 29, 30, or 31 will return 28

If you want a specific number of days, then just use

=G3+547

547 is 18 months....

Expiration Date with Edate accounting for leap years

LinkBack

Thread Tools

Rate This Thread

Display

Expiration Date with Edate accounting for leap years

Re: Expiration Date with Edate accounting for leap years

Thread Information

Users Browsing this Thread

Similar Threads

leap years and date calculation

Simple question - accounting for leap years in formulas calculating dates

[SOLVED] Date function not accounting for leap year

[SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

Date Differences and ignoring Leap Years

Excel =Date and Leap Years

Problem with displaying a date range for leap years

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions