11

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!

 

Functionality

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.

 

 

 

In reply to by L@123

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

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

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

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