Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Add number of months onto a date
I have a sheet that summarizes all of our projects. I want to add the projected term of the project (in months) to the kick off date of the project. Here is what I came up with but it is not working:
DATE(MONTH([Kick Off]1) + [Project Term]1)
Is there a formula that I can reference the date on one cell and add...say "6 months" to that date?
Comments
-
Saylor,
Short answer: DATE takes three arguments. You have only one.
To clarify,
[Kick Off] is date type column.
[Project Term] is a text/number type column.
You want to add the [Project Term] (a number in months) to the [Kick Off] date and this will be displayed in another date type column.
=[Kick Off]1 + [Project Term]1 * 30
will get you close. That adds 30 days times the number of months to the Kick Off date.
It will be off the farther out you go (there are 91 days per quarter, not 90)
30.33 will get you closer.
This will also work when the Project Term results in a date next year.
If you are set on 6 months from now must be the same calendar date (2/17 + 6 months is 8/17), then you need to account for the year change.
The formula for the next 12 months would be:
=IF(MONTH([Kick Off]1) + [Project Term]1 > 12, DATE(YEAR([Kick Off]1) + 1, MONTH([Kick Off]1) + [Project Term]1 - 12, DAY([Kick Off]1)), DATE(YEAR([Kick Off]1), MONTH([Kick Off]1) + [Project Term]1, DAY([Kick Off]1)))
pseudo code
if the kick off month + project term is more than 12
then the date is next year
otherwise it is this year.
date is DATE(year,month,day)
If your project term's are longer than 12 months, there's another level of complexity to determine if you are going beyond more than one year.
Craig
-
thank you so much for the feedback. I am going to try this in the morning. I believe I know where to find the examples for the additional level of complexity "IF" coding if the projects go past 12 months.
Thanks again and have a great night.
-
You are welcome.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives