How To Extract Month And Year From Date In Excel: A Complete Step-by-Step Guide

Extracting the month and year from an Excel date is achieved using either non-destructive cell formatting, the versatile TEXT function, or Power Query for larger datasets. To return a dynamic text string like January 2026, use the formula =TEXT(A2, "mmmm yyyy") on a standard Excel serial date. Choosing the right method depends on whether you need to retain the underlying date serial number for mathematical operations or convert it into a static text label for reporting.

Data Integrity and Pre-Extraction Planning

Excel does not view dates as text strings; instead, it stores them as sequential serial numbers starting from January 1, 1900. For instance, January 1, 1900, is stored as serial number 1, while January 1, 2026, is stored as serial number 46023. Understanding this underlying database architecture is critical before executing any formulaic alterations. If you try to extract elements from a date that Excel reads as a plain text string rather than a serial date, your formulas will return errors or incorrect outputs.

Pre-Extraction Checklist

• Input Data Validation: Confirm that your source column is classified as a true date. Select your data cells and press Ctrl + 1 to check the classification. If classified as Text, you must convert it using the Text to Columns feature.
• Required Tools: Microsoft Excel (any version from 2007 through Office 365) or Google Sheets.
• Prerequisite Knowledge: Basic cell reference logic (absolute vs. relative referencing) and navigating the formula bar.
• Time Allocation and Computational Budget: 5 to 15 minutes. For datasets exceeding 100,000 rows, formulas like TEXT can consume processing memory. In these high-volume cases, using custom cell formatting or Power Query is recommended to avoid performance lag.

Six Professional Workflows to Isolate Month and Year in Excel

Method 1: The TEXT Function for Dynamic Text Labels

The TEXT function is the most popular way to extract month and year as a text string. It allows you to format the output exactly how you want, making it ideal for dashboard labels and presentation charts. The function uses the syntax =TEXT(value, format_text), where value is the source date cell, and format_text is your desired display code wrapped in quotation marks.

Step 1: Select an empty column adjacent to your source dates. Click on the first destination cell, for example, cell B2.

Step 2: Type the formula =TEXT(A2, "mmmm yyyy") and press Enter. This extracts the full month name followed by the four-digit year (such as January 2026). If you prefer a shortened version, type =TEXT(A2, "mmm-yy") to output Jan-26.

Step 3: Hover your cursor over the bottom-right corner of cell B2 until a small black cross (the Fill Handle) appears. Double-click or drag it down to apply the formula to the rest of the column.

Pro-Tip: Remember that the TEXT function permanently converts dates into text strings. If you try to sort this new column chronologically, Excel will sort it alphabetically (e.g., April will appear before January). If chronological sorting is required, use Method 2 or Method 3 instead.

Method 2: Custom Number Formatting (Preserving Serial Data)

If you need your data to look like "January 2026" but still need Excel to recognize it as a real, sortable date for pivot tables and timelines, use Custom Number Formatting. This changes how the date looks without altering its underlying serial value.

Step 1: Highlight the entire range of cells containing the dates you want to change.

Step 2: Press Ctrl + 1 on your keyboard (or right-click and choose Format Cells) to open the formatting menu.

Step 3: In the Category pane on the left, click on Custom.

Step 4: Go to the Type input box, delete any existing text, and type "mmmm yyyy" (with or without a space or comma depending on your preference). Click OK. The dates will update visually, but if you click on a cell, you will see the full, original date in the formula bar.

Method 3: Combining the YEAR and MONTH Functions for Numeric Analysis

When building financial models, databases, or math formulas, you may need the month and year as separate numbers. This method combines the YEAR and MONTH functions to give you clean, numeric values.

Step 1: To extract just the year as a four-digit number, use the formula =YEAR(A2) in your target cell.

Step 2: To extract the month as a number from 1 (January) to 12 (December), use =MONTH(A2) in an adjacent column.

Step 3: If you want to combine them into a single, clean date value that always starts on the first of the month, use the formula =DATE(YEAR(A2), MONTH(A2), 1). This is highly recommended for financial models, as it keeps your data in a uniform date format while standardizing the day to the 1st of every month.

Warning: Do not use simple division or text splitting on serial dates. For instance, dividing a date cell by another number will only calculate mathematical divisions on the underlying 5-digit serial code, yielding incorrect results. Always use dedicated date functions.

Method 4: Using Flash Fill for Quick, Static Extractions

If you do not need your extraction to update dynamically when your source data changes, Flash Fill is a fast, formula-free option. It uses pattern recognition to extract data in seconds.

Step 1: Insert a new column next to your date column.

