Adding months to a cell linked date
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
Best Answers
-
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 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
-
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!
-
Thank you. That has helped me big time. And really appreciate the plain English translation. Helps me understand it better.
-
@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!
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!