How to convert date to weekday, month, year name or number in Excel?
Says you enter a date in one cell, and it shows as 12/13/2015. Is there a way to show only the month or the weekday, or the text of month name or weekday name, such as December, or Sunday? The following methods can help you easily convert or format any kinds of date to display only the weekday name or month name in Excel.
- Convert dates to weekday/month/year name or number with Format Cell
- Convert dates to weekday/month name with TEXT function
- Convert a date to weekday/month name with CHOOSE function
- Convert dates to weekday/month/year name or number with an amazing tool
Convert dates to weekday/month/year name or number with Format Cell
We can customize the date formatting and display dates as weekday names or month names only easily in Excel.
1. Select the date cells that you want to convert to day of week, month, or year names/numbers, right click and select the Format Cells from the right-clicking menu.
2. In the opening Format Cells dialog box, under Number tab click the Custom in the Category box, and then enter "ddd" into the Type box.
Note: The "ddd" will display date as weekday name such as "Sat". Take the date 3/7/2019 for example, the following table will show other custom date formatting:
| A | B | C | D | |
| 1 | Date | 3/7/2019 | ||
| 2 | ||||
| 3 | No. | Display as | Formatting Code | Example Shows |
| 4 | 1 | Weekday Name | ddd | Thu |
| 5 | 2 | Weekday Name | dddd | Thursday |
| 6 | 3 | Month Name | mmm | Mar |
| 7 | 4 | Month Name | mmmm | March |
| 8 | 5 | Month Number | m | 3 |
| 9 | 6 | Year Number | yyyy | 2019 |
| 10 | 7 | Year Number | yy | 19 |
| 11 | 8 | Date Number | d | 7 |
| 12 | 9 | Date Number | dd | 7 |
3. Click the OK button to apply the custom date formatting.
One click to convert multiple dates to week/month/year names or numbers in Excel
Have you ever converted a date to the day of week by right clicking and specify formatting code in the Format Cell dialog? Here, with Kutools for Excel's Apply Date Formatting feature, you can quickly show a series of dates as only month names, or day of weeks easily with only one click in Excel!
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now
Convert dates to weekday/month name or number with TEXT function
Microsoft Excel's TEXT function can help you to convert a date to its corresponding month name or weekday name easily.
In a blank cell, please enter this formula =TEXT(A2,"mmmm"), in this case in cell C2. , and press the Enter key. And then drag this cell's AutoFill handle to the range as you need.
And the date have been converted to month name. See screenshot:
Note: You can change the "mmmm" to other format code according to above table. For example, you can also convert a date to the weekday name with the formula =TEXT(A2,"dddd").
Do you want to Stand out from the crowd now? 30+ Date features raise your Expertise!
With 30+ date features of Kutools for Excel, you will get practical skills about dates in 3 minutes, and work faster and better than others, easily get pay raise and promotion!
To be More likeable
Efficiently deal with date problems in Excel, help you easily get appreciation of others in work.
Take care your family
Say goodbye to repetitive and trivial date work in Excel, save more time to accompany your family.
Enjoy healthy life
Insert, modify, or calculate dates in bulk, reduce hundreds of clicks every day, farewell to mouse hand.
Never worry about layoffs
Improve 91% work efficiency, solve your 95% date problems in Excel, finish work ahead of schedule.
Free up your memory
12 Kutools formulas about dates, stop memorizing painful formulas and VBA codes, work with ease.
Kutools for Excel brings 300 handy tools for 1500 work scenarios, only $39.0 but worth more than $4000.0 Excel training of others, save every penny for you!
Convert a date to weekday/month name with CHOOSE function
If these special formatting codes are hard to remember and apply in formulas, you can also apply the CHOOSE function to convert a date to the name of month or day of week in Excel. Please do as follows:
In a blank cell, please enter the formula =CHOOSE(WEEKDAY(B1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"), and press the Enter key. This formula will convert the date to the day of week as below screenshot shown.
Note: For converting a date to the name of month, please apply this formula =CHOOSE(MONTH(B1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Convert dates to weekday/month/year name or number with Kutools for Excel
The TEXT function is easy to deal with a few dates, and it will be time-consuming if many ones. Kutools for Excel's Apply Date Formatting tool can help you convert all dates in selections to the month name or weekday name easily.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now
1. Select the range that you will work with, and click Kutools > Format > Apply Date Formatting, see screenshot:
2. In the Apply Date formatting dialog box, please select the date formatting in the Date formatting box, and click the Ok button.
For example, you can select 03, Mar or March in the Date formatting box to convert the dates to month names or month numbers, or select Wed or Wednesday to convert the dates to weekday names, or select 01 or 2001 to convert dates to the year numbers, or select 14 to convert to date numbers.
Now all selected dates are converted to the specified date formatting, such as name of month, day of week, or others as you choose:
Note: The Apply Date Formatting tool does not change the actual values.
This Apply Date Formatting feature will help Excel users one click to convert all selected dates to the month names, day of weeks, year, etc. Have a Free Trial!
Related articles
Insert current day or month or year into cell in Excel
300 tools help you stand out from the crowd now
with Kutools for Excel - never worry about job cut
Kutools for Excel brings 300 handy tools for 1500 work scenarios, helps you work faster and better than your colleagues, and easily win trust of your boss. You will be the last one in the list of layoffs, and easily maintain a stable and better life for your family!
- To be a master of Excel in 3 minutes, and easily win appreciation of others.
- Improve 80% work efficiency, solve your 80% problems in Excel, not work overtime.
- Speed up your work, save 2 hours every day to improve yourself and accompany family.
- Say goodbye to complicated formulas and VBA code, free up your memory.
- Reduce thousands of keyboard operations and mouse clicks, far away from mouse hand.
- Spend $39.0, worth more than $4000.0 training of others.
- Choice of
110,000+highly effective people and 300+ famous companies, work stable in Excel.
- Full feature free trial 30-day, no credit card required.
