Adding specified months to a date in another column

Gavin Seaton
Gavin Seaton ✭✭✭✭
edited 08/03/23 in Formulas and Functions

Hi

Looking for some help. I've tried several formulas (Mainly suggested by Chat GPT) but can't seem to get anything to do the following:

  1. Take 'End Date' column cell
  2. Add however many Months are specified in 'Defects Period' column cell (This is currently a set dropdown)
  3. Return date in the 'End of Defects Date' Column cell

Attached image shows the last tried formula and layout.

I had thought of putting in an add specific days formula based on the dropdown value but each month has different durations so that wouldn't work.

Kind Regards

Gavin

Best Answer

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Hi Gavin,

    Try this formula in the "End of Defects Date" column :

    =DATE(YEAR([End Date]@row) + ROUNDDOWN((MONTH([End Date]@row) + [Defects Period (Months)]@row) / 12, 0), MOD(MONTH([End Date]@row) + [Defects Period (Months)]@row, 12), DAY([End Date]@row))

    Hope it works.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!