You can use the following formula in Excel to convert a 3-letter month abbreviation to a month number in Excel:
=MONTH(DATEVALUE(A2&1))
This particular formula converts the 3-letter month abbreviation in cell A2 to a month number.
For example, if cell A2 contains Oct then this formula will produce a value of 10, since October is the 10th month.
The following example shows how to use this formula in practice.
Example: Convert 3 Letter Month Abbreviation to Number in Excel
Suppose we have the following column of 3-letter month abbreviations in Excel:
We can type the following formula into cell B2 to convert the 3-letter month abbreviation in cell A2 to a month number:
=MONTH(DATEVALUE(A2&1))
We can then drag and fill this formula down to each remaining cell in column B:
Column B now displays the month number for each 3-letter month abbreviation in column A.
How This Formula Works
Consider the formula that we used:
=MONTH(DATEVALUE(A2&1))
First, this formula uses A2&1 to add a 1 to the end of the three-letter abbreviation.
For example, JAN becomes JAN1.
Next, the DATEVALUE function converts a text date to a serial number representing the number of days since Jan 1, 1900.
For example, JAN1 becomes 44927 (the formula assumes the year being used is the current year) since Jan 1, 2023 is 44,927 days after Jan 1, 1900.
Lastly, the MONTH function extracts the month number from the serial number.
For example, the month number associated with the serial number 44927 is 1.
Note: You can find the complete documentation for the DATEVALUE function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Convert Days to Months in Excel
How to Calculate Average by Month in Excel
How to Calculate the Number of Months Between Dates in Excel
