Formula to figure out months between two dates

  • Thread starter Thread starter Kimbe
  • Start date Start date
K

Kimbe

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
 
You can use TTC to automatically convert those strings to XL recognizable
dates.

Select the column of dates, then:

<Data> <TextToColumns> <Next> <Next>,

Under "Column Data Format", click on "Date", and make sure MDY shows in the
window.

Then <Finish>
 
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
Click to expand...

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
 
Back
Top