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.
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
will give you “Dec”, while
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
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
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:
|yy||20||two-digit year format|
|yyyy||2020||four-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.
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
To show month in three characters
Showing Date as Year
Suppose we have “30th March 2090” as date.
Showing Date as Month and Year
Suppose we have 30th March 2090 recorded as 30/03/2090 in cell, then
|Mar 90||=Text(date, “MMM yy”)|
|Mar 2090||=Text(date, “MMM yyyy”)|
|2090 Mar 30||=Text(date, “yyyy MMM dd”)|