Convert months to years
Hi,
I have a sheet that currently takes a start date and adds a number of years from another column to create an end date:
=IF(OR(ISBLANK([Contract Start Date]60)), "", IF(OR(ISBLANK([Contract Period (Years)]60)), "", DATE(YEAR([Contract Start Date]60) + [Contract Period (Years)]60, MONTH([Contract Start Date]60 - 1), DAY([Contract Start Date]60 - 1))))
This works great for adding whole years. However, I need to change the column to Contract Length in Months (most are over 12 months and quite often 36/48/60/72 months but can be anything from 1 month.)
I can't work out how to get a formula to either take the number of months, convert it to years and then add it to the start date to get an end date or add the number of months to the start date in another way (that I haven't thought of)
Does anyone have any ideas?
Thanks in advance!
Comments
-
all you need to do is move the + part of the years to the month part of the date statement. Smartsheet should auto calculate the change.
-
Are you able to provide some screenshots? I have an idea of how to make this work but want to be sure I understand exactly what you are trying to accomplish and what exactly you are working with.
-
Thanks.
I have attached two screenshots. Once shows what currently happens - the one with Contract Period (Years) and the second shows what I am looking to be able to use Contract Period (Months).
Currently, you put in a start date and the contract length in years and it auto generates the contract end date. I would like to be able to do the same but for months instead of years
Hope these help to show you what I am trying to do.
Just realised my code is using Row 60 as I took it from another sheet:
=IF(OR(ISBLANK([Contract Start Date]1)), "", IF(OR(ISBLANK([Contract Period (Years)]1)), "", DATE(YEAR([Contract Start Date]1) + [Contract Period (Years)]1, MONTH([Contract Start Date]1 - 1), DAY([Contract Start Date]1 - 1))))
-
Thanks. I just tried this but it only works for numbers up to 12 months and even then it doesn't always select the previous day which is what my formula currently does
-
Give this a whirl...
=DATE(YEAR([Contract Start Date]@row) + (IF(([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12) + [Contract Period (Months)]@row > 12, (INT([Contract Period (Months)]@row / 12)) + 1, (INT([Contract Period (Months)]@row / 12)))), MONTH([Contract Start Date]@row) + (IF(([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12) + MONTH([Contract Start Date]@row) > 12, 0 - (MONTH([Contract Start Date]@row) + (12 - (MONTH([Contract Start Date]@row) + ([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12)))), ([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12)))) - 1
-
Thanks for your help!
Works perfectly!
-
Actually, if the start date is 01/01/2018 and you do 24 months it outputs 31/12/2020 not 31/12/2019. Unless I have done my maths wrong its 1 year out
-
My apologies. That's one date I didn't test. Standby.
-
Ok. Give this one a try. Hopefully it'll work out for you. I tested the 1st, 15, and last day of every month across 2019 and got a good result. Let me know...
=DATE(IF(MONTH([Contract Start Date]@row) + ([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12) > 12, (YEAR([Contract Start Date]@row) + (INT([Contract Period (Months)]@row / 12))) + 1, YEAR([Contract Start Date]@row) + (INT([Contract Period (Months)]@row / 12))), IF(MONTH([Contract Start Date]@row) + ([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12) > 12, (MONTH([Contract Start Date]@row) + ([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12)) - 12, MONTH([Contract Start Date]@row) + ([Contract Period (Months)]@row - (INT([Contract Period (Months)]@row / 12)) * 12)), DAY([Contract Start Date]@row)) - 1
-
Thank you very much
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!