Excel Formula: Display Month And Year Only

When working with dates in Excel, it's common to want to display only the month and year, without the day. This can be achieved using various formulas, depending on the format you want to achieve. Here are a few methods:

Method 1: Using the TEXT Function

You can use the TEXT function to format a date cell and display only the month and year.

Formula: =TEXT(A1,"mmm yyyy")

Assuming the date is in cell A1, this formula will display the month as a three-letter abbreviation (e.g., "Jan" for January) followed by the year.

Method 2: Using the MONTH and YEAR Functions

You can use the MONTH and YEAR functions to extract the month and year from a date, and then concatenate them.

Formula: =MONTH(A1)&" "&YEAR(A1)

This formula will display the month as a number (e.g., "1" for January) followed by a space and the year.

Method 3: Using the FORMAT Function (Excel 2016 and later)

If you're using Excel 2016 or later, you can use the FORMAT function to format a date cell and display only the month and year.

Formula: =FORMAT(A1,"mmm yyyy")

This formula works similarly to the TEXT function, but is a more modern and flexible way to format dates.

Method 4: Using a Custom Number Format

You can also use a custom number format to display only the month and year.

Select the cell(s) you want to format, then press Ctrl+1 to open the Format Cells dialog box. In the Category list, select "Custom" and enter the following format code: mmm yyyy

Click OK to apply the format.

Tips and Variations

To display the month as a full name (e.g., "January" instead of "Jan"), use the format code mmmm yyyy or MMMM yyyy (Excel 2016 and later).
To display the year only, use the format code yyyy or YY (for a two-digit year).
To display the month and year in a different order, simply swap the order of the format codes (e.g., yyyy mmm).

Gallery Section