# 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.

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.

``` Related Posts Count Sundays between two dates Adding years or months to a date in Excel Percentage of the Year in Excel Custom Function for Age in Excel MIN and MAX and Dates in Excel ```

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

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

## 62 thoughts on “Excel – Convert Text Month to the Month Number”

1. Bracket in formula is in the wrong spot, worked a treat once corrected though ðŸ™‚

2. neale_blackwood says:

Thanks for spotting that – fixed.

3. Rock says:

when number should come its gettin converted into date. i need 2.86 , its showing feb-86.Please help!

• anon says:

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

4. neale_blackwood says:

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.

5. Sandeep says:

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

• Hi Sandeep

Assuming A1 has the month

=TEXT(MONTH(1&LEFT(A1,3)),”00″)

should work. It will return text.

Regards

Neale

• Rick Rothstein says:

Assuming the text after the first 3 characters are correct for the month name, you could also use this shorter formula…

=TEXT(1&A1,”mm”)

6. Alireza says:

Hi,

Assuming A1 has the month

=MONTH(A1&1)

It returns a number.

• Alireza says:

Also, it works for any month text (at least three characters) e.g. Jan, feb, March, …

7. Your formula converts “Mar” into “J”…

• Not sure why – unless you have another format on the cell – try removing all formats and see what it gives you.

8. gemma says:

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?

• The & symbol joins text together – the formula creates a text string that Excel can treat as a date.

• You can use & in all versions of Excel but you must use it in a formula with the = or + in front eg =1&”Jan” not 1&”Jan”

9. Anon says:

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.

• Good to see it can work with Power BI as well. Thanks for letting me know.

10. Dana says:

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!

• Hi Dana

To convert that layout in A1 into a date you would use a formula like

=DATE(RIGHT(A1,4),MONTH(1&LEFT(A1,3)),MID(A1,5,SEARCH(“,”,A1)-5))

Hope that helps.
Regards
Neale

11. Yana says:

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

• Hi Yana

If A1 contains June then this formula should work for year = 2016 and day = 22

=DATE(2016,MONTH(1&A1),22)

Hope that helps.
Regards
Neale

• Yana says:

Thank you very much. ðŸ˜€

• Yana says:

Thank you very much. ðŸ˜€

• Yana says:

Thank you very much. ðŸ˜€

12. Jackie says:

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

• Jackie says:

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.

13. 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.

• Hi
Use the EOMONTH function around the function in the article. Something like

=EOMONTH(DATE(2017,MONTH(A1&1),1),0)

This has the year hardkeyed in.

14. Mike says:

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

15. Ananya Mishra says:

how to get last day of month if only month is given like 1 or JAN or January ??
any formula??

• The EOMONTH function returns the end of the month.

=EOMONTH(A1,0)

returns the end of the month for the date in A1.

16. Hung Tran says:

17. The above formula does not work. The function returns the letter j no mattter what month you put in.

• Hi Thomas

The MONTH function returns a number – not sure how it could return a J – if you want to email me the file I can have a look.

18. amani says:

06 Jul 2019
how to convert in number number

• 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

19. Petru says:

Hi, I have a month name that I want to convert to a number. It works if the month name is in English, with the examples from this topic. But what if the name is in another language, how can I make excel know that the text is a month?
Thanks.

• Sorry that is one question I will have to pass on – I have never worked with other languages in Excel.

• shoukat ali says:

“dear sir,
i am a accountant in government institue.creating salary bills my job.
please send me a excel sheet with formula which we give him amount one month ,sheet should automatic gernate salary betweent to diffrent period and add increment decenber .for example,
salary bill
period 1-oct-2019 to 31-dec-2019.
amount / month.1200=calculate amount oct to nov 2019 ,i want show ruslt with formula.rate/month with add increment amount december.1200+400 =show ruslt with formula…=g.total

20. David Staples says:

Hi,

I’m working on a project, I would like to know how to parse the text in D1, i.e.:
JanuaryÂ 3,Â 1993
into a usable date that can be exported to a CSV file for Google Calendar.
Specifically, 1993-01-03.
YYYY-MM-DD
Thanks.

• HI David
Assuming the date is in cell A1 this formula should work
=DATE(RIGHT(A1,4),MONTH(1&LEFT(A1,3)),MID(A1,SEARCH(“,”,A1)-2,2))
Will send a file example via email.
Regards
Neale

• manu says:

Hi,
I have a query. I have CSV file which has date 20200401 format , I want convert this excel format, or actual month in text , please help me

• Hi
Honestly if you are working with CSV files Power Query is the best way to import – use “Get & Transform” options in the Data ribbon.

To convert that date layout via a formula try this – assumes cell A1 has the date

=DATE(LEFT(A1,4),MID(A1,3,2),RIGHT(A1,2))

This assumes the structure of 20200401 is YYYYMMDD

Hope that helps

• Rick Rothstein says:

If the structure is YYYYMMDD then you can also use this Excel formula…

=TEXT(A1,”0000-00-00″)+0

• Thanks Rick – cool hack – the + 0 converts the text date into a real date.

21. Greetings
2014-05
I want to get month name from above string e.g. May from 2014-05

• If it was in A1 then use

=TEXT(1&RIGHT(A1,3)&”-21″,”mmm”)

• Rick Rothstein says:

This formula should also work…

=TEXT(A1&”-01″,”mmm”)

22. Thanks for sharing such an informative post. Hope you will share some more information about Excel. Cheers!!!

• Thanks – there are lots of Excel content on this website.

23. Andy says:

Brilliantly simple – it was putting the 1 in front that I had not tried and fixed it.

24. Tom says:

hi, I would like to convert day/date/year and time (5/5/2023 5:28:00 PM) to Month and Year (May 2023).

thank you

• Hi Tom
If you are trying to remove the time use the TRUNC function on the date. And then format it.
Sorry by using 5/5/23 as an example not sure which is the day and the month as May is the 5th month.
Regards
Neale