To convert date to month in Excel, you can use TEXT() function to change the format to show month only or use Month() function to extract month as a numeric value.

Excel Convert to Date

Datevalue() function is the function that will turn a date in text format to a serial date format that Excel recognises.

excel convert to date

Note that once you change it into a serial format, you will still have to change cell format (Home tab, Number group, format field) to date format for it to show as a proper date.

Excel Convert Date to Month

Suppose you have 15/12/2024 in cell B1 then then formula

=TEXT(B1, “MMM”)

will give you “Dec”, while

=TEXT(B1, “MMMM”)

will give you “December”.

This is just how the result is shown. Excel will still recognise the underlying value as 15/12/2024.

On the other hand, if you do not mind just getting the month, then you can use

=Month(B1)

Convert Date to Month and Year in Excel

There are two ways to convert date to month and year in Excel.

The first method involves changing the format but not the underlying value. For example if you have 12/03/2019 in cell A5, then the formula

=TEXT(A5, “mmm-yyyy”)

will give you Mar-2019.

Note that TEXT() function will give you a result in text format.

In addition, you can replace the arrangement of year and month.

Using 31/7/2020 as an example:

Number formatResultExplantion
MMMJulShort form
MMMMJulyLong form
yy20two-digit year format
yyyy2020four-digit year format

You can similarly use “dd”, “ddd” and “dddd” to represent day, day of the week in short form, day of the week in long form.

The second method allows you to convert the actual underlying date and keep it as a numeric value:

  • To find out the month of a date use the Month() function.
  • To find out the month of a date use the Year() function.
convert date to month in excel

Excel Date Format Formula

You can change the format of a date value without changing its underlying value.

Showing Date as Month

To show full month

=Text(date, “MMMM”)

To show month in three characters

=Text(date, “MMM”)

Showing Date as Year

Suppose we have “30th March 2090” as date.

ResultFormula
90=Text(date, “yy”)
2090=Text(date, “yyyy”)

Showing Date as Month and Year

Suppose we have 30th March 2090 recorded as 30/03/2090 in cell, then

ResultFormula
Mar 90=Text(date, “MMM yy”)
Mar 2090=Text(date, “MMM yyyy”)
Mar-2090=Text(date, “MMM-yyyy”)
2090 Mar 30=Text(date, “yyyy MMM dd”)

Related Post

Pin It on Pinterest

Share This