Number of Months from....

[StuartM1]

Hi folks,

I do NOT have the DATEDIF function on my Excel. I'm not sure why. Anyway, how can I count the number of months as follows?

2016/11 vs. 2017/03 = 4

Any ideas??

Thanks very much!!!!!

 

[Amanda Wilso]

Use the the YEAR function and the MONTH function.
Example (using format:dd/mm/yyyy):
Suppose you have the date 25/09/2007 in cell A1 and the date 31/03/2008 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:
=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)
This should give a result of 6.

 

[63falcondude]

DATEDIF is a hidden function.

Try =DATEDIF(A1,B1,"M")

where A1 = 11/1/2016 and B1 = 3/1/2017

Make sure that A1 and B1 are formatted as dates.

 

[StuartM1]

Thank you both!

My only avail format is YYYY/MM. There is no DAY value. Just year and month.

I do not have DATEDIF function available. Can I ask what you mean by Hidden function?

 

[63falcondude]

Thank you both!

My only avail format is YYYY/MM. There is no DAY value. Just year and month.

I do not have DATEDIF function available. Can I ask what you mean by Hidden function?

That's fine if your dates are formatted as YYYY/MM as long as they are seen as dates and not as text.

If they are text values, the formula will become more complicated if you refuse to change them to Excel recognized dates.

Why do you think that you do not have the DATEDIF function? It has been around since Excel 2000.

See this for more info on the function: Meet DATEDIF(), Excel’s secret Date & Time function that's still handy | PCWorld

Formula to use if they are text values: =DATEDIF(DATEVALUE(A1&"/1"),DATEVALUE(B1&"/1"),"M")

 

[StuartM1]

Thank you! Right. That's the issue. I do not have DATEDIF.

I do NOT have =DateDif()

And YYYY/MM is not an available Date Format.

Any ideas???

Thanks in advance.

 

[63falcondude]

Thank you! Right. That's the issue. I do not have DATEDIF.

I do NOT have =DateDif()

And YYYY/MM is not an available Date Format.

Any ideas???

Thanks in advance.

What version of Excel are you using? If it is Excel 2000 or newer, you DO have the DATEDIF function.

If you really don't want to use it, you can use this:

=ROUND((DATEVALUE(B1&"/1")-DATEVALUE(A1&"/1"))/30,0)

Where A1 is 2016/10 and B1 is 2017/3

 

[StuartM1]

Awesome thanks. I will do that.
I have Office/Excel 2013 but no DateDif is available.
Thanks again very much.

 

[63falcondude]

Awesome thanks. I will do that.
I have Office/Excel 2013 but no DateDif is available.
Thanks again very much.

I'm not going to argue whether or not you have the DATEDIF function.

Just know that either of these will work if you choose to use them:

=DATEDIF(DATEVALUE(A1&"/1"),DATEVALUE(B1&"/1"),"M")

=ROUND((DATEVALUE(B1&"/1")-DATEVALUE(A1&"/1"))/30,0)

You're welcome.

 

[snjpverma]

You will not see the date datedif function or its arguments but you will get the results if you use the formula properly.