Month Excel Formula From Date: Number, Name, or Date

Formulas & functions · Updated November 18, 2026

To get the month number from a date in Excel, use =MONTH(A2) when the date is in cell A2. The result is a number from 1 to 12; use a formatting or text formula instead if you need the month’s name or a month-and-year label.

The Basic MONTH Formula

Excel’s MONTH function extracts the month component of a valid date. With a date such as March 14, 2025, in A2, the function returns 3. January returns 1 and December returns 12. It does not return the day or year, and it does not change the date in the original cell.

To apply it to a list, enter the formula in the row beside the first date, then copy it down. In Microsoft 365 for Windows, select the formula cell and drag its fill handle down the column, or copy and paste it into the remaining rows. Excel adjusts the reference for each row, so a formula referring to A2 becomes one referring to A3 in the next row.

For example, with dates in A2:A4—March 14, 2025; July 2, 2024; and December 31, 2025—and the formula entered beside each date, the results are 3, 7, and 12. The year and day do not affect the month number. A date that includes a time also works: Excel still returns the month of the date portion.

What Excel Means by a Date

Excel generally stores a date as a serial number and displays it using a date format. A displayed value such as Mar 14, 2025 is therefore more than a string of characters: Excel recognizes the underlying value as a date. MONTH reads that date value and returns its month component.

This distinction matters when data comes from a website, a text file, or another system. A cell can look like a date while actually containing text. If Excel does not recognize the value as a date, MONTH may return a #VALUE! error. Formatting the cell to look like a date does not necessarily convert text into a real date; the underlying value must be valid.

When a result seems unexpected, select the source cell and inspect the formula bar and cell format. A date can display in a different regional style from the one used when it was entered. The displayed order of day and month may vary, but a recognized Excel date still has a month value that MONTH can extract.

Return a Month Name Instead

MONTH returns a numeric value, not a word. If a report needs a label such as March, use Excel’s TEXT function to display the date with a month format. This returns text, which is useful for headings and labels but is not the same kind of value as a month number.

With a valid date in A2, enter =TEXT(A2,"mmmm") to return the full month name, such as March. With the same date, =TEXT(A2,"mmm") returns the abbreviated name, such as Mar. The number of m characters controls the display: one or two show a numeric month, three show an abbreviation, and four show the full name. These are formatting codes inside the TEXT function, not separate month functions.

Month names are normally displayed in the language associated with Excel’s regional settings. Because TEXT returns text, alphabetical sorting may put April before August rather than arranging months in calendar order. If the order matters, keep a numeric month value for sorting, use a real date as the category, or create a separate month-order field.

Show a Month and Year

For monthly reports, a month alone can be ambiguous: March 2024 and March 2025 have the same month number and name. A date formatted to show both month and year keeps those periods distinct. One direct option is to use TEXT, which creates a text label.

With a valid date in A2, enter =TEXT(A2,"mmmm yyyy") to display a label such as March 2025. To use a shorter label, enter =TEXT(A2,"mmm yyyy"), which displays Mar 2025. The space in the format code separates the month from the year; changing it to a hyphen gives a label such as Mar-2025.

If the result will be used in calculations, filtering, or chronological sorting, a real date is often more useful than a text label. With the original date in A2, enter =DATE(YEAR(A2),MONTH(A2),1) to create the first day of that date’s month. For March 14, 2025, the result is March 1, 2025. Format the result as mmmm yyyy to display March 2025 while retaining a genuine date value.

Extract the First or Last Day of the Month

Sometimes the goal is not to display the month but to calculate with its boundaries. A month-start date is useful for grouping transactions consistently, building monthly summaries, or matching records to a period. The DATE, YEAR, and MONTH functions can create that first day.

With a valid date in A2, the formula =DATE(YEAR(A2),MONTH(A2),1) returns the first day of the same month. For a date of July 18, 2024, it returns July 1, 2024. The result remains a date, so Excel can sort it chronologically and use it in date comparisons.

