Search form

Convert a Month name into a number

This lesson shows you a formula to convert a month name into its corresponding number (i.e. Jan = 1, Feb =2, etc).

There are two common scenarios where this can be useful:

  • You have a column of month values entered as text, and you want to sort the list by date order rather than by alphabetically by month name:
    Excel list of months sorted by date order rather than alphabetically
  • You have imported date values from another system. These dates have been imported as text, and you need to convert them from text to dates. The imported dates include the month name rather than the month number, and the MONTH function doesn't know how to convert text to a number, such as Sep and April in the following examples:
    • Sep 11 2015 1:50PM
    • April 1, 2010 3:02:43 PM EDT

This simple formula will convert these text values into their corresponding numbers:

=MONTH(A1&" 1") (assuming our date has been entered into A1)

Here's how this formula works:

  • The MONTH function tells you the month number for a given date. It is commonly used to calculate which month a date represents, e.g.
    • April 19, 2014 is stored by Excel as 42113
    • =MONTH(42113) returns 4
    • The number, 42113 is how Excel stores April 19, 2014 as a date.
    • You could also write =MONTH(A1) where A1 contains the date you want to convert.
  • In our example, the date has been provided as text. Excel doesn't recognise it as a valid date, so the MONTH doesn't work.
  • However, if we concatenate our date text with " 1", we can fool Excel into thinking it's looking at a valid date, so the MONTH function returns the correct value:
    • =MONTH("Sep 1") > returns 9
    • =MONTH("April 1") > returns 4

Let's return to our challenge of sorting our months by date order rather than alphabetically. To solve this problem you can:

  • Add a new column to your spreadsheet which contains this formula
  • Sort your data based on that column rather than on the Date column.
    Excel column of data with months, plus a second sort column.

If you have a more complex scenario, such as the date examples shown above, check out our lesson on converting text values to dates.

If you have any questions about this lesson, or want to ask about a specific scenario that has you stumped, please add a comment below.

Want to learn more? Try these lessons:

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.

Add a comment to this lesson

Add comment