How to convert date to weekday name or month name 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 names or month names with Format Cell feature

Convert date to weekday name or month name with Text function

Convert date to weekday name or month name with Kutools for Excel

Insert dates as weekday/month name (day of week/month ) directly

Easily combine multiple worksheets/workbooks/CSV files into one worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks! Click for 60-day free trial!

ad combine sheets books 1

arrow blue right bubble Convert dates to weekday names or month names with Format Cell feature

We can customized the date formatting and display dates as weekday names or month names only easily in Excel.

1. Select the date cells that you will convert to weekday names or month names, 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 2015-1-17 for example, the following table will show other custom date formatting:

No. Formatting code Display as Example
1 ddd Weekday name Sat
2 dddd Weekday name Saturday
3 mmm Month name Jan
4 mmmm Month name January
5 m Month 1
6 d Day 17

3. Click the OK button to apply the custom date formatting.


arrow 
blue right bubble Convert date to weekday name or month name with Text function

Microsoft Excel's Text function can help you to convert date to its corresponding month name or weekday name easily.

In a blank cell, please enter this formula =TEXT(A1,"mmmm"), in this case in cell C1. , 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(A1,"dddd") .


arrow 
blue right bubble Convert date to weekday name or month name with Kutools for Excel

The Text function is easy to deal with a few date, and it will be time- consuming if many ones. Kutools for Excel's Apply Date Formatting tool can help you convert all date in selections to the month name or weekday name easily.

Kutools for Excel - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

1. Select the range that you will work with, and click Kutools > Format > Apply Date Formatting, see screenshot above:

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 items in the Date formatting box to convert the dates to month names, or select Wed or Wednesday items in the Date formatting box to convert the dates to weekday names.

Note: The Apply Date Formatting tool does not change the actual value.

Free Trial Kutools for Excel Now


arrow blue right bubbleDemo: Convert date to weekday name or month name with Kutools for Excel

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!


Easily insert date as weekday/month name (day of week/month )  in Excel

Normally the date will be inserted as “12/2/2015”, but we require to show the date as “Wednesday” or “Wed”. Kutools for Excel’s Insert Date can help us insert a date with specific date formatting by a double-click.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More      Free Download Now

ad insert formatting date 0


arrow blue right bubbleRelated articles:

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +2 subhash
Thanks, very much effective formula
2014-02-17 07:47 Reply Reply with quote Quote
Permalink +4 Pankaj Sanwaria
Thanks a lot for formula (=Text(Cell),"m mmm").
2014-02-28 07:18 Reply Reply with quote Quote
Permalink +6 Pankaj Sanwaria
Thanks a lot for formula =TEXT(A1,"mmmm" )to convert date in text.
2014-02-28 08:19 Reply Reply with quote Quote
Permalink +1 PAttie
I entered the text formula but it did not work...help?
2014-03-24 20:33 Reply Reply with quote Quote
Permalink 0 PAttie
My goodness, it's working now....sorry!
2014-03-24 20:37 Reply Reply with quote Quote
Permalink 0 hey 1990
need how to convert date(25-02-14) into month in excel. also while creting the pivot if have to date in a same month showing sam e month in two times in pivot.pls
2014-04-08 07:01 Reply Reply with quote Quote
Permalink 0 John
Thank you, this saved me HOURS of time!!!!!
2014-05-12 19:41 Reply Reply with quote Quote
Permalink 0 Smarty
Thanks a lot :)
Really helpful.
2014-06-02 08:35 Reply Reply with quote Quote
Permalink +1 Hemant
Good Job .... Great .....it solve my issues in seconds
2014-07-28 17:04 Reply Reply with quote Quote
Permalink 0 Girish
thanks for the formula.. very nice one :-)
2014-09-24 07:26 Reply Reply with quote Quote
Permalink +3 janey
for weekday you may use =TEXT(A1,"[$-14 409]dddd")and you get Friday displayed. No need that Kutools
2014-09-25 07:00 Reply Reply with quote Quote
Permalink +1 janey
or just =TEXT(WEEKDAY(A 1),"dddd")
2014-09-25 07:04 Reply Reply with quote Quote
Permalink +1 Charlie
Hi using Excel 2007... entered =TEXT(A1,"mmmm" ) got error... corrected formula with =TEXT(A1;"mmmm" ) getting result 00!! if I change "mmmm" with "dddd" it displays the "day" properly. How I can I get it to display the "month"?..
2014-10-13 10:31 Reply Reply with quote Quote
Permalink +1 Geno
There's an extra space hiding in your formula (try right before or after the closing parenthesis). The ";" should not work - the "," is the correct format for the formula.
2014-11-07 15:30 Reply Reply with quote Quote
Permalink 0 Charlie
Quoting Charlie:
Hi using Excel 2007... entered =TEXT(A1,"mmmm") got error... corrected formula with =TEXT(A1;"mmmm") getting result 00!! if I change "mmmm" with "dddd" it displays the "day" properly. How I can I get it to display the "month"?..

