Adding months to a cell linked date

Simon Bamford

Hi everyone,

I am trying to add 3 months and 6 months on from a date that comes from another sheet.

=DATE(YEAR([Date (Hidden)]5), MONTH([Date (Hidden)]5) + 6, DAY([Date (Hidden)]5))

That is the formula I am using.

As long as it is in the same year this formula works but if the it goes into the next year the formula becomes an invalid value.

Any ideas on how I could let it change the year as well?

Thank you

Jeff Reisman

@Simon Bamford

You just need to make this an IF, based on if the month is before July (for the 6 month add) or before October (for the 3 month add.)

For 6 months:

=IF(MONTH([Date (Hidden)]5) < 7, DATE(YEAR([Date (Hidden)]5), MONTH([Date (Hidden)]5) + 6, DAY([Date (Hidden)]5)), DATE(YEAR([Date (Hidden)]5) + 1, MONTH([Date (Hidden)]5) - 6, DAY([Date (Hidden)]5)))

In English, if the month of the [Date (Hidden)]5 value is less than 7 (July), build the date in this year with the month 6 months ahead, otherwise, build the date in next year, with the month minus 6 (ex. August - 6 = Feb, October - 6 = April).

For 3 months:

=IF(MONTH([Date (Hidden)]5) < 10, DATE(YEAR([Date (Hidden)]5), MONTH([Date (Hidden)]5) + 3, DAY([Date (Hidden)]5)), DATE(YEAR([Date (Hidden)]5) + 1, MONTH([Date (Hidden)]5) - 9, DAY([Date (Hidden)]5)))

In English, if the month of the [Date (Hidden)]5 value is less than 10 (Oct), build the date in this year with the month 3 months ahead, otherwise, build the date in next year, with the month minus 9 (ex. Oct - 9 = January, December - 9 = March).

Jeff Reisman

@Simon Bamford Happy to help, Simon.

The one wrinkle you may run into is when the day value is a day that doesn't exist in the month for the date you're creating. For example, if the date you want is 3 months after November 30th, you end up with February 30th, which isn't a thing. The date value of 2/30/23 will save, but if you try to run any calculations or automations based on that date value, they will fail.

With a helper date-type column, you could put some automation in place that will alert you when an invalid date value is entered, so that you can manually fix it. Use a formula in the helper column such as:

=IFERROR([6 Month Date column]@row - [Date (Hidden)]@row, "Invalid Date Value - Please fix!")

Set up a workflow automation to send you an alert when a row changes and your helper date column = "Invalid Date Value - Please fix!"

Jeff Reisman

@Simon Bamford

You just need to make this an IF, based on if the month is before July (for the 6 month add) or before October (for the 3 month add.)

For 6 months:

=IF(MONTH([Date (Hidden)]5) < 7, DATE(YEAR([Date (Hidden)]5), MONTH([Date (Hidden)]5) + 6, DAY([Date (Hidden)]5)), DATE(YEAR([Date (Hidden)]5) + 1, MONTH([Date (Hidden)]5) - 6, DAY([Date (Hidden)]5)))

In English, if the month of the [Date (Hidden)]5 value is less than 7 (July), build the date in this year with the month 6 months ahead, otherwise, build the date in next year, with the month minus 6 (ex. August - 6 = Feb, October - 6 = April).

For 3 months:

=IF(MONTH([Date (Hidden)]5) < 10, DATE(YEAR([Date (Hidden)]5), MONTH([Date (Hidden)]5) + 3, DAY([Date (Hidden)]5)), DATE(YEAR([Date (Hidden)]5) + 1, MONTH([Date (Hidden)]5) - 9, DAY([Date (Hidden)]5)))

In English, if the month of the [Date (Hidden)]5 value is less than 10 (Oct), build the date in this year with the month 3 months ahead, otherwise, build the date in next year, with the month minus 9 (ex. Oct - 9 = January, December - 9 = March).

Simon Bamford

Thank you. That has helped me big time. And really appreciate the plain English translation. Helps me understand it better.

Jeff Reisman

@Simon Bamford Happy to help, Simon.

The one wrinkle you may run into is when the day value is a day that doesn't exist in the month for the date you're creating. For example, if the date you want is 3 months after November 30th, you end up with February 30th, which isn't a thing. The date value of 2/30/23 will save, but if you try to run any calculations or automations based on that date value, they will fail.

With a helper date-type column, you could put some automation in place that will alert you when an invalid date value is entered, so that you can manually fix it. Use a formula in the helper column such as:

=IFERROR([6 Month Date column]@row - [Date (Hidden)]@row, "Invalid Date Value - Please fix!")

Set up a workflow automation to send you an alert when a row changes and your helper date column = "Invalid Date Value - Please fix!"

Simon Bamford

Hi Jeff,

I was testing the dates out and it seems Smartsheet is smart enough to realise that the dates are wrong and just moves the days to the next month.

This should have said 31/02/23 but moves it to 03/03/23.

Jeff Reisman

@Simon Bamford

Cool! It must be the DATE function that does that. I had thought Smartsheet did that automatically, so I tested just putting 2/30/23 into a date field manually and it didn't fix it. So it's got to be built into the DATE function. Good to know.