Adding months to a cell linked date

Options

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

Tags:

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!"

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Simon Bamford
    Simon Bamford ✭✭✭✭
    Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!"

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Simon Bamford
    Simon Bamford ✭✭✭✭
    Options

    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
    Jeff Reisman ✭✭✭✭✭✭
    Options

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!