Convert three letter month abbreviation to number?

Question

Is there an excel function for converting a three letter month abbreviation (such as FEB, APR) to a number? I've been using a mapping table and then using v-lookup from there, but curious if there's a function or faster way.

Thanks!

edit to clarify: this is one column that has the three letter month abbreviation hardcoded in (i.e. it is not part of a longer date)

Answer 1

You can use MONTH() and create a pseudo date for it:

=MONTH(DATEVALUE(A1&" 1, 2017"))

---

Or another variation on the same theme:

=--TEXT(DATEVALUE(A1 & "1, 2017"),"m")

---

One more note: DATEVALUE can be replaced by -- in both formula.

=MONTH(--(A1&" 1, 2017"))

and

=--TEXT(--(A1 & "1, 2017"),"m")

Answer 2

Excel natively supports this using cell formatting. If you write a date, you can format the cell to show the month by 3 letters, but the value will actually be the month's number.

Answer 3

This is how to convert 01JAN1900 to 1/1/1900

• split the original date into 3 separate columns using the functions Left middle right.

  This should look this:

  

• Next link the 3 cells using =G2&"-"&H2&"-"&I2. This will put the dates in an excel format.

• Next copy the column and paste values.

• You can now right click on cell and format the date to desired format.