To return the last day of the month, use EOMONTH. With a valid date in A2, enter =EOMONTH(A2,0). For July 18, 2024, the result is July 31, 2024. The zero means the end of the month containing A2; a positive offset moves forward by months and a negative offset moves backward. EOMONTH is available in current Microsoft 365 Excel.

Handle Text Dates and Blank Cells

If a date is stored as text, first determine whether the text follows a format Excel can interpret under the computer’s regional settings. For a text date in A2 that Excel can parse, =MONTH(DATEVALUE(A2)) converts the text to a date value and then returns its month number. For example, if A2 contains text Excel interprets as March 14, 2025, the result is 3.

DATEVALUE can interpret ambiguous dates differently depending on regional settings. A string like 04/05/2025 might mean April 5 or May 4. When importing data, prefer an unambiguous date format or convert the source data using a method that explicitly identifies the day, month, and year. If DATEVALUE returns an error, changing the cell’s display format alone is not a reliable fix.

A blank source cell can produce an unwanted result if the formula is copied down a long column. To leave the output blank when A2 is blank, enter =IF(A2="","",MONTH(A2)). This assumes the source date is in A2 and returns a blank for an empty source cell; otherwise, it returns the month number. It is a display convenience, not a substitute for checking whether nonblank entries are valid dates.

Choose the Right Result for Your Task

The best approach depends on what the next step needs. A numeric month is compact and works well as a criterion or category number. A month name is easier to read in a report, while a real date representing the first day of the month is usually the strongest choice for sorting and date-based calculations.

  • Use MONTH when you need a number from 1 to 12, such as a month-based condition.
  • Use TEXT when you need a readable label such as March or Mar 2025 and do not need the result itself to behave as a date.
  • Use DATE with YEAR and MONTH when you need the first day of the month as a genuine date value.
  • Use EOMONTH when a calculation needs the final calendar day of a month.

One common misconception is that changing the number format of a date extracts the month into a separate value. A custom date format can display only the month, but the cell still contains the complete underlying date. That is useful when you only want a different appearance; it is not equivalent to creating a separate month number in another cell.

Common Problems and How to Fix Them

  • #VALUE! error: The source may be text rather than a recognized date, or it may contain characters Excel cannot convert. Check the source value and its regional interpretation.
  • A result of 1 for a blank-looking cell: The source may contain a zero or a formula result rather than being truly empty. Check the formula bar and use a blank-checking formula if appropriate.
  • Wrong month for an imported date: Day-month order may have been interpreted differently during import. Verify the actual date value, not only its display.
  • Month names sort alphabetically: TEXT creates text labels. Sort by the original date or by a numeric month field instead.
  • The formula appears as text: The formula cell may be formatted as Text or may include a leading apostrophe. Change the cell format to General, then re-enter the formula.

On Excel for the web, the same MONTH, TEXT, DATE, and EOMONTH formulas work for ordinary date calculations. On Mac, the formulas are also the same; ribbon labels or keyboard shortcuts may differ, but there is no separate month formula for those platforms.

Frequently Asked Questions

What is the Excel formula to get the month from a date?
Use MONTH with the cell containing the date. For example, a date in A2 returns its month number from 1 through 12 when you enter =MONTH(A2).

How do I get the month name from a date in Excel?
Use TEXT with a month format. With a valid date in A2, =TEXT(A2,"mmmm") returns the full name, while =TEXT(A2,"mmm") returns an abbreviation.

Can Excel return the month and year together?
Yes. With a date in A2, =TEXT(A2,"mmmm yyyy") returns a text label such as March 2025. For sorting or date calculations, use a real date value and format it to show the month and year instead.

Why does MONTH return #VALUE!?
The referenced cell may contain text that Excel does not recognize as a date. Check whether the date is valid under your regional settings. DATEVALUE can convert some parseable text dates, but ambiguous formats may need to be corrected at the source.

Does MONTH work with dates that include a time?
Yes. If Excel recognizes the cell as a date and time, MONTH returns the month from the date portion. The time does not change the result.

How can I show the month without changing the original date?
Use a separate formula cell for a month number or month name, or apply a custom date number format if you only want to change how the original date looks. A display format changes appearance, not the underlying date value.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.