Skip to main content

How to convert date to weekday, month, year name or number in Excel?

Author: Kelly Last Modified: 2023-12-11

Suppose you enter a date in a cell, and it displays as 12/13/2015. Is there a way to show just the month or the weekday, or perhaps the text of the month name or weekday name, such as "December", or "Sunday"? The methods outlined below can help you easily convert or format any kind 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

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 in Excel.

1. Select the date cells that you want to convert to day of the 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% of Problems in Excel
Free Trial Now

Say goodbye to tiring VBA and formulas!

2. In the opening Format Cells dialog box, under the Number tab, click Custom in the Category box, and then enter "ddd" into the Type box.

Note: The "ddd" will display dates as a short weekday name, such as "Sat". Take the date 3/7/2019 for example, the following table illustrates various custom date formatting options:

  A B C
1 Display "3/7/2019" as Format Code Formatted Result
2 Weekday (Name) ddd Thu
3 dddd Thursday
4 Month (Name) mmm Mar
5 mmmm March
6 Month (Number) m 3
7 mm 03
8 Year (Number) yy 19
9 yyyy 2019
10 Day of the Month (Number) d 7
11 dd 07

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

Are you still manually converting dates to their corresponding day of the week by right-clicking and specifying a formatting code in the Format Cells dialog? With Kutools for Excel's Apply Date Formatting feature, you can effortlessly display a series of dates as month names or days of the week with just 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 convert a date to its corresponding month name or weekday name easily using a specified formatting code.

Please enter the formula =TEXT(A2,"mmmm") in a blank cell (cell C2 in our case), and press the Enter key. Then drag this cell's fill handle to apply the formula to the range as you need.

The dates are converted to their corresponding month names immediately. See screenshot:

Tip: You can change the "mmmm" to other format codes according to the above format code table. For example, you can also convert a date to the weekday name with the formula =TEXT(A2,"dddd").

Want to stand out? Boost your expertise with 30+ Excel date features now!

Enhance your Excel skills with Kutools' 30+ date features! Gain practical date handling expertise in just 3 minutes, outpace your colleagues, and secure raises and promotions with ease!

To Boost Likeability

Efficiently solve Excel date issues, gaining recognition and appreciation at work.

For Family Time

Say goodbye to repetitive and trivial date tasks in Excel, saving more time for family moments.

For a Healthier Life

Bulk insert, modify, or calculate dates with ease, minimizing daily clicks and avoiding mouse strain.

Never Worry about Layoffs

Boost work efficiency by 91%, solve 95% of Excel date problems, and complete tasks early.

Ease Your Mind

Use Kutools' 13 date formulas, forget complex formulas and VBA codes, and simplify your work.

Get 300+ powerful Kutools for Excel tools for over 1500 work scenarios at just $49.0 - a value surpassing $4000 in traditional Excel training, saving you significantly.

Convert a date to weekday/month name with CHOOSE function

If you find it challenging to remember and use these specific formatting codes in formulas, Excel also allows you to utilize the CHOOSE function for converting dates to month names or weekday names. Here’s how you can do it:

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 in cell B2 into the day of the week as below screenshot shown.

Tip: 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

While the TEXT function in Excel efficiently converts dates to a format you want, it's less convenient when dates are scattered across the sheet. In such cases, Kutools for Excel's Apply Date Formatting tool offers a superior solution, enabling you to handle multiple selections with just a few clicks and easily convert all dates in the selections to their corresponding month or weekday names.

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 one or multiple ranges containing dates you will work with, and click Kutools > Format > Apply Date Formatting.

2. In the Apply Date formatting dialog box, please select the date formatting in the Date formatting box, and click the Ok button.

Tips:
  • By selecting 03, Mar or March in the Date formatting box, you can convert the dates to month names or month numbers.
  • By selecting Wed or Wednesday, you can convert the dates to weekday names.
  • By selecting 01 or 2001, you can convert dates to the year numbers.
  • By selecting 14, you can convert dates to day numbers.

Now, all the selected dates have been transformed into your specified date format, be it month names, days of the week, or any other format you choose.

Notes:

  • The Apply Date Formatting tool modifies the display format without altering the actual values, and you can easily revert the changes by pressing Ctrl + Z to undo.
  • Want to access the Apply Date Formatting feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Related articles

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (53)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
please help me with formula to show week 1,2,34 for the date mentioned in coloumn
This comment was minimized by the moderator on the site
Can you help me with a coloumn holding the date and in another coloumn it should get identified as week 1
This comment was minimized by the moderator on the site
gostaria de uma maneira (fç) para formatar uma data pelo formato de número da semana.
Por exemplo: 04/10/22 seria a W40 ou 40a. semana do ano.
obg
This comment was minimized by the moderator on the site
Hi there,

Suppose the date is in the cell A1, you can enter the formula in another cell to get the week number from the given date quickly: =WEEKNUM(A1)
If you want to add the date in the formula, you can use the following: =WEEKNUM(DATE(2022,10,4))

Amanda
This comment was minimized by the moderator on the site
In Cell 1, Month is mentioned in Text format and how do I find the number of days for the same month in Cell 2
This comment was minimized by the moderator on the site
HOW DO I CONVERT 2ND MONDAY 2019 TO A DATE?
This comment was minimized by the moderator on the site
Suppose today is 25.10.19 that is Friday and I have to make any kind of statement in excel where I need every day to enter previous day date or you can say one day back date that is 24.10.19 which is Thursday in one of the cell in excel. So I tried for date one formula that is =Today()-1 ,so it becomes 24.10.19 but I don't know how to put formula for weekly day ,Can Anyone help me out
This comment was minimized by the moderator on the site
=CHOOSE(WEEKDAY(TODAY()-1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
This comment was minimized by the moderator on the site
if I have 4th Thursday of Dec 2019, how would I calculate the date in excel , what will be the formulla
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi Saran,
You can use Kutools formula – Add minutes to date: add 810 minutes (13.5 hours) to the IST time, and get the PST time.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi,
Both =TEXT(A1,"dddd") and =TEXT(A1,"ddd") can convert the dates with time to days of week. Try them!
This comment was minimized by the moderator on the site
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!!
This comment was minimized by the moderator on the site
Hi,

you can try this formula =IF(DAY(A1)>25,TEXT(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),"MMMM"),TEXT(A1,"MMMM"))
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations