To get the month number from a date in Excel, enter =MONTH(A2) in another cell, assuming A2 contains a valid Excel date; for a date in November, the result is 11. The MONTH function returns a number from 1 to 12, so you can sort, group, or compare dates by month. It does not return the month name, and it does not change the date stored in the original cell.
What You’ll Need
- Excel for Microsoft 365 on Windows, or another recent version of Excel. MONTH is a long-standing Excel function and also works in Excel for Mac and Excel for the web.
- A cell containing a date Excel recognizes as a date. The date may be displayed in a format such as 11/2/2026 or 2-Nov-2026; the appearance can vary with your regional settings.
- An empty cell for the result. If you plan to calculate the month for many dates, leave room to fill the formula down the column.
Step-by-Step
- Check the date column. Find the cell containing the date you want to analyze. For example, you might have transaction dates in A2:A100, with a header in A1. Click a date cell and check that Excel treats it as a date, rather than text. A date may be aligned to the left because of formatting, but alignment alone is not a reliable test; you can also try changing its number format temporarily to a date or number format.
- Choose where the month number should appear. If dates are in column A, you could put the result in B2 and label B1 Month. Keeping the extracted month in a separate column preserves the original dates, which are useful for sorting and displaying the full date later.
- Enter the MONTH formula. With the date to analyze in A2, select B2 and type
=MONTH(A2), then press Enter. Replace A2 with the actual cell address of your date. Excel calculates the result immediately. For example, if A2 contains a valid date in November 2026, the returned value is 11, whether the date is November 2 or November 30. - Confirm the result is a number from 1 to 12. MONTH returns 1 for January, 2 for February, and so on through 12 for December. It ignores the day and year when producing this month number. If the result looks like a date or has unexpected decimal places, select the result cell and choose Home > Number Format, then select General or Number.
- Apply the formula to the rest of the dates. Select the cell containing your formula. Point to the small square at its lower-right corner, called the fill handle, and drag it down beside the remaining dates. Excel adjusts the relative reference as it fills: the next row uses A3, then A4, and so on. You can also copy the formula cell, select the destination range, and paste.
- Use the result for sorting or filtering. Select your data and use Data > Sort to sort by the month-number column. If you filter the table, you can filter that column to a particular number, such as 11 for November. Remember that sorting only by month puts January dates from different years together; include the year too if you need a true chronological order.
- Save or reuse the result as needed. The formula recalculates if the source date changes. If you need fixed month numbers that will not update when dates change, copy the result cells and use Home > Paste > Paste Values. This replaces the formulas with their current values, so keep the original dates or a copy of the formulas if you may need them later.
On Excel for Mac and Excel for the web, the MONTH function and formula entry work the same way. You can enter the formula in a cell and fill or copy it down. Ribbon layouts and the location of Paste Values can differ slightly between platforms and versions, but you do not need a special Mac or web version of the function.
Common Mistakes
- Expecting the function to return a month name. MONTH returns a number, not January or Jan. To display a month name from a date in A2, use
=TEXT(A2,"mmmm"); this assumes A2 contains a valid Excel date and returns the full month name according to the workbook’s language and regional settings. Use=TEXT(A2,"mmm")for an abbreviated name such as Nov. TEXT returns text, which may sort alphabetically rather than in calendar order, so keep a numeric month column when you need calendar sorting. - Using MONTH on text that only looks like a date. A value pasted from a website or imported from another system may be stored as text. MONTH can return a #VALUE! error when Excel cannot interpret that text as a date. Convert the source to real dates before relying on the result.
- Sorting by month number without considering the year. Month 1 represents January in every year. If your records span multiple years, sorting just by the MONTH result will not put all dates in chronological sequence. Sort using the full date, or include separate year and month fields in the sort.
- Confusing a month number with elapsed time. Subtracting two month numbers does not always tell you how many months passed between two dates, especially when the dates cross a year boundary. For example, December is 12 and January is 1, but January can come after December. Use a method designed for the time interval you need rather than interpreting the two MONTH results alone.
- Trying to use a month number as a complete date. The value 11 only identifies November; it does not contain a year or day. Keep the source date when you need to identify a unique date, or store the year alongside the month.
Tips
- Make the result column clear. Use a heading such as Month Number so other people do not mistake 1 through 12 for day numbers or a count of records.
- Keep a real date for chronological work. MONTH is especially useful for grouping or filtering, but the complete date is generally the better field for date sorting and date-range calculations.
- Combine month and year when comparing periods. A month number alone cannot distinguish November 2025 from November 2026. Keep the year in a separate column or use a year-month label for reports that cover multiple years.
- Use an Excel Table for growing lists. If you convert your data range to a table with Insert > Table, a formula entered in a new table column is generally filled through the table, and the formula column expands as you add rows. Check the result on new rows if table formula autofill has been disabled in your Excel settings.
- Do not change the source date just to get a month number. A custom display format can make a date look like a month, but it does not extract a separate value. A MONTH formula gives you a distinct result you can use in other calculations and filters.
- Use a month name only when it improves readability. A name can make a report easier to scan, while a number is usually more convenient for numeric comparisons. You can keep both columns if readers need the label and your analysis needs the number.
Troubleshooting
The formula returns #VALUE!. The source may be text, blank, or otherwise not recognized as a date. First check the source cell itself. If the date is text in a format Excel recognizes under your regional settings, you can try converting it with Text to Columns: select the affected cells, choose Data > Text to Columns, follow the wizard, and select a suitable date order in the final step when the wizard offers a date format choice. Alternatively, use a conversion formula on a text date in A2: =MONTH(DATEVALUE(A2)). This assumes A2 contains date text that DATEVALUE can interpret under your computer’s regional settings. DATEVALUE may fail or interpret the value differently if the text uses an ambiguous order, such as 03/04/2026. Check the converted date against the original source before using it for reporting.
The result is displayed as a date. The formula may be correct, but the result cell may have a date number format. Select the result, then choose Home > Number Format > General. The underlying result should display as an integer from 1 to 12.
The formula appears in the cell instead of a result. The cell may be formatted as Text, or Excel may be showing formulas. Change the cell format to General, edit the cell by pressing F2, and press Enter. If formulas are displayed throughout the sheet, turn off formula display with Ctrl+` on Windows; on Mac, the shortcut can vary by keyboard, so check Formulas > Show Formulas.
The formula does not update after the source date changes. Check that the cell contains a formula beginning with an equals sign and that calculation is set to automatic. In Excel for Windows, open Formulas > Calculation Options and select Automatic. If you pasted values over the formula, re-enter the formula and fill it down again.
The month name or interpretation seems wrong. Verify the original date and your regional settings. A text value such as 04/05/2026 can mean April 5 or May 4 depending on its source format. Excel’s display format can also differ from the stored date. Use an unambiguous date source or import setting, and confirm the year, month, and day before trusting the output.
Frequently Asked Questions
What does the Excel MONTH function return?
It returns an integer from 1 through 12 for the month of a date: 1 is January and 12 is December. It does not return the year, day, or month name.
Does MONTH work with a date that includes a time?
Yes. If the cell contains a valid date and time, MONTH returns the month from the date portion. The time of day does not change the result.
Can MONTH return January instead of 1?
MONTH itself returns a number. To show a name, use the TEXT function with a month format such as mmmm for a full name or mmm for an abbreviation. The result of TEXT is text, not a numeric month value.
Why does MONTH return 1 for some dates that look like they are in another month?
The cell may contain text Excel has interpreted differently, or the displayed format may hide the stored date. Check the source value, its regional date order, and the cell’s number format. A clear date format can help you verify what Excel is using.
Can I use MONTH to group dates by month in a PivotTable?
Yes. You can add a helper column containing the MONTH result and use that field in the PivotTable. If the source covers more than one year, include the year as well; otherwise, the same month number from different years will be grouped together.
Does the function require Microsoft 365?
No. MONTH is available in older Excel versions as well as Microsoft 365. The same basic formula works in Excel for Windows, Mac, and the web.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function: Can It Return Variable-Length Text?Yes. Excel’s MID function can return a variable number of characters when you calculate its num_chars argument from…
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare…
- Compound Interest Calculator in Excel: Formulas and TipsYes. In Microsoft Excel for Microsoft 365 on Windows, enter the starting balance, annual interest rate, number of…
- Excel SUMPRODUCT for Two Columns: Formula and ExamplesTo multiply corresponding values in two Excel columns and add the products, use =SUMPRODUCT(A2:A5,B2:B5). If A2:A5 contains 2,…
- Excel PV vs. NPV: Formulas, Examples, and DifferencesIn Excel, use PV to find the value today of a fixed series of equal payments, and use…
- Python pandas Excel Functions: Read and Write FilesPython pandas handles Excel files mainly with read_excel() to load workbook data into a DataFrame and DataFrame.to_excel() to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.