Quoting Geno:
There's an extra space hiding in your formula (try right before or after the closing parenthesis). The ";" should not work - the "," is the correct format for the formula.


Hello again, I've double checked the formula... no hidden spaces etc in it. In excel you can actually choose between "," and ";" as operative divider which in my case is ";" using it in the formula for day dddd works fine but mmmm does not return name of month! All I get is 00. I've tried on several computers with excel installed... seems to be a bug in the program.
2015-03-28 10:06 Reply Reply with quote Quote
Permalink 0 sudha
Nice formula... working good
2014-11-15 11:06 Reply Reply with quote Quote
Permalink 0 sudha
Thank you a lot....to show the weekday formula.
2014-11-15 11:09 Reply Reply with quote Quote
Permalink 0 rupesh sharma
Thank you, working good.
2015-03-23 05:25 Reply Reply with quote Quote
Permalink 0 Maher
Hi,

How to create a formula for the flowing:
1- Cell A2 has full birthday date (Day, Month and Year)
2- I want Cell B to show "happy Birthday" message if Cell A2 is today's birthday date

Thanks,
2015-07-29 12:18 Reply Reply with quote Quote
Permalink 0 Charlie
Quoting Maher:
Hi,

How to create a formula for the flowing:
1- Cell A2 has full birthday date (Day, Month and Year)
2- I want Cell B to show "happy Birthday" message if Cell A2 is today's birthday date

Thanks,

Hi,
Try this formula... =IF(C2=TODAY(); "happy birthday";"")
If it doesn't work replace ; with : also if you wish to leave cell B2 empty when birthday is not today then replace FALSE with "" in the formula.
2015-08-03 04:50 Reply Reply with quote Quote
Permalink 0 Charlie
Hi,
1- Type the date in A2 in the date format of your computer
2- Use formula '=IF(A2=TODAY() ;"Happy Birthday";FALSE ) in the cell you want to show Happy Birthday(cell B2) you may need to change ; with : in the formula if it doesn't work. Also if you want to leave cell B2 empty when A2 is not today then replace FALSE with "".
2015-08-03 05:19 Reply Reply with quote Quote
Permalink 0 Marcia
Thanks. This was incredibly helpful!!!
2015-09-22 17:48 Reply Reply with quote Quote
Permalink 0 Marilyn
My table came over with the date as 20160502, however I have to add 45 days to all dates so this format does not work, is there a way to convert it to 05/02/2016?
2016-05-20 15:30 Reply Reply with quote Quote
Permalink 0 Mike Delaney
When I use the formula in column K =CHOOSE(WEEKDAY (J4), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") then copy it down the column, any space in column J that is blank comes up with Saturday in column K. I need a way for if a cell is a blank in column J for it to stay blank in K as well.
2016-12-31 06:00 Reply Reply with quote Quote
Permalink 0 Charlie
Quoting Mike Delaney:
When I use the formula in column K =CHOOSE(WEEKDAY(J4), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") then copy it down the column, any space in column J that is blank comes up with Saturday in column K. I need a way for if a cell is a blank in column J for it to stay blank in K as well.


Hi, try the following formula in K4... =if(J4="","",CH OOSE(WEEKDAY(J4 ), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))
When copied down in column K if cells in column J is empty it'll stay blank in column K.
2017-01-05 16:36 Reply Reply with quote Quote
Permalink 0 ha
anyone please help me to get only weeks like i want to show data group by week and it need to show always Sunday and week group

example: 1/1, 1/8, 1/15
2017-01-01 20:47 Reply Reply with quote Quote
Permalink 0 eyvii
how i can display the date like this in excel 23th day of March 24, 2018?
2017-03-23 06:48 Reply Reply with quote Quote
Permalink 0 Aahana
Hi,

I want to add a column "Month" that displays month of the date mentioned in "Date" column and there are almost 11,000 rows. Please help. text() function cannot be applied to every column individually.

Thank you
2017-04-15 05:52 Reply Reply with quote Quote
Permalink 0 Charlie
Quoting Aahana:
Hi,

I want to add a column "Month" that displays month of the date mentioned in "Date" column and there are almost 11,000 rows. Please help. text() function cannot be applied to every column individually.

Thank you


Hi, on a separate sheet in the workbook create a table A1- A12 fill 1,2,3 etc, in col B1-12 type the month names.. jan, feb, mar etc..
Rename the Sheet to: Months
In name manager create a new name, call this "month" and refer to =Months!$A$1:$B$12
On the sheet you want to show the month, insert a column after the date, and type the following formula: =IF(A1="";"";VLOOKUP(Month(A1);month;2))
A1 should be replaced with the reference of the cell that contains the date! Then you can copy the formula down in the column.
If the dates in your sheet are in format yyyy-mm-dd then this formula will return April for 2017-04-18
Hope this helps.
2017-04-18 12:31 Reply Reply with quote Quote

Add comment


Security code
Refresh