Calculating a date up to 60 months in future

Options
✭✭

I need to calculate a future date from a past date + a number of months. I have a working formula but it breaks after 14 MONTHS . I think it creates some sort of circular reference error after it passes the current MONTH in the following YEAR but am so far unable to correct the issue. I think i need to nest some additional IF statement in there for ">" 24, 36, 48 & 60 months of Shelf Life.

=IF(MONTH(CurrCal@row) + [ShelfLife (months)]@row > 12, DATE(YEAR(CurrCal@row) + 1, MONTH(CurrCal@row) - (12 - [ShelfLife (months)]@row), DAY(CurrCal@row)), DATE(YEAR(CurrCal@row), MONTH(CurrCal@row) + [ShelfLife (months)]@row, DAY(CurrCal@row)))

Tags:

• ✭✭✭✭✭✭
Options

Could you convert the ShelfLife (months) into days and then just add the days to CurrCal, without the need for IFs?

• ✭✭✭✭✭✭
Options

Could you convert the ShelfLife (months) into days and then just add the days to CurrCal, without the need for IFs?

• ✭✭
Options

Thanks for the suggestion KPH! I added a helper column to convert Months to Days (30.4/Month) which gives a minor variance on some dates, but works perfectly for my needs. A very simple solution, bravo.

• ✭✭✭✭✭✭
Options

Wonderful! Glad the easy option was good enough.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!