How to get Month Name and Number in 2 Easy Steps

by

In this tutorial, we will be covering how to use Excel’s built-in functions to convert months from their numerical representation (e.g., 1 for January) to their textual representation (e.g., “January”) and vice versa.

Extracting the month from a date.

In this example, we have a list of dates that we want to extract only the months from. There are two ways to do this: using the MONTH function and the TEXT function. The MONTH function will return the value of the month as a number, while the TEXT function will return the value of the month as a text corresponding to its month.

Using the MONTH function

  1. In the cell where you want to extract the month, type the =MONTH() function.
  2. Inside the parentheses of the MONTH() function, input the cell reference of the cell that contains the date you want to extract the month from. For example, if the date is in cell A1, you would enter =MONTH(A1)
  3. Press the Enter key on your keyboard to calculate the function. The result will be the month of the date in the form of a number (e.g. 1 for January, 2 for February, etc.).

Using the TEXT function

  1. In the cell where you want to extract the month, type the =TEXT() function.
  2. Inside the parentheses of the TEXT() function, input the reference of the cell containing the result of the MONTH() function and the format code “MMM” to get the month name in short format. For example, if the result of the MONTH() function is in cell A1, you would enter =TEXT(A1,”MMM”)
  3. Press the Enter key on your keyboard to calculate the function. The result will be the month of the date in the form of a short text (e.g. “Jan”, “Feb” etc.).

Converting months to a number and vice versa

In this example, we only have the numbers of the month and you want to convert it into a month; subsequently, you only have the names of the month and you want to convert them to the corresponding number. To do these, we will utilize functions to convert them accordingly.

Converting a number to a month.

  1. In the cell where you want to convert the number to a month, type the =TEXT() function.
  2. Inside the parentheses of the TEXT() function, input the cell reference of the cell that contains the number you want to convert to a month, followed by the format code “MMM” to get the month name in short format. For example, if the number is in cell A1, you would enter =TEXT(A1*28,”MMM”)
  3. Press the Enter key on your keyboard to calculate the function. The result will be the month of the number in the form of a short text (e.g. “Jan”, “Feb” etc.).

Note: Since the text function looks at the cell value as a date, you have to multiply the cell with 28; otherwise, it will return a value of “Jan.”

Converting a month to its corresponding number.

  1. In the cell where you want to convert the month to a number, type the =MONTH() function.
  2. Inside the parentheses of the MONTH() function, type in DATEVALUE()
  3. Inside the parentheses of the DATEVALUE() function, input the cell reference of the cell that contains the month you want to convert to a number and add: “&”1””. For example, if the month is in cell A1, you would enter =MONTH(DATEVALUE(A1&”1”))
  4. Press the Enter key on your keyboard to calculate the function. The result will be the number of the month (e.g. 1 for January, 2 for February, etc.)

Conclusion

The MONTH() and TEXT() functions, along with other built-in functions such as DATEVALUE(), are powerful tools that can help you easily extract, convert, and manipulate data in Excel. With the step-by-step guide provided in this tutorial, you should now have a solid understanding of how to work with months in Excel and be able to confidently extract and convert data for your own projects.

Related Posts: