# Adding a number of months to a date using formulas

I have a calibration sheet that lists the number of months required between calibration dates and a column that states when the last calibration was carried out. I would like to use these 2 columns to calculate the date when the equipment next requires calibration. Can anyone help?

Thanks,

Colin

«1

• ✭✭✭✭✭✭

I recently helped someone else here in the community with adding n months to a date. Let me dig around and see if I can find the link.

• Thanks Paul, worked a treat.

• ✭✭✭✭✭✭

Happy to help! 👍️

• My friend and I came up with this:

=IF(ISBLANK([EMROLLMENT DATE - NOMs: Initial Date Completed]@row), "TBD", IF(MONTH([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) < 10, DATE(YEAR([EMROLLMENT DATE - NOMs: Initial Date Completed]@row), MONTH([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) + 3, DAY([EMROLLMENT DATE - NOMs: Initial Date Completed]@row)), DATE(YEAR([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) + 1, MONTH([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) - 9, DAY([EMROLLMENT DATE - NOMs: Initial Date Completed]@row))))

• ✭✭✭✭✭✭

@Jen Eldridge It looks like that would only be for adding 3 months and not a variable amount of months that could be stored in a separate column. Am I reading it correctly?

• ✭✭

Hi, I have a date column and I want to generate a new date by adding 6 months to the date. I have used this formula-

=DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 1, DAY([Go Live Date in V2]@row))

but its only working within the year when date lays in next year its giving me an error so can you help me with that thanks.

• ✭✭✭✭✭✭

@gourav To add six months, it would look something like this...

=IFERROR(DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 6, DAY([Go Live Date in V2]@row)), DATE(YEAR([Go Live Date in V2]@row) + 1, MONTH([Go Live Date in V2]@row) - 6, DAY([Go Live Date in V2]@row)))

• ✭✭
edited 04/30/21

Hi Paul

I am trying to replicate this for an application that is very similar to the calibration one but my attempt at amending the formula is showing as #UNPARSEABLE. Here is a screen shot of the sheet and it relates to the last 3 columns:

Thanks

Ed

• ✭✭✭✭✭✭

@Ed Rose Exactly which formula are you trying to modify?

• ✭✭

Hi Paul

Your original one for Colin Alexander. I am trying to use your formula to automatically generate the Next Test Date by adding Retest Interval - Months to the Last Test Date column figure. I then can create further automation to send me a notification when a retest is required.

Many thanks

Ed

• ✭✭✭✭✭✭

@Ed Rose How exactly are you populating the number of months? It looks like they are being stored as text values?

• ✭✭

Hi Paul. Yes they are text values.

• ✭✭✭✭✭✭

There's the issue then. You are going to want to have them either stored as numerical values or you are going to need to wrap each instance of that column name in a VALUE function within the formula.

• ✭✭

It's a drop-down (single select) column. Please can you help me with the correct formula for this.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!