I have an excel spreadsheet that I download from a vendor that has the dates
listed as MAY/17/2010 - JUL/31/2011 and I am not able to use my existing
formula to figure out the number of months between two dates.

I was using =(YEAR(cell for end date)-YEAR(cell for start
date))*12+MONTH(cell for end date)-MONTH(cell for start date) which was
working great but now since the vendor changed their date format it won't
work. I have even tried to format those cells to yyyy-mm-dd but they stay as
they are above.

Any help would be greatly appreciated because I don't want to have to retype
all the dates each time to get the month formula to work.

Thank you in advance.

Kim

Hi,

Try this

=datedif(A2,B2,"m")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kimbe" wrote in message
...
I have an excel spreadsheet that I download from a vendor that has the
dates
listed as MAY/17/2010 - JUL/31/2011 and I am not able to use my existing
formula to figure out the number of months between two dates.

I was using =(YEAR(cell for end date)-YEAR(cell for start
date))*12+MONTH(cell for end date)-MONTH(cell for start date) which was
working great but now since the vendor changed their date format it won't
work. I have even tried to format those cells to yyyy-mm-dd but they stay
as
they are above.

Any help would be greatly appreciated because I don't want to have to
retype
all the dates each time to get the month formula to work.

Thank you in advance.

Kim

On May 17, 1:36*pm, Kimbe wrote:
I have an excel spreadsheet that I download from a vendor that has the dates
listed as MAY/17/2010 - JUL/31/2011 and I am not able to use my existing
formula to figure out the number of months between two dates.

I was using =(YEAR(cell for end date)-YEAR(cell for start
date))*12+MONTH(cell for end date)-MONTH(cell for start date) which was
working great but now since the vendor changed their date format it won't
work. *I have even tried to format those cells to yyyy-mm-dd but they stay as
they are above.

Any help would be greatly appreciated because I don't want to have to retype
all the dates each time to get the month formula to work.

Thank you in advance.

Kim

If your start date is in cell A1 and the end date is in cell B1, the
following formula should work in place of the one you were previously
using:

=DATEDIF(DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"/"," ",1),"/",",
")),DATEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"/"," ",1),"/",", ")),"m")

Hope this helps,
Jason