Calculate the EndDate across Years
Answers
-
I want to do something very similar for months versus weeks and see the duration field does not allow months. Can you help?
I have a product sold based on subscription. Subscriptions always have an AcceptanceDateDate and a Term(months) user inputs. I want to auto-calculate the StartDate and EndDate of the subscription. Term is always input as a variable number of months.
The Start Date must be the first day of the next month following acceptance. An acceptance date of 15 Feb 2021 needs to auto-calculate a StartDate of 1 Mar 2021. An acceptance date of 2 Dec 2021, needs to auto-calculate a StartDate of 1 Jan 2022, and so on. This part, I have figured out elegantly, but I'm struggling with the EndDate.
The EndDate must be one day less than the StartDate plus the term. In other words, the EndDate will always end on the last day of the preceding month. Using the above examples, an 18 month term would yield EndDates as follows:
StartDate = 1 Mar 2021 => EndDate = 31 Aug 2022
StartDate = 1 Jan 2022 => EndDate = 30 Jun 2023
To calculate the EndDate, I currently have the following:
DATE(YEAR(AcceptanceDate@row), MONTH(AcceptanceDate@row) + [SubscriptionTerm(months)]@row, DAY(AcceptanceDate@row) - 1)
This works great except when I roll into the next year and return #INVALID VALUE.
Any help is greatly appreciated, the only other solution is 1) Force the EndDate to be user input, or 2) create a very ugly imbedded IF(OR statement.
-
Give the following a try...
First, the start date has to be precise. Use the formula below to calculate the Start Date.
=IFERROR(IF(MONTH(AcceptanceDate@row) < 12, DATE(YEAR(AcceptanceDate@row), MONTH(AcceptanceDate@row) + 1, 1) - 1, DATE(YEAR(AcceptanceDate@row), 12, 31)) + 1, "")
For easier reading...
1 IFERROR( IF(MONTH(AcceptanceDate@row) < 12 2 , DATE(YEAR(AcceptanceDate@row), MONTH(AcceptanceDate@row) + 1, 1) - 1 3 , DATE(YEAR(AcceptanceDate@row), 12, 31) ) + 1 4 , "" )
Then use the following formula to calculate the End Date.
=IFERROR(DATE((YEAR(StartDate@row) + (INT([Term in Months]@row / 12))), IF((MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) > 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) - 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12))), 1) - 1, "")
Formatted for easier reading.
1 IFERROR( DATE( (YEAR(StartDate@row) + (INT([Term in Months]@row / 12))) 2 ,IF((MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) > 12 3 ,(MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) - 12 4 ,(MONTH(StartDate@row) + MOD([Term in Months]@row, 12))) 5 ,1) - 1 6 , "")
-
I am using this formula you posted earlier:
=IFERROR(DATE((YEAR(StartDate@row) + (INT([Term in Months]@row / 12))), IF((MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) > 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) - 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12))), 1) - 1, "")
But am trying to modify slightly. I am trying to calculate the end date of a project based on the start date and number of months that I input in my sheet. So for example, my start date column will read 5/15/23, my duration column will show a number of 6 for the amount of months. I want to then have the formula in the finish date column calculate based on these two data points. Is there a way to easily modify your formula above to calculate this specific date?
Help Article Resources
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
Check out the Formula Handbook template!