Step 2: In the first cell of this new column (B2), manually type the exact month and year you want to extract from cell A2 (e.g., if A2 is 05/12/2026, type December 2026).

Step 3: Press Enter to move to cell B3. Begin typing the month and year for the second row. Excel will often show a greyed-out list of suggested values for the rest of the column.

Step 4: Press Enter to accept the suggestions. If the list does not appear automatically, select the rest of the empty cells in column B and press Ctrl + E to force Flash Fill to run.

Method 5: Power Query for Large Enterprise Datasets

For databases containing hundreds of thousands of rows, using formulas can slow down your workbook. Power Query handles this data transformation outside of the Excel grid, which keeps your workbook running fast.

Step 1: Click anywhere inside your data table, navigate to the Data tab on the Excel Ribbon, and select From Table/Range. This opens the Power Query Editor.

Step 2: In the Power Query window, right-click the header of your date column, select Duplicate Column, and rename this new column to Month-Year.

Step 3: Select the new column, go to the Transform tab at the top of the screen, and click on Date.

Step 4: Choose Month from the dropdown, then select Name of Month to extract the month name, or select Year to extract the year. To combine them, you can use the Format options under the Transform menu or merge columns using a custom delimiter. Once finished, go to the Home tab and click Close & Apply to load the clean data back into your spreadsheet.

Date Code Syntax and Structural Output Specifications

The following table outlines the key formatting codes used in both the TEXT function and Custom Number Formatting, along with their exact outputs and ideal use cases.

Diagnosing and Fixing Date Extraction Failures

Scenario 1: The extracted month returns "January" for every single row or a generic date in 1900.

• Root Cause: The source cell is empty, contains a zero, or is formatted as a text string that Excel cannot read as a date. Because Excel treats an empty cell or 0 as the serial number 0, it defaults to the start of its calendar: January 0, 1900.
• Actionable Fix: Clean your dataset to remove or skip blank cells. You can wrap your formula in an IF statement to handle blanks gracefully: =IF(A2="", "", TEXT(A2, "mmmm yyyy")). If the source dates are stored as text, select the column, navigate to the Data tab, click Text to Columns, and click Finish to convert them back into true dates.

Scenario 2: The formula displays as a literal text string instead of the calculated result.

• Root Cause: The destination cell was set to the Text format before you typed or pasted the formula. This tells Excel to treat your formula as plain text rather than an active calculation.
• Actionable Fix: Select the problem cell, go to the Home tab, and change the number format dropdown from Text to General. Next, double-click inside the formula bar and press Enter to recalculate the cell.

Scenario 3: Regional setting conflicts cause formatting code errors.

• Root Cause: Excel uses regional formatting codes based on your computer's operating system settings. For example, while English systems use "yyyy" for year and "dd" for day, German systems require "jjjj" (Jahr) and "tt" (Tag). If you use English codes on a non-English system, Excel will return an error or display the literal characters instead of the year.
• Actionable Fix: Either update your formula to use your system's regional codes (e.g., =TEXT(A2, "mmmm jjjj") in German versions) or use locale-independent functions like DATE, YEAR, and MONTH, which automatically adjust to any system's regional settings.

Frequently Asked Questions

How do I extract only the month and year so that it sorts chronologically?

To sort chronologically, use custom cell formatting (Method 2) using the code "mmmm yyyy" on your original serial dates. This keeps the underlying date intact so Excel can sort it correctly. Alternatively, if you must use formulas, use the "yyyy-mm" format code. Because this starts with the year, alphabetical sorting will match chronological sorting.

Can I extract the month name in a different language?

Yes, you can specify a language code inside the TEXT function. For example, to extract the month name in French, use the formula =TEXT(A2, "[$-040C]mmmm yyyy"). The code "[$-040C]" tells Excel to use French formatting, regardless of your computer's default regional settings.

What is the difference between custom formatting and the TEXT function?

Custom formatting only changes how the cell looks on your screen while preserving the underlying serial date for math calculations and sorting. The TEXT function permanently converts the date into a text string, which is useful for combining text in dashboards but prevents Excel from performing math operations or sorting chronologically.

Why does my Flash Fill fail to extract the dates correctly?

Flash Fill relies on clear patterns. If your source column contains mixed date formats (such as some written as MM/DD/YYYY and others as DD/MM/YYYY), Flash Fill will get confused and return incorrect data. Ensure your source dates are formatted consistently before running Flash Fill.

Master Your Corporate Data Architecture

Transform your raw corporate data into interactive, automated management dashboards with our comprehensive enterprise spreadsheet training. Contact our operations team today to design a custom data analytics workshop tailored to your organization's business needs.