Excel – Convert Text Month to the Month Number

A trick to get a number from a name

Ever needed to convert the text Sep or September into a 9? Well Excel can convert text months into their respective numbers.

If you enter 1Sep in a cell Excel will convert that into the date 1/9/13 (Excel uses the current year). You can use that functionality to convert the text of a month into the month number.

Assume A1 contains the word September. The following formula will convert that into the number 9.

=MONTH(1&LEFT(A1,3))

Using the & symbol joins the 1 to the first three characters of the cell or 1Sep. Excel recognises that as a date format and treats it like a date for the MONTH function to then extract the month number.

Month Number Formula

We could shorten this formula to

=MONTH(1&A1)

Because if you type 1September it also returns a date.

Note: in Australia we put the day before the month in our dates.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply to Jackie Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

41 thoughts on “Excel – Convert Text Month to the Month Number

    • It may be you wrote 20/7 which is either 20th July or 2.8571 or 2.86 in currency terms.
      If you write 20/7/14 it should work

  1. That’s unusual as the decimal point is not a date delimiter. I haven’t been able to replicate it, so I can’t offer a solution – sorry.

  2. I want the month name to covert to a two digit Month Number, can this be done via a single formular, excel does not include the leading zero.

    Month Name Month Number
    January 01
    February 02
    March 03
    April 04
    May 05
    June 06
    July 07
    August 08
    September 09
    October 10
    November 11
    December 12

  3. I don’t understand, you all use &1 or 1&, but excel 2010 won’t take it.
    Unless there is something I’m missing.
    What exactly is the & role in this?

  4. First – thanks for the post. Had to modify this slightly in PowerBI as it wouldn’t take 1Jan for some reason…it wanted 1-Jan. Simple adjustment to =Month(1&”-“&Left(Sheet[ColumnName],3)) made it work.

  5. Thanks Neal… this is great. Is there a way to only convert the written month, but keep other parts of the date? For example, I have a client that sends me dates in excel written as “Nov 21, 2014” in a single cell. The system we use requires that the format read “11/21/2014”. Thanks!

  6. Is there a formula wherein I can change the month name to the month number, but there is a date that is with the month number.
    Ex.
    June (once I change June to July it would only affect the 06 but not the other parts of the date)
    06/22/16

  7. Hello, I have a date that I need to convert. The data is being pulled in from a TXT Export which I then open with Excel using the Delimited Import function. The date appears as Jun 17 13, I have been trying to automatically convert it to 2013-06-17 without any luck. When I change the cell formatting to Date nothing happens. It will convert if I manually add a comma after the day (17). I have a long list of dates that need to be converted, I want to avoid having to add the comma manually to each line item. Is there a formula or something you can suggest to automatically convert the date from Jun 17 2013 to 2013-06-17?

    • Hi Jackie

      If the date is in cell A1 the following formula should work

      =DATE((RIGHT(A1,2)+2000),MONTH(1&LEFT(A1,3)),MID(A1,5,2)*1)

      This assumes recent dates eg after 2000. If it is birth dates if may be a little harder.

      Hope that helps.

      Neale

      • Thank you very much for the amazing formula.

        This formula converts a date like Jun 17 13 to 2013/06/17:
        =DATE((RIGHT(A1,2)+2000),MONTH(1&LEFT(A1,3)),MID(A1,5,2)*1)

        Thank you again.

  8. Hi,
    I have a query. I want to convert a text into last working day of the month. For example – “Oct” should be converted into 20171031 and another example “Nov” should be converted into 20171130. As I received the data in Text format only with three alphabets of the month. Please help me out.

  9. I have an imported file with date/time strings as text – Sep 19 2017 10:08AM and I am wanting to convert these to actual date values and time values so I can perform analysis. I have tried text to columns, datevalue, timevalue, and a few other things I found while searching for a solution. In a perfect world, I would like the Date to be 9/19/2017 and the time to be 10:08 (24 hour time).

    Thank you in advance for your help

    • If that entry is in cell A1 then multiplying it by 1 will convert it to a real date eg =A1*1 just format it to see the date

      If that entry was in A1 then these formulas extract Month and Day
      =MONTH(A1*1)
      =DAY(A1*1)

      Hope that helps