Find Month and Year From a Date in Excel with Formulas

To find a month and year from a date in Excel, enter =MONTH(A2) for the month number and =YEAR(A2) for the year, assuming the date is in A2. To show both together as a readable label, use =TEXT(A2,"mmmm yyyy"). These formulas work in Microsoft Excel for Microsoft 365 on Windows; first make sure the source cell contains a real Excel date, not text that only looks like one.

What You’ll Need

An Excel workbook and a cell containing the date you want to use. The examples below assume the first date is in A2.
One or more empty columns for the results, if you want to keep the month and year separate.
A clear idea of the output you need: a month number, a month name, separate month and year values, or a single month-year label.

You do not need an add-in. MONTH, YEAR, and TEXT are standard Excel worksheet functions. If the date is part of a table or a longer list, the same formulas can be filled down for the other rows.

Step-by-Step

Check the source date. Find the cell that holds your date and confirm that Excel recognizes it as a date. Click the cell and look at the formula bar. A displayed date such as 7/14/2025 may be stored as an Excel date serial, even if the cell is formatted to show a date. A quick clue is to temporarily choose Home > Number Format dropdown > General. A real date will usually appear as a number; switch the cell back to a date format afterward. Do not leave it as General if you want it to display as a date.
Choose where the result should go. For separate results, use two blank columns next to the date list. For example, if the dates are in column A, you could put Month in B1 and Year in C1, then place the formulas in B2 and C2. For a combined label, reserve one blank column and add a heading such as Month and year. Using headings makes the results easier to understand and sort later.
Extract the month as a number, if that is useful. With the date in A2 and the month result going in B2, enter =MONTH(A2) in B2 and press Enter. Excel returns a number from 1 to 12: for example, a date in July returns 7. This is a numeric month value, not the word July. It is useful for calculations, grouping, or creating a separate month field.
Extract the year into its own cell. With the date in A2 and the year result going in C2, enter =YEAR(A2) in C2 and press Enter. A date in July 2025 returns 2025. The result is a number, so you can use it in comparisons and calculations. If the source date includes a time as well, YEAR still returns the year portion of the date.
Show a month name instead of a number, if preferred. With the date in A2 and the month-name result going in B2, enter =TEXT(A2,"mmmm") in B2. Excel displays the full month name, such as July. To use a shortened name such as Jul, use =TEXT(A2,"mmm") instead. TEXT returns text, not a numeric month, so use MONTH when you need a number for later calculations.
Combine the month and year in one cell. With the date in A2 and the label going in B2, enter =TEXT(A2,"mmmm yyyy") in B2. For a date in July 2025, the result is July 2025. For a shorter label, use =TEXT(A2,"mmm yyyy"), which returns Jul 2025. The space between the month and year in the format code creates a space in the displayed result.
Alternatively, keep a real date and change only how it looks. This is often the better option if you will sort the results chronologically or use them in date calculations. With the original date in A2 and the formatted result also in A2, select the cell or range, press Ctrl+1 on Windows to open Format Cells, and choose Number > Custom. Enter mmmm yyyy in the Type box and select OK. The cell still contains the original date; Excel simply displays it as a month and year. On Mac, select the cells and use Command+1 to open Format Cells; the dialog and custom format approach are similar.
Copy the formula down the list. If the dates continue below A2, select the formula cell and drag its fill handle (the small square at the lower-right corner) down the column. Excel adjusts the reference from A2 to A3, A4, and so on. You can also copy the formula, select the destination cells, and paste. Check a few rows near the bottom to confirm each result lines up with the correct source date.
Choose a display style that suits the task. Use separate numeric month and year values when you need to filter or calculate by each component. Use a TEXT formula when you need a label for a report or heading. Use custom number formatting when you want a date to look like a month-year label but remain a real date value. These methods may look similar on the sheet, but they behave differently in later calculations.

For example, if A2 contains the real date July 14, 2025, MONTH returns 7, YEAR returns 2025, and the combined TEXT formula displays July 2025. If you use a custom format of mmm yyyy on A2, Excel displays Jul 2025 while retaining July 14, 2025 as the underlying date.

Common Mistakes

