Convert months to years

handmadetsunami

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!

 

L_123

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.

 

 

 

Paul Newcome

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.

handmadetsunami

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))))

handmadetsunami

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

Paul Newcome

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

handmadetsunami

Thanks for your help!

Works perfectly!

handmadetsunami

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

Paul Newcome

My apologies. That's one date I didn't test. Standby.

Paul Newcome

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

handmadetsunami

Thank you very much

Paul Newcome

Happy to help!