How do I convert this to the number of months instead of days?

jcepon · 01/04/22

How do I convert this number of days calculated between to dates into months?

=NETDAYS([Contract Effective Date]@row, [Contract Expiration Date]@row)

Maaik Meijerink · 01/04/22

You could use:

=VALUE(YEAR([Contract Expiration Date]@row) + RIGHT("00" + MONTH([Contract Expiration Date]@row); 2)) - VALUE(YEAR([Contract Effective Date]@row) + RIGHT("00" + MONTH([Contract Effective Date]@row); 2))

However: be aware for the transition of years: for example the diff between dec-21 and jan-22 is of course 89 and not 1 with this formula. Of course the formula must be modified taken into account the transition of years, something with MOD(....;12) when Years differ. If anyone has an idea ???

Dale Murphy · 01/04/22

@jcepon Why don't you extract the month from each date and compare them?

MONTH([Contract Effective Date]@row) - MONTH([Contract Expiration Date]@row))

... or the other way 'round.

dm

Maaik Meijerink · 01/04/22

Hello @jcepon,

You could use:

=VALUE(YEAR([Contract Expiration Date]@row) + RIGHT("00" + MONTH([Contract Expiration Date]@row); 2)) - VALUE(YEAR([Contract Effective Date]@row) + RIGHT("00" + MONTH([Contract Effective Date]@row); 2))

However: be aware for the transition of years: for example the diff between dec-21 and jan-22 is of course 89 and not 1 with this formula. Of course the formula must be modified taken into account the transition of years, something with MOD(....;12) when Years differ. If anyone has an idea ???

Maaik Meijerink · 01/04/22

https://community.smartsheet.com/discussion/comment/314783#Comment_314783

Updated my answer with the Year issue solve:

=(12 * YEAR([Contract Expiration Date]@row) + MONTH([Contract Expiration Date]@row)) - (12 * YEAR([Contract Effective Date]@row) + MONTH([Contract Effective Date]@row))

jcepon · 01/04/22

I appreciate it your support so much, that worked the best!!!! Its nice to rely on good team work!

How do I convert this to the number of months instead of days?

Best Answer

Answers

Help Article Resources

Categories