Using a text date instead of a real date. A cell can look like a date but contain text. MONTH and YEAR may return #VALUE! if Excel cannot interpret that text as a date. This commonly happens with dates imported from another system or written in a different regional order.
Confusing a month number with a month name. MONTH returns 1 through 12; it does not return January through December. Use TEXT with mmmm when you need the full name, or apply a date format to a real date.
Expecting a TEXT result to behave like a date. TEXT creates a text string. A label such as July 2025 may look right but will not act like a date in every sort, filter, or calculation. Keep the original date or use custom number formatting if date behavior matters.
Using the wrong cell reference. If the date is in A2 but the formula points to B2, the result may be wrong or may produce an error. Check the reference in the formula bar, especially after copying formulas across columns.
Formatting the formula rather than the source data by accident. A custom date format changes only how an existing date value appears. It will not convert arbitrary text into a valid date, and applying a date format to a TEXT result will not turn that text back into a date.

Tips

Use one output column for a compact report. A month-year label is convenient for charts and summaries. For calculations, however, retain a date or separate numeric month and year columns so Excel can compare values reliably.
Choose a format that avoids ambiguity. July 2025 is easy to read; 07/25 can be unclear because readers may interpret it differently. The custom format mmm yyyy gives a concise but recognizable month-year display.
Keep the day when chronological order matters. If you format a date such as July 14 as July 2025, Excel still stores the day. If you need each month represented by its first day instead, create a new date value. With the source date in A2 and the month-start result in B2, enter =DATE(YEAR(A2),MONTH(A2),1). The result for July 14, 2025 is July 1, 2025; format B2 as mmmm yyyy if you want to hide the day.
Use clear headings. Label columns Month number, Month name, Year, or Month-year label rather than leaving readers to infer what each output means. This is especially helpful when another person will use the workbook or when the columns feed a pivot table.
Consider regional settings. Excel displays month names according to language and regional settings. A formula that shows July on one computer may show the equivalent month name in another language. The underlying date remains the same.

Troubleshooting

The formula returns #VALUE!. Check whether the source is stored as text. Select the source cell and change its format to General as a diagnostic: a real Excel date usually appears as a serial number. If it remains text, re-enter the date in a format Excel recognizes for your regional settings, or use Excel’s import and conversion tools to correct the source column. Do not assume that changing the cell’s number format alone converts text into a date.

The result is a number you did not expect. MONTH is designed to return a number from 1 to 12, and YEAR returns a four-digit year. If a cell containing one of these results is formatted as a date, Excel may display an unexpected date instead of the number. Select the result cell and choose Home > Number Format dropdown > General, or select Number, to show the numeric result.

The combined result shows an unexpected month or year. Verify the source date and the formula reference. If the date was imported, check whether a value such as 03/04/2025 means March 4 or April 3 in the source system. Excel can interpret ambiguous text according to regional settings. Correct the source date before relying on formulas.

The formula appears as text instead of calculating. Make sure the entry starts with an equals sign and the cell is not formatted as Text. Change the cell format to General, then re-enter the formula (or click in the formula bar and press Enter). Also check that Show Formulas is not turned on; on Windows, Ctrl+` toggles formula display. On Mac, the shortcut can vary by keyboard, so check the Formulas tab for the Show Formulas command.

Copied formulas all show the same date’s month or year. Look for dollar signs in the reference. A formula using =MONTH($A$2) keeps pointing at A2 when filled down, while =MONTH(A2) adjusts to A3, A4, and later rows. Use a fixed reference only when you intentionally want every row to use the same source cell.

Frequently Asked Questions

How do I get the month name from a date in Excel?
With the date in A2 and the month name going in B2, enter =TEXT(A2,"mmmm") for the full name, such as July. Use =TEXT(A2,"mmm") for the abbreviated name, such as Jul. These formulas return text.

How do I get the month and year in one cell?
With the date in A2 and the result in B2, enter =TEXT(A2,"mmmm yyyy"). It displays a label such as July 2025. For a real date that remains sortable as a date, format the original cell with the custom format mmmm yyyy instead.

Can I get the month and year in separate columns?
Yes. If the date is in A2, enter =MONTH(A2) in the month column and =YEAR(A2) in the year column. The first result is a number from 1 to 12, and the second is the year number.

Why does Excel show a number instead of a month name?
MONTH returns a number by design. To display a name, use TEXT with a month format code, or format a real date with mmmm for the full name. If a numeric result looks like a date, change that result cell’s number format to General.

Can I extract month and year if my date includes a time?
Yes. Excel stores a date and time together as a numeric value, and MONTH and YEAR read the date portion. For example, if A2 contains July 14, 2025 at 3:30 PM, MONTH returns 7 and YEAR returns 2025.

Do these formulas work in Excel for Mac or Excel for the web?
MONTH, YEAR, and TEXT are standard Excel functions and work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The main difference described here is the Format Cells shortcut: use Command+1 on Mac rather than Ctrl+1 on Windows.

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.