To convert a standard date to a month and year, you can use the TEXT function.
|
1 |
=TEXT(date,"mm-yyyy") |
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.
|
1 2 3 4 |
=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 a single “m”) can also be written using the MONTH function.
Only year
In a very similar way, you can convert date to year.
|
1 2 3 4 |
=TEXT(A2, "yyyy") =TEXT(A3, "yyy") =TEXT(A4, "yy") =TEXT(A5, "y") |
There are only two types of formatting 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, that you know how to display months and years, you can create many different formattings, depending on your requirements.
Here are a few examples:
