Find the number of months between today and a specific date in Excel

0
votes

A simple way to find the number of months between two dates is =DATEDIF(DATE1;DATE2;"m"), where DATE2 must be greater than DATE1.

What I'm trying to achieve is to find the number of months between TODAY and a future date. I would expect =DATEDIF(MONTH(TODAY());DATE2;"m") to work, but this returns a strange result (see example).

=DATEDIF(TODAY();DATE2;"m") or =DATEDIF(NOW();DATE2;"m") won't work, since the number of months that will return between, say, November 20th and December 12th is zero.

Thank you.The result should be 4, but it returns an inconsistent value

2
excel
To me =DATEDIF(TODAY(),DATE2,"m") returns 1. What exactly do you expect otherwise? Can you elaborate. Also, your first result isn't strange at all. MONTH(TODAY()) returns "11", which for Excel as a date would be 11th of jan 1900. There are 1453 months between that date and mar-21 apparently. – JvdV
find the number of days and divide by 30? – Solar Mike
Not really: =DATEDIF(TODAY();"01/12/2020";"m") returns "0". Thank you for clarifying what I considered an inconsistent result for =DATEDIF(MONTH(TODAY());DATE2;"m"). Counting the number of days and dividing it by 30 will not always work (February). – Carlos Ticó
So, if you get 0.86 based on 01.02.20 and 27.02.20 giving 0.86666667 then round as needed you can get 1... just thinking around the situation :) – Solar Mike
What is your rule for that calculation? It seems to me that the number of months between November 20 and December 12 IS zero. Certainly there is not a full month-- only 22 days. – Ron Rosenfeld

2 Answers

0
votes

Does this formula work for you? If not, please post a list of dates along with your expected result.

=IF(DAY(A1)>DAY(TODAY()),MONTH(TODAY())-MONTH(A1)-1,MONTH(TODAY())-MONTH(A1))

enter image description here

0
votes

Maybe try:

=DATEDIF(TODAY(),EOMONTH(DATE2,0),"m")