=TEXT(A2,"dddd") returns the day name of the date in A2, such as Monday. To get the day as a number, =WEEKDAY(A2) returns 1 for Sunday through 7 for Saturday.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Day | WEEKDAY |
| 2 | 2026-03-02 | Monday | 2 |
| 3 | 2026-03-06 | Friday | 6 |
| 4 | 2026-03-07 | Saturday | 7 |
| 5 | 2026-03-08 | Sunday | 1 |
| 6 | 2026-12-25 | Friday | 6 |
2026-03-02 is a Monday, so WEEKDAY gives 2 (Sunday counts as 1). Christmas 2026 falls on a Friday. Change any date in column A and the day name follows. TEXT returns text, which is right for labels and reports; use WEEKDAY when the day feeds a calculation, such as a weekend check.
Short day names and other formats
The format code decides how much of the name you get: "dddd" is the full name, "ddd" the first three letters, and "dd" or "d" the day of the month instead. Combine codes to write a full date in words.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | dddd | ddd | dddd, mmmm d |
| 2 | 2026-03-02 | Monday | Mon | Monday, March 2 |
| 3 | 2026-07-04 | Saturday | Sat | Saturday, July 4 |
| 4 | 2026-10-31 | Saturday | Sat | Saturday, October 31 |
The day names follow the language of your Excel: German Excel writes Montag and uses "TTTT" as the code. Japanese Excel is the exception: "dddd" still returns Monday, and the Japanese names have codes of their own. =TEXT(A2,"aaa") shows 月 and =TEXT(A2,"aaaa") shows 月曜日, and =TEXT(A2,"m/d(aaa)") gives the usual Japanese label, 3/2(月). The same codes work in a custom number format.
To keep the date in the cell and only display the day, use a number format instead of a formula: Home > Number Format > More Number Formats > Custom, type dddd or ddd yyyy-mm-dd. The cell still holds the date, so sums, lookups and sorting keep working.
WEEKDAY function and return types
WEEKDAY(serial_number, [return_type]) returns the position of the day in the week. The second argument decides where the week starts.
| return_type | Numbers |
|---|---|
| 1 or omitted | Sunday 1 to Saturday 7 |
| 2 | Monday 1 to Sunday 7 |
| 3 | Monday 0 to Sunday 6 |
| 11 | Monday 1 to Sunday 7 (same as 2) |
| 12 to 17 | Tuesday 1 to Monday 7, and so on, up to 17: Sunday 1 to Saturday 7 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Type 1 | Type 2 | Type 3 |
| 2 | 2026-03-02 | 2 | 1 | 0 |
| 3 | 2026-03-06 | 6 | 5 | 4 |
| 4 | 2026-03-07 | 7 | 6 | 5 |
| 5 | 2026-03-08 | 1 | 7 | 6 |
For Monday, March 2, the three types give 2, 1 and 0. For Sunday, March 8, they give 1, 7 and 6. Type 2 is the one most people want outside the US: Monday is 1, and Saturday and Sunday are the only days above 5.
Check if a date is a weekend
With type 2, Saturday is 6 and Sunday is 7, so WEEKDAY(A2,2)>5 is TRUE on weekends. Put it in IF for a label, or use it as a conditional formatting rule (Home > Conditional Formatting > New Rule > Use a formula) to shade weekend rows.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Weekend? | Label |
| 2 | 2026-03-05 | FALSE | Weekday |
| 3 | 2026-03-06 | FALSE | Weekday |
| 4 | 2026-03-07 | TRUE | Weekend |
| 5 | 2026-03-08 | TRUE | Weekend |
| 6 | 2026-03-09 | FALSE | Weekday |
| 7 | 2026-03-10 | FALSE | Weekday |
The rows for Saturday, March 7 and Sunday, March 8 are highlighted. The $ before A in the rule keeps every column of a row reading the date in column A. To count the weekdays between two dates instead of checking them one by one, use NETWORKDAYS: see NETWORKDAYS and WORKDAY.
Turn the day number into your own labels
When you want labels TEXT cannot produce, such as Mo and Tu or shift names, give WEEKDAY's number to CHOOSE, which picks the item at that position: =CHOOSE(WEEKDAY(A2,2),"Mo","Tu","We","Th","Fr","Sa","Su"). With type 2 the list starts at Monday, so a Wednesday returns We.
Practice: day names and weekends
| A | B | |
|---|---|---|
| 1 | Date | Day |
| 2 | 2026-04-16 |
Your turn: In B2, show the full name of the day of the date in A2, for example Monday.
| A | B | |
|---|---|---|
| 1 | Date | Weekend? |
| 2 | 2026-04-16 | |
| 3 | 2026-04-17 | |
| 4 | 2026-04-18 | |
| 5 | 2026-04-19 |
Your turn: In B2, return TRUE if the date in A2 falls on a Saturday or Sunday, and FALSE otherwise, then fill it down to B5.
Why TEXT returns the wrong day
If column A holds the day of the month (1, 2, 15) instead of a full date, TEXT reads each number as a date in January 1900, the start of Excel's calendar, and returns day names that have nothing to do with your month.
| A | B | C | |
|---|---|---|---|
| 1 | Typed | TEXT | Built with DATE |
| 2 | 15 | Sunday | Wednesday |
15 is read as January 15, 1900, which Excel calls a Sunday. Build the real date with DATE(2026,4,A2) and April 15, 2026 turns out to be a Wednesday. The same thing happens with dates stored as text: check with =ISNUMBER(A2), which is TRUE for a real date.
Frequently Asked Questions
How do I get the day of the week from a date in Excel?
=TEXT(A2,"dddd") returns the full name (Monday), =TEXT(A2,"ddd") the short one (Mon). For a number, use =WEEKDAY(A2,2), which counts Monday as 1 and Sunday as 7.
What does WEEKDAY return in Excel?
A number from 1 to 7. With no second argument, Sunday is 1 and Saturday is 7. WEEKDAY(A2,2) makes Monday 1 and Sunday 7, and WEEKDAY(A2,3) makes Monday 0 and Sunday 6.
How do I check if a date is a weekend in Excel?
=WEEKDAY(A2,2)>5 returns TRUE for Saturday and Sunday. Wrap it in IF for a label: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday").
How do I show the date and the day name in the same cell?
Give the cell a custom number format: Home > Number Format > More Number Formats > Custom, and type ddd yyyy-mm-dd. The cell still holds the date, so formulas that read it keep working.
Why does TEXT return the wrong day name?
The cells hold day numbers (1 to 31) or text, not dates. TEXT reads the number 15 as day 15 of Excel's calendar, January 15, 1900, which Excel shows as a Sunday. Use real dates, or build them with DATE.
