Adding specified months to a date in another column

Gavin Seaton

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

Gia Thinh

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.

Gavin Seaton

Gia Thinh

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.

Gavin Seaton

Hi @Gia Thinh

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

Gia Thinh

Yeah. That's good!

GAC PINDAR

@Gia Thinh

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

Gia Thinh

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.

Con Ayroso-Marcelo

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

Gia Thinh

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)))