Formula adjustment to calculate date based on years duration
Hello,
I currently use the below formula to automatically give me a date if I enter the duration. Now, this currently only works if I enter it in months. Can someone help me adjust this so that it calculates based on years duration?
=IFERROR(DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)) = 12, -1), IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)), DAY([Start Date]@row)) - 1, 0)
Thanks!
Best Answer
-
If it is just years, you would use something along the lines of...
=DATE(YEAR([Start Date]@row) + [Duration (years)]@row, MONTH([{Start Date]@row), DAY([Start Date]@row))
Answers
-
Hope you are fine, you did a brilliant formula but you can do it in an easy way by converting the duration whatsoever it to days then use Start date + Duration ( Days ) to get the end date.
End Date =([Duration (Years)]@row * 365 + [Duration (Months)]@row * 30.4) + [Start Date]@row
Check the following screenshot
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Bassam.M Khalil,
Thank you for your suggestion. I would like to stick to a single duration column in this case, rather than having a column for years AND months. Can you help with the original formula?
Also, @Bassam.M Khalil , thank you, but I need to give credit where credit is due and it's formula by @Paul Newcome :)
-
Yes, it's a good idea, I think Paul will help you more than me because he is the creator of this brilliant formula.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Paul Newcome can you please help me with your original formula?
-
Do you need years and months, or just years?
-
Hi Paul,
It would be exactly the same layout as below, except for the duration (months) it will be Duration (years).
-
If it is just years, you would use something along the lines of...
=DATE(YEAR([Start Date]@row) + [Duration (years)]@row, MONTH([{Start Date]@row), DAY([Start Date]@row))
-
Thanks Paul,
that was way easier than I imagined!
-
Haha. We got lucky on that one. Happy to help. 👍️
-
I have set up the easiest way to do this due date, but I need the day to remain the same as the original state date. This is probably changing due to a leap year. How do I add that to the date. I have a start date as 1/19/21 and the end date is five years from the start date and should be 1/19/26. How do I add the leap year information?
-
@Tina Chamblee You shouldn't need to change anything from the above formula. The above formula pulls the MONTH() and DAY() from the original date and should only be adjusting the year.
Exactly what is the formula you are using?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!