Formula to calculate months between today and future date

Platform discussions
1

Hi there

I’m trying to calculate number of months between today and a future date in a cell. I’m currently using this formula

INT(MOD(YEARFRAC(FORMAT_DATE({WOF Expiry}), FORMAT_DATE(TODAY())), 1) * 12) & " Months "

But for a future date of 30/10/2026 it’s showing me 5 months so it’s not taking the year into account.

How can I fix this formula please?

2

Hi @NVRensburg - make sure your future date is the second argument. Also, I think you can minimize the complexity:

YEARFRAC(TODAY(),{WOF Expiry})*12

Hope this helps,
Mark

3

Thank you! How can I round that number off instead of it showing 3.657?

4

Hi @NVRensburg - you can adjust the formula as follows:

ROUND(YEARFRAC(TODAY(),{WOF Expiry})*12,0)

1 Like
5

It seems the issue with your formula arises from how the YEARFRAC function works. It calculates the fractional difference in years between two dates, and you’re only considering the decimal part (the months) without accounting for the full year difference. To fix this, you’ll need to calculate both the year and month difference separately.

Here’s a revised formula that should work:

=(YEAR({WOF Expiry})-YEAR(TODAY()))*12 + (MONTH({WOF Expiry})-MONTH(TODAY())) & " Months"

This formula calculates the total difference in months by multiplying the difference in years by 12 and then adding the difference in months. This way, it correctly takes the year into account.

For more detailed information and an easy-to-use tool to calculate the number of months or days between two dates, you can visit my website at days from today

Let me know if you need further assistance!