Formula for Adding Months
I am having a hard time with finding a formula for adding months, rather than days. I need to be able to input a date in one date column, and have it calculate 10 months out in another date column.
Best Answer
-
Hi @Natasha W.
Returning to community, I had the same for 6 months in my early Smartsheet day's and was assisted, for you I changed the 6 to a 10.. This accounts for leap years too
Save to the Date Cell you want the result in and convert to column formula to capture all row entries as they come in, where there is no date in the [Date Column] the result cell remains blank.
=IF(ISBLANK([Date Column]@row), "", DATE(YEAR([Date Column]@row) + FLOOR((MONTH([Date Column]@row) + 10 - 1) / 12, 1), MOD(MONTH([Date Column]@row) + 10 - 1, 12) + 1, DAY([Date Column]@row)))
Good luck.
Cheers.
Answers
-
@Natasha W.
Try this:
=DATE(YEAR([Original Date Column Name]@row), MONTH([Original Date Column Name]@row) + 10, DAY([Original Date Column Name]@row)) -
If it's never going to be anything other than 10 months, you can use the below. If 10 becomes a variable it would need some changes. Note this also takes the exact day so if you add 10 months to a date that the day is 31 but the 10th month stops at 28, it might have an error so you'd need to modify the day portion to catch that.
=DATE(IF(MONTH([Date1]@row) + 10 > 12, YEAR([Date1]@row) + 1, YEAR([Date1]@row)), IF(MONTH([Date1]@row) + 10 > 12, MONTH([Date1]@row) + 10 - 12, MONTH([Date1]@row) + 10), DAY([Date1]@row))
Certified Platinum Partner
-
Hi @Natasha W.
Returning to community, I had the same for 6 months in my early Smartsheet day's and was assisted, for you I changed the 6 to a 10.. This accounts for leap years too
Save to the Date Cell you want the result in and convert to column formula to capture all row entries as they come in, where there is no date in the [Date Column] the result cell remains blank.
=IF(ISBLANK([Date Column]@row), "", DATE(YEAR([Date Column]@row) + FLOOR((MONTH([Date Column]@row) + 10 - 1) / 12, 1), MOD(MONTH([Date Column]@row) + 10 - 1, 12) + 1, DAY([Date Column]@row)))
Good luck.
Cheers.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!