Convert three letter month abbreviation to number?

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)

4 Answers

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")

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.

For me all the solutions proposed throw an error:

But to be fair you can just do this:

=IFS(LOWER(A1)="jan",1,LOWER(A1)="feb",2,LOWER(A1)="mar",3,LOWER(A1)="apr",4,LOWER(A1)="may",5,LOWER(A1)="jun",6,LOWER(A1)="jul",7,LOWER(A1)="aug",8,LOWER(A1)="sep",9,LOWER(A1)="oct",10,LOWER(A1)="nov",11,LOWER(A1)="dec",12)

OR:

=(FIND(LOWER(A1),"janfebmaraprmayjunjulaugsepoctnovdec")-1)/3+1

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.