Convert months to years

handmadetsunami
handmadetsunami ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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!

 

Tags:

Comments

  • L_123
    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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • handmadetsunami
    handmadetsunami ✭✭✭✭
    edited 03/15/19

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

    Current.PNG

    Proposed.PNG

  • handmadetsunami
    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
    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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • handmadetsunami
    handmadetsunami ✭✭✭✭

    Thanks for your help!

    Works perfectly!

  • handmadetsunami
    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
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • handmadetsunami
    handmadetsunami ✭✭✭✭

    Thank you very much :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!