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.
Future Date Calculation
Hello,
I am trying to find a formula that will calculate out 90 days from a given date AND give the result as the last day of the month.
The given date is Completion Date Estimate i.e. 10/31/2017 of a project and I would like a cost cut off date that is 90 days later on the last day of the month i.e. 01/31/2018.
One formula I have is =[Completion Date Estimate]ROW# + 90. This only gives me a count of 90 days later. i.e 10/31/2017 to 01/29/2018
The next formula I have is =IF((MONTH([Completion Date Estimate]ROW#) + 3) < 1, DATE(YEAR([Completion Date Estimate]ROW#) - 1, MONTH([Completion Date Estimate]ROW#) + 3 + 12, DAY([Completion Date Estimate]ROW#)), DATE(YEAR([Completion Date Estimate]ROW#), MONTH([Completion Date Estimate]ROW#) + 3, DAY([Completion Date Estimate]ROW#))). This formula works great, except if the completion date falls in Oct, Nov, or Dec. Then the month count does not know to go from 12 to 1 and an error of #INVALID VALUE is given.
Does anyone have any suggestions?
Comments
-
Hello Shannon,
Thanks for posting. This was a fun one to create.
=DATE(YEAR(Date1 + 118), MONTH(Date1 + 118), 1) - 1
Essentially, this formula will produce the year and month from 118 days from your start date which will force it to be in the fourth month and not the third month (90 days). Then is creates a date that is the 1st of the fourth month. Then it takes the date and subtracts 1 day which will then be the last day of the third month.
The reason for this is because each month starts on the 1st but doesn't always go 31 days. If we created a larger formula that accounts for 28, 29, 30, or 31 days to be the last of certain months it would be a very large formula. This way we can find the first of the 4th month and subtract 1 day.
Let me know if you have any questions on this.
-
Hi Taylor,
Thank you very much for taking the time to help me with this. It works perfect!
Cheers!
Shannon
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 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
- 289 Events
- 33 Webinars
- 7.3K Forum Archives