Convert date to month and year in Excel

To convert a standard date to month and year, you can use the TEXT function.


This function converts a value to text in a specific number format. In our case, it’s month and year.

Only month

If you want to return only the month, without day and year, you can also use this function, but this time, formatted differently. Insert these formulas into cells: B2, B3, B4, and B5.

=TEXT(A2, "mmmm")
=TEXT(A3, "mmm")
=TEXT(A4, "mm")
=TEXT(A5, "m")

Each of these formulas returns a different notation of a month:

B2: Long name

B3: Short name

B4: Number with leading zero

B5: Number without leading zero.

The last notation (with single “m”) can also be written using the MONTH function.

Only year

In a very similar way, you can convert date to year.

=TEXT(A2, "yyyy")
=TEXT(A3, "yyy")
=TEXT(A4, "yy")
=TEXT(A5, "y")

There are only two types of formattings for the year: with four digits (“mmmm” and “mmm”) and with two digits (“mm” and “m”), so you can use them interchangeably.

Even when you use a single letter to format a year, it returns “08” and not “8”.

There is also the YEAR function. It returns the year of a date.

The YEAR function returns years in the same way as the TEXT function formatted as “mmmm” or “mmm”.

Different ways of formatting

Now, when you know how to display months and years, you can create many different formattings, depending on your requirements.

Here are a few examples: