Months Remaining Formula

Scotty Bob

Trying to come up with a formula to calculate the duration of contract between 2 dates regardless of todays date with a result in months. Then, how do I get the remaining months on the contract from todays date - in months - over multiple years - and if the start date has not began yet.

Paul McGuinness

Hi @Scotty Bob

Think these may work for you

Terms in month -

=SUM(12 * (YEAR([End date]@row) - YEAR([Start date]@row)), MONTH([End date]@row) - MONTH([Start date]@row))

Months remaining, just substitute your start date for todays date -

=SUM(12 * (YEAR([End date]@row) - YEAR(TODAY())), MONTH([End date]@row) - MONTH(TODAY()))

Hope that helps,

Thanks

Paul

Paul McGuinness

Hi @Scotty Bob

Think these may work for you

Terms in month -

=SUM(12 * (YEAR([End date]@row) - YEAR([Start date]@row)), MONTH([End date]@row) - MONTH([Start date]@row))

Months remaining, just substitute your start date for todays date -

=SUM(12 * (YEAR([End date]@row) - YEAR(TODAY())), MONTH([End date]@row) - MONTH(TODAY()))

Hope that helps,

Thanks

Paul

Scotty Bob

Thank you Paul!

Scotty Bob

I'm using the formula below to calculate the Terms in Months - based on the Start and End dates columns. Based on the results I see - it doesnt look like it is calculating the full months. Almost as if it is one month less.

=SUM(12 * (YEAR([End Date]@row) - YEAR([Start Date]@row)), MONTH([End Date]@row) - MONTH([Start Date]@row))

Genevieve P.

Hi @Scotty Bob

Try thinking of the months simply as numbers to see what you'll calculate:

June (6) - July (7) = -1 *then adding 12 for each year difference

December (12) - January (1) = 11 *then adding 12 for each year difference

June (6) - June (6) = 0 *then adding 12 for each year difference

If you want to include the end month as an additional month (so June - June = 1, instead of 0) then you can +1 to the end of your formula:

=SUM(12 * (YEAR([End Date]@row) - YEAR([Start Date]@row)), MONTH([End Date]@row) - MONTH([Start Date]@row) + 1)

Cheers,

Genevieve