Adding specified months to a date in another column

✭✭✭✭
edited 08/03/23

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

• ✭✭✭✭✭✭

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 - Smartsheet Solution Partner.

• ✭✭✭✭
• ✭✭✭✭✭✭

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 - Smartsheet Solution Partner.

• ✭✭✭✭

That formula worked a treat, thanks for your help, much appreciated 👍️

• ✭✭✭✭✭✭

Yeah. That's good!

Gia Thinh Technology - Smartsheet Solution Partner.

• Are you able to assist with a formula for the below:

I am looking to add 6 months to Chief Date of Entry / CDS Entry Date and show in column Expiry Date.

I have tried multiple versions of the above options but I can't seem to get a match.

Regards,

Martin

• ✭✭✭✭✭✭

Hi @GAC PINDAR,

Simplify your sheet and column names, I have tested the formula on my sheet below.

Try this one :

`=IF((MONTH([Date of Entry]@row) + Validity@row) <= 12, DATE(YEAR([Date of Entry]@row), MONTH([Date of Entry]@row) + Validity@row, DAY([Date of Entry]@row)), DATE(YEAR([Date of Entry]@row) + 1, MONTH([Date of Entry]@row) + Validity@row - 12, DAY([Date of Entry]@row)))`

Hope it works for you.

Gia Thinh Technology - Smartsheet Solution Partner.

• Hi @Gia Thinh,

Have also adapted this formula to my sheet. However, this works only up to 20 months. More than that it returns an #INVALID VALUE. Help?

Thanks,

@Con Ayroso-Marcelo

• ✭✭✭✭✭✭

Try this generic formula for any number of Validity:

`=IF((MONTH([Date of Entry]@row) + MOD(Validity@row, 12)) <= 12, DATE(YEAR([Date of Entry]@row) + ROUNDDOWN(Validity@row / 12), MONTH([Date of Entry]@row) + MOD(Validity@row, 12), DAY([Date of Entry]@row)), DATE(YEAR([Date of Entry]@row) + ROUNDUP(Validity@row / 12), MONTH([Date of Entry]@row) + MOD(Validity@row, 12) - 12, DAY([Date of Entry]@row)))`

Gia Thinh Technology - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!