excel get day name from date
In this tutorial, it provides formulas to return the day name from the given date in Excel.
If you want to follow along with this tutorial, please download the example spreadsheet.
Get General Day Name with TEXT Function
Generic formula:
Find the earliest date of each group
| TEXT(date,”day_format”) |
Syntaxt and Arguments
| Date: the date that you want to get the day name from. |
| Day_format: the day format that you want to display in the result in. |
Day format list
|
Day format
|
Display (take 6/03/2019 for instance)
|
|
"d"
|
3
|
|
"dd"
|
03
|
|
"ddd"
|
Mon
|
|
"dddd"
|
Monday
|
How this formula works
To get the day name from the date in cell B3, please use below formula:
Find the earliest date
| =TEXT(B3,"ddd") |
Press Enter key to get the result.
Explanation
TEXT function: converts number to text with a specific format. In Excel, the date is stored as number.
Get Custom Day Name with CHOOSE and WEEKDAY Function
Generic Formula
| CHOOSE(WEEKDAY(date),"Sun_format","Mon_format","Tue_format","Wed_format", "Thu_format","Fri_format","Sat_format") |
Syntax and Arguments
| Date: the date that you want to get the day name from. |
| Sun_format, Mon_format…Sat_format: the weekday format that you want to display the result in. |
How the formula works
Supposing you want to display the day name in these formats:
|
Sunday > su |
|
Monday > mo |
|
Tuesday > tu |
|
Wednesday > we |
|
Thursday > th |
|
Friday > fr |
|
Saturday > sa |
Please use below formula to get the day name from the date in cell B4:
| =CHOOSE(WEEKDAY(B4),"su","mo","tu","we","th","fr","sa") |
Press Enter key to get the result.
Explanation
WEEKDAY function: gets day of the week as a number (1 to 7) from the given date.
Weekday number list
| Weekday | Number |
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
CHOOSE function: returns a value from the list of value arguments by the given index number.
=CHOOSE(WEEKDAY(B4),"su","mo","tu","we","th","fr","sa")
=CHOOSE(3, "su","mo","tu","we","th","fr","sa")
=tu
Relative Formulas
- Count days of month
This tutorial provides formulas to count the total days of the month based on given date. - Count days until expiration date
To count the days between today and each expiration date in Excel by formula - Display current date or time
This tutorial provides formulas to show the current date or time in Excel. - Create weekly date range
In this tutorial, it provides a formula to quickly get the weekly date range in Excel.
Relative Functions
- Excel WEEKNUM Function
Returns the week number of the given date in a year - Excel WORKDAY Function
Adds workdays to the given start date and returns a workday - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel NOW Function
Get the current time and date
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
