If the date is in A2, enter =TEXT(A2,"mmmm yyyy") in B2 to display its month and year, such as February 2025. This is the quickest option when you want a readable label; if you need the result to remain a date for sorting, calculations, or PivotTables, use a date value with a month-year number format instead.
The basic month-and-year formula
In the example, A2 contains a genuine Excel date, such as February 15, 2025, and B2 contains the formula. The result is the text February 2025. The formula does not change the date in A2; it creates a separate display value in B2.
The format pattern inside the quotation marks controls how the result looks. Four m characters mean the full month name, and four y characters mean a four-digit year. Change the pattern to suit the space available or the style of your report:
- With a date in A2 and the result in B2,
=TEXT(A2,"mmm yyyy")returns Feb 2025. - With a date in A2 and the result in B2,
=TEXT(A2,"mm/yyyy")returns 02/2025. - With a date in A2 and the result in B2,
=TEXT(A2,"mmmm, yyyy")returns February, 2025.
Use the month-year style that makes the data easiest to scan. A numeric month can be more compact, while the full month name is often clearer in a presentation or a summary for people who do not work with the dates every day.
How the TEXT formula works
Excel stores a real date as a number and applies a format to show it as a calendar date. The TEXT function takes a value and a format pattern, then returns a text string that looks the way the pattern specifies. In =TEXT(A2,"mmmm yyyy"), A2 is the value and mmmm yyyy is the requested display pattern.
Because TEXT returns text, the result is a label, not a date. For example, February 2025 in B2 will not behave exactly like February 15, 2025 in A2. You can use the label in headings, concatenated messages, or chart labels, but it is not the best source for date arithmetic or chronological sorting. This distinction is central to choosing the right approach.
Excel’s month names follow the language and regional settings used to interpret and display dates. If a workbook is opened with different regional settings, month names or date interpretation can differ. For a shared workbook, test the display on the computers or Excel environments that matter to your audience.
Choose between a text label and a real date
Use TEXT when the month and year are intended as a finished display label. Use a real date with a custom number format when the value should continue to work as a date. For example, a monthly sales report may need a month label in a chart, but its underlying dates should remain numeric so that Excel can sort, filter, calculate, and group them correctly.
To create a real date representing the first day of the month, with the source date in A2 and the result in B2, enter =DATE(YEAR(A2),MONTH(A2),1). If A2 is February 15, 2025, the result is February 1, 2025. It is a date value, even if you later choose to display only February 2025. The day is set to 1 so every date in the same month maps to the same monthly value.
This method is useful for building monthly summaries from transactions. You can compare the resulting month-start dates, sort them in calendar order, or use them as date fields in further calculations. Apply a month-and-year display format if you do not want the day 1 to appear.
Format an existing date without a formula
If you only want an existing date to look like a month and year, changing its number format is usually simpler than adding a formula. The cell keeps its original date value, while Excel changes how it is displayed. For a date in A2:
- Select A2, or select the range of date cells you want to format.
- Press Ctrl+1 on Windows to open Format Cells. On a Mac, press Command+1.
- Choose the Number tab and select Custom.
- In Type, enter
mmmm yyyyand choose OK.
A2 still holds the full date, such as February 15, 2025, but displays February 2025. Other cells that use the date in a calculation still see the underlying date. To show a shorter label, use mmm yyyy; for a numeric style, use mm/yyyy. These are number formats, not formulas, so enter them in the Custom Type box without an equals sign.
Formatting changes the appearance of the selected cells, not the underlying date. If you copy the cell and paste it as plain text into another application, the displayed text may be transferred instead of the original date value. The exact paste result depends on the paste option and destination.
Get the month number or year separately
Sometimes you need the month and year as separate numeric values rather than a single label. With a date in A2 and the result in B2, =MONTH(A2) returns 2 for February. With the same date in A2 and the result in C2, =YEAR(A2) returns 2025. These functions can help build criteria, perform comparisons, or create separate report columns.
Be aware that a month number alone does not identify a unique month across multiple years: February 2024 and February 2025 both return 2. Keep the year alongside the month if you are comparing data across years. For a stable monthly key that is still a date, the first-of-month formula above is often more convenient than keeping two separate numbers.
Common problems and how to fix them
- The result shows a number or an error. Check that the source cell contains a valid Excel date. A date imported as text may not be recognized as a date, and regional settings can affect how strings such as 03/04/2025 are interpreted. If you control the input, enter a clear date or convert the source text to a real date before formatting it.
- The result says January 1900 or another unexpected month. A blank source cell can be treated like zero in date-related calculations or formats. Check for empty cells and decide how they should appear rather than assuming a blank will automatically produce a blank label.
- Months sort alphabetically. TEXT creates text, so labels such as April 2025 and February 2025 may sort alphabetically rather than in calendar order. Sort using the original date column or a real first-of-month date, then use the text label only for display.
- Every row displays the same month. Confirm that each formula points to the corresponding date row. When filling a formula down, a relative reference such as A2 normally changes to A3, A4, and so on. A reference with dollar signs, such as $A$2, stays fixed.
- The month name is abbreviated or appears in another language. Check the format pattern and the workbook’s regional or language settings. Use four m characters for a full month name and three for an abbreviation. Month names may be localized according to the environment.
If blank source cells are common, you can return an empty string for those rows. With source dates in A2:A100 and the display formula in B2, enter =IF(A2="","",TEXT(A2,"mmmm yyyy")) in B2 and fill it down. A blank A2 then produces a blank B2; a valid date such as February 15, 2025 produces February 2025. The formula assumes the cells in A contain either recognized dates or blanks.
Fill the formula down and use it in summaries
For a list of dates in column A, place the formula in B2 and fill it down through the rows with data. You can drag the fill handle at the lower-right corner of B2, or copy B2 and paste into the cells below. Excel adjusts the relative reference automatically, so the next row refers to A3. If the source dates are in an Excel Table, entering the formula in a new table column generally fills the formula down that column.
For a summary that must remain in date order, consider using the original dates or first-of-month date values as the grouping field. A text month-year column is convenient for reading, but it can make sorting and date filtering less straightforward. Keeping both a genuine date field and a display label is reasonable when a report needs a clear caption and reliable date-based analysis.
Frequently Asked Questions
What is the simplest Excel formula to show month and year?
With a date in A2 and the result in B2, use =TEXT(A2,"mmmm yyyy"). It displays the full month name and four-digit year, for example February 2025, but the result is text.
How do I show month and year without a formula?
Select the date cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Number and then Custom, and enter mmmm yyyy as the format. The cells remain real dates.
How do I display a short month and year?
With the date in A2 and the result in B2, use =TEXT(A2,"mmm yyyy") to return a label such as Feb 2025. For a real date, apply the custom number format mmm yyyy instead.
Can I use the month-year result for sorting?
You can sort the original date column or a real date representing the first of each month chronologically. A result produced by TEXT is text, so sorting that column alone may put month names in alphabetical order rather than calendar order.
Why does my formula return an error or the wrong month?
The source may be text rather than a recognized date, or Excel may be interpreting an ambiguous date string according to regional settings. Check the value in the source cell and convert it to a genuine date before applying a display formula or number format.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Advanced Excel HLOOKUP Formula: Examples and TipsFor an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys…
- Excel Business Days: NETWORKDAYS and WORKDAY FormulasTo count weekdays between two dates in Excel, use =NETWORKDAYS(B2,B3); for example, with a Monday start date in…
- Sort Excel and Keep Rows Together Without Data Mix-UpsTo sort Excel data and keep each record together, select the entire data range or click inside an…
- Excel Formula to Extract the First Five Digits from D2Assuming D2 contains the text or number you want to search, use =IFERROR(IF(D2="","",LEFT(TEXTJOIN("",TRUE,IFERROR(MID(D2,SEQUENCE(LEN(D2)),1)*1,"")),5)),"") to collect the first five…
- Excel SUM Array: Formulas, Examples, and Common FixesTo add the values in an array or range in Excel, use =SUM(A2:A10) when the numbers are in…
- Excel Sort Multiple Columns: Rules, Steps, and TipsTo sort Excel data by multiple columns, select a cell in the data, choose Data > Sort, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.