Adding a number of months to a date using formulas

colin alexander · 03/19/20

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

Paul Newcome · 03/20/20

HERE is a link to a thread that wanted to do the same exact thing. Have a date in one column, a number (of months to add) in a second column, and a formula in a third column (date type) that will add that number of months to the original date.

I took the formula there and updated the column names to the ones in your screenshot.

NOTE: Your screenshot shows a typo in a column name. You have "Calbration" in the screenshot and "Calibration" in your post. I used the first when I updated the formula below so that it would match the column name. If you enter the formula before changing the spelling in your column, the formula will automatically update with the new spelling. If you change the spelling in the sheet first, then you will need to add the "i" to each appropriate cell reference in the formula to get it to work.

=DATE(YEAR([Calbration Date]@row) + ROUNDDOWN((MONTH([Calbration Date]@row) + Months@row) / 12, 0) + IF(IF(MOD(MONTH([Calbration Date]@row) + Months@row, 12) = 0, 12, MOD(MONTH([Calbration Date]@row) + Months@row, 12)) = 12, -1), IF(MOD(MONTH([Calbration Date]@row) + Months@row, 12) = 0, 12, MOD(MONTH([Calbration Date]@row) + Months@row, 12)), DAY([Calbration Date]@row))

Paul Newcome · 03/20/20

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.

Paul Newcome · 03/20/20

HERE is a link to a thread that wanted to do the same exact thing. Have a date in one column, a number (of months to add) in a second column, and a formula in a third column (date type) that will add that number of months to the original date.

I took the formula there and updated the column names to the ones in your screenshot.

NOTE: Your screenshot shows a typo in a column name. You have "Calbration" in the screenshot and "Calibration" in your post. I used the first when I updated the formula below so that it would match the column name. If you enter the formula before changing the spelling in your column, the formula will automatically update with the new spelling. If you change the spelling in the sheet first, then you will need to add the "i" to each appropriate cell reference in the formula to get it to work.

=DATE(YEAR([Calbration Date]@row) + ROUNDDOWN((MONTH([Calbration Date]@row) + Months@row) / 12, 0) + IF(IF(MOD(MONTH([Calbration Date]@row) + Months@row, 12) = 0, 12, MOD(MONTH([Calbration Date]@row) + Months@row, 12)) = 12, -1), IF(MOD(MONTH([Calbration Date]@row) + Months@row, 12) = 0, 12, MOD(MONTH([Calbration Date]@row) + Months@row, 12)), DAY([Calbration Date]@row))

colin alexander · 03/23/20

Thanks Paul, worked a treat.

Paul Newcome · 03/23/20

Happy to help! 👍️

Jen Eldridge · 10/16/20

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

Paul Newcome · 10/16/20

@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?

gourav · 03/24/21

https://community.smartsheet.com/discussion/comment/243593#Comment_243593

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.

Paul Newcome · 03/25/21

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

Ed Rose · 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:

Please can you help me

Thanks

Ed

Paul Newcome · 04/30/21

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

Ed Rose · 05/04/21

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

Paul Newcome · 05/04/21

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

Ed Rose · 05/05/21

Hi Paul. Yes they are text values.

Paul Newcome · 05/05/21

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.

Ed Rose · 05/06/21

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

Adding a number of months to a date using formulas

Best Answer

Answers

Help Article Resources

Categories