Jul 03 2019 12:45 PM
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?
Jul 03 2019 12:59 PM
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
Jul 04 2019 10:19 AM
Jul 04 2019 11:42 AM
@Twifoo , if only in leap years it's not Feb 28 required
Jul 04 2019 01:47 PM