To get the day of the week for a date, such as Tuesday for 28-Dec-1937, use =TEXT(B3,"dddd"). Here are four ways: two formulas, a number format that shows the day without changing the date, and Power Query, plus day names in Malayalam and Hindi.
Table of Contents
1. TEXT function
TEXT turns a date into text in any format. "dddd" gives the full day name and "ddd" the first three letters:
=TEXT(B3,"dddd")
28-Dec-1937 was a Tuesday, and 15-Aug-1947 a Friday. The result is text, so you can join it to other text: ="Delivery on "&TEXT(B3,"dddd").
How the formula works
- Excel stores every date as a number, so a date has a weekday built in.
TEXT(B3,"dddd")reads that weekday and writes it as a word in your language settings.WEEKDAYgives the same information as a number, which is easier to test in formulas, for example=WEEKDAY(B3,2)>5is TRUE for Saturday and Sunday.
2. WEEKDAY and CHOOSE
WEEKDAY returns a number from 1 (Sunday) to 7 (Saturday). CHOOSE then picks a name from a list you write, so you control exactly what appears:
=CHOOSE(WEEKDAY(B3),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
Use this when you want your own labels, such as Mon or Weekend: =CHOOSE(WEEKDAY(B3),"Weekend","Weekday","Weekday","Weekday","Weekday","Weekday","Weekend").
3. Show the day name with a number format
To keep the real date in the cell but display the day, select the dates, press Ctrl + 1, choose Custom, and type a format:
| Format | 28-Dec-1937 shows as |
|---|---|
dddd |
Tuesday |
ddd |
Tue |
dddd, dd-mmm-yyyy |
Tuesday, 28-Dec-1937 |
ddd dd/mm |
Tue 28/12 |
Because the value is still a date, you can sort, filter and calculate with it.
Day names in Malayalam, Hindi and Tamil
Add a language code to the TEXT format:
| Language | Formula |
|---|---|
| Malayalam | =TEXT(B3,"[$-44C]dddd") |
| Hindi | =TEXT(B3,"[$-439]dddd") |
| Tamil | =TEXT(B3,"[$-449]dddd") |
| Arabic | =TEXT(B3,"[$-401]dddd") |
The code inside [$-…] is the Windows language ID. It works the same way in a custom number format.
4. Power Query
To add day names to a large table:
| Step | Do this |
|---|---|
| 1 | Select the table and go to Data → From Table/Range. |
| 2 | Select the date column, then Add Column → Date → Day → Name of Day. |
| 3 | Click Home → Close & Load. |
Use Transform → Date → Day → Name of Day instead to replace the dates with day names. Learn more in the Power Query Editor guide.
Notes
- TEXT returns text, so a day name can’t be used as a date afterwards. Keep the original date column.
- WEEKDAY’s second argument changes the numbering:
=WEEKDAY(B3,2)makes Monday 1 and Sunday 7. - To count how many Mondays fall between two dates, see NETWORKDAYS.INTL.
Common errors
| Problem | Cause | Fix |
|---|---|---|
#VALUE! |
The date is stored as text | Convert it with DATEVALUE or Text to Columns |
| Same day for every row | The format is "DDDD" on a text date |
Check the cells are real dates (right-aligned) |
| Number instead of a name | WEEKDAY used on its own | Wrap it in CHOOSE, or use TEXT |
Practice file
Both formula methods and the number format, with the Malayalam and Hindi day-name formulas.