Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

  1. Convert dates to weekday/month/year name or number with Format Cell
  2. Convert dates to weekday/month name with TEXT function
  3. Convert a date to weekday/month name with CHOOSE function
  4. Convert dates to weekday/month/year name or number with an amazing tool

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! Full Feature Free Trial 30-day!

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

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.

Kutools for Excel

Stand out from the Crowd

300 Handy Tools
Solve 80% Problems in Excel
Free Trial Now

Say goodbye to terrible VBA and formulas!

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.

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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, 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

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.
Read More ...
Free Trial Now
 
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Daniel · 2 months ago
    if I have 4th Thursday of Dec 2019, how would I calculate the date in excel , what will be the formulla
    • To post as a guest, your comment is unpublished.
      kellytte · 2 months ago
      Hi Daniel,
      First, define the day of weekday. In general, we can use 1 represents Sun, 2 represents Mon, …, and 7 for Sat.
      Second, the Year and Month are fixed (2019 Dec)
      Now we can use the formula =DATE(B3,C3,1+E3*7)-WEEKDAY(DATE(B3,C3,8-VLOOKUP(D3,B6:C12,2,FALSE))) to return the specified date. See screenshot:
      Note: B3 is the year, C3 is Month, E3 indicates the nth day of week, D3 is the day of week, B6:C12 is the table where we define the day of weeks.
  • To post as a guest, your comment is unpublished.
    Saran · 7 months ago
    Hi,
    How can I convert day and time (IST) to PST? For example, SUN 6:00 AM (IST) in column A2, I need the value for PST which is SUN 7:30 PM.
  • To post as a guest, your comment is unpublished.
    Annastacia · 1 years ago
    02/01/2016 00:00 i Have date in this format and i want to convert it to the days of the week.....monday tuesday,wednesday etc. Kindly help
    • To post as a guest, your comment is unpublished.
      kellytte · 9 months ago
      Hi,
      Both =TEXT(A1,"dddd") and =TEXT(A1,"ddd") can convert the dates with time to days of week. Try them!
  • To post as a guest, your comment is unpublished.
    B · 1 years ago
    If I have a date in a1 (1/25/18) and I want a2 to give the month (Jan) but my months from the 25th - 26th of next month, ie; 12/26/17 - 1/25/17 would be Jan, and 1/26/18 - 2/25/18 would be Feb. So in my case if a1 is 1/27/18 would make a2 say Feb. What formula could I use? I can't find anything about setting your own date range to reflect a certain month, for like billing cycles for instance. Please help!!
    • To post as a guest, your comment is unpublished.
      kelly001 · 1 years ago
      Hi,

      you can try this formula =IF(DAY(A1)>25,TEXT(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),"MMMM"),TEXT(A1,"MMMM"))
  • To post as a guest, your comment is unpublished.
    owen · 2 years ago
    id like to ask, how to compute for the # of days outstanding based on the cut off date : e.g. 07.21.17 ( cutt off date
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      Hi Owen,


      Do you mean calculate days from today to the deadline? If so, you can try this formula =deadline date -TODAY()
  • To post as a guest, your comment is unpublished.
    pavan · 2 years ago
    hello, how can be show month period in this formate like 1 march 2017 to 31 march 2017, pls help
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      maybe this formula =TEXT(A1,"d mmmm yyyy") can help you
  • To post as a guest, your comment is unpublished.
    Aahana · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      Charlie · 2 years ago
      [quote name="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[/quote]

      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.
  • To post as a guest, your comment is unpublished.
    eyvii · 2 years ago
    how i can display the date like this in excel 23th day of March 24, 2018?
    • To post as a guest, your comment is unpublished.
      test · 1 years ago
      do you mean 23rd day of March, 2018
  • To post as a guest, your comment is unpublished.
    ha · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      kellytte · 9 months ago
      Hi,

      you can apply the WEEKNUM function to return the week number of specified date. for example, =WEEKNUM("2018/5/27",1) will return 22, it means 2018/5/27 is in the 22ed week of Year 2018.
      And then you can group dates by their week numbers.
  • To post as a guest, your comment is unpublished.
    Mike Delaney · 2 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Charlie · 2 years ago
      [quote name="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.[/quote]

      Hi, try the following formula in K4... =if(J4="","",CHOOSE(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.
  • To post as a guest, your comment is unpublished.
    Marilyn · 3 years ago
    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?
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      Hi Marilyn,
      =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) this formula will change the string 20160502 to date in default date formatting.
  • To post as a guest, your comment is unpublished.
    Marcia · 4 years ago
    Thanks. This was incredibly helpful!!!
  • To post as a guest, your comment is unpublished.
    Maher · 4 years ago
    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,
    • To post as a guest, your comment is unpublished.
      Charlie · 4 years ago
      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 "".
    • To post as a guest, your comment is unpublished.
      Charlie · 4 years ago
      [quote name="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,[/quote]
      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.
  • To post as a guest, your comment is unpublished.
    rupesh sharma · 4 years ago
    Thank you, working good.
  • To post as a guest, your comment is unpublished.
    sudha · 5 years ago
    Thank you a lot....to show the weekday formula.
  • To post as a guest, your comment is unpublished.
    sudha · 5 years ago
    Nice formula... working good
  • To post as a guest, your comment is unpublished.
    Charlie · 5 years ago
    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"?..
    • To post as a guest, your comment is unpublished.
      Geno · 5 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Charlie · 4 years ago
        [quote name="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"?..[/quote]
        [quote name="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.[/quote]

        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.
  • To post as a guest, your comment is unpublished.
    janey · 5 years ago
    or just =TEXT(WEEKDAY(A1),"dddd")
  • To post as a guest, your comment is unpublished.
    janey · 5 years ago
    for weekday you may use =TEXT(A1,"[$-14409]dddd")and you get Friday displayed. No need that Kutools
  • To post as a guest, your comment is unpublished.
    Girish · 5 years ago
    thanks for the formula.. very nice one :-)
  • To post as a guest, your comment is unpublished.
    Hemant · 5 years ago
    Good Job .... Great .....it solve my issues in seconds
  • To post as a guest, your comment is unpublished.
    Smarty · 5 years ago
    Thanks a lot :)
    Really helpful.
  • To post as a guest, your comment is unpublished.
    John · 5 years ago
    Thank you, this saved me HOURS of time!!!!!
  • To post as a guest, your comment is unpublished.
    hey 1990 · 5 years ago
    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
  • To post as a guest, your comment is unpublished.
    PAttie · 5 years ago
    My goodness, it's working now....sorry!
  • To post as a guest, your comment is unpublished.
    PAttie · 5 years ago
    I entered the text formula but it did not work...help?
  • To post as a guest, your comment is unpublished.
    Pankaj Sanwaria · 5 years ago
    Thanks a lot for formula =TEXT(A1,"mmmm")to convert date in text.
  • To post as a guest, your comment is unpublished.
    Pankaj Sanwaria · 5 years ago
    Thanks a lot for formula (=Text(Cell),"mmmm").
  • To post as a guest, your comment is unpublished.
    subhash · 5 years ago
    Thanks, very much effective formula
    • To post as a guest, your comment is unpublished.
      Jacques Limbi · 2 years ago
      Fantastic! I worked very well for me aswell. Really appreciate your help