Forum Discussion
Excel End of Month Question
I want to be able to have Excel automatically add a date based on another set of data. For example, we have a production date of 7/3/2019. The shelf life in most cases is 4 years, but there are some that expire in 6 months, 1 year or 2 years, etc.. Our expiration date is always the 30th of the month, except for February which is the 28th. Is there a formula that will allow me to tell Excel to automatically add the 28th or 30th of the month, and the appropriate amount of years based on the production date I type in? So for this example, the production date is 7/3/2019. My expiration date is 7/30/2021. I don't think EOMONTH will work as it will give me the absolute end of month. Any suggestions?
4 Replies
- If production date is in A2, and expiry months is in B2, try this formula in C2 to return the expiry date:
=EOMONTH(A2,B2)-
OR(MONTH(A2)={1,3,5,7,8,10,12})Twifoo , if only in leap years it's not Feb 28 required
- Thanks for your astute remark, SergeiBaklan. I wilfully ascribed the expiry date a liberal construction as to mean a day not later than the 30th. Thus, such date may be the 29th of February during leap years.
If a strict construction is to pursued, such that expiry dates in February always occur on the 28th, my formula shall be slightly longer with an addition of another logical test, like this:
=EOMONTH(A2,B2)-
OR(MONTH(A2)=
{1,3,5,7,8,10,12})-
(DAY(EOMONTH(A2,B2))=29)
That could be like
=EOMONTH(A1,12)+IF(DAY(EOMONTH(A1,12))>29,30,28)-DAY(EOMONTH(A1,12))
if we take 12th months period
