Calculate difference in months between two dates

[goodmachine]

Hello,

I want to calculate the difference in months between two dates. For example, in cell B2, I have 12/31/2014. In cell A2, I have A2, I have 5/3/2014. The days of the month will vary, so this may be messy.

What I'm looking for is a calculation that will tell me that there would be 7 months between the two dates on the based on the months. I would like to not consider the days of the month. Is that possible.

If yes, the second part of this formula would multiply a value in cell C2 (11 in this example). Roughly what I'm looking for is something that would result in:

(B2-A2)*C2 = 77.

Thanks in advance for your help.

 

[gorem]

I use the two methods described in this link all the time, and they work great:

How to calculate the number of months between two dates in Excel

 

[vogel997]

[TABLE="width: 260"]
<colgroup><col width="347" style="width: 260pt; mso-width-source: userset; mso-width-alt: 12690;"> <tbody>[TR]
[TD="width: 347, bgcolor: transparent"]Try[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]=MONTH(B2)-MONTH(A2)[/TD]
[/TR]
</tbody>[/TABLE]

 

[gorem]

[TABLE="width: 260"]
<tbody>[TR]
[TD="width: 347, bgcolor: transparent"]Try[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]=MONTH(B2)-MONTH(A2)[/TD]
[/TR]
</tbody>[/TABLE]

This will only work if the two months are in the same year

 

[AhoyNC]

You could also use the DATEDIF function.
See this link on the function.
https://www.youtube.com/watch?v=F9dz30QkL7g

Excel Workbook
	A	B	C	D
1
2	12/31/2014	5/3/2014	11	77
Sheet

 

[vogel997]

[TABLE="width: 260"]
<tbody>[TR]
[TD="width: 347, bgcolor: transparent"]Try
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]=MONTH(B2)-MONTH(A2)
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 862"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="764" style="width: 573pt; mso-width-source: userset; mso-width-alt: 27940;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 129, bgcolor: transparent"][/TD]
[TD="width: 764, bgcolor: transparent"]Try[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3-May[/TD]
[TD="bgcolor: transparent, align: right"]31-Dec[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent"]=(MONTH(B2)-MONTH(A2))*C2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]21-May[/TD]
[TD="bgcolor: transparent, align: right"]8-Feb[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent"]=IF(MONTH(B3)-MONTH(A3)<0,MONTH(B3)-MONTH(A3)+12,MONTH(B3)-MONTH(A3))*C3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[goodmachine]

[TABLE="width: 862"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 129, bgcolor: transparent"][/TD]
[TD="width: 764, bgcolor: transparent"]Try[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3-May[/TD]
[TD="bgcolor: transparent, align: right"]31-Dec[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent"]=(MONTH(B2)-MONTH(A2))*C2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]21-May[/TD]
[TD="bgcolor: transparent, align: right"]8-Feb[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent"]=IF(MONTH(B3)-MONTH(A3)<0,MONTH(B3)-MONTH(A3)+12,MONTH(B3)-MONTH(A3))*C3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Thank you Vogel. Your solution worked. And thanks to everybody else for contributing. Much appreciated.

 

[goodmachine]

Gorem is correct, Vogel's solution only works if the year is the same. Is there a way for this formula to consider the year in it's calculation as well?

 

[goodmachine]

I tried AhoyNC's solution and the formula returned a Number Error.

 

[vds1]

Try,

=DATEDIF(A2,B2,"m")*C2