Cellwise Try it free

Excel guide · 3 min read

How to get the day of the week from a date in Excel

Quick answerUse =TEXT(A2,"dddd") for the full day name (Monday) or =TEXT(A2,"ddd") for the short one (Mon). If you need a number for calculations, =WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday. To show the day name but keep the real date in the cell, use a custom number format of dddd instead of a formula.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

AB
1DateSales
2Nov 2, 20261840
3Nov 5, 20262210
4Nov 6, 20262975
5Nov 7, 20263620
6Nov 8, 20263105
7Nov 10, 20261690
8Nov 14, 20263480

Download this sheet as an Excel practice file (.xlsx)

Full day name with TEXT

These are daily sales for a small café. TEXT turns the date into text using a format code: dddd is the full day name. It works in every version of Excel and on Mac.

The result is text, not a date. That is fine for labels, reports and grouping, but you cannot do date math on it.

=TEXT(A2,"dddd")

Result: Monday

Monday: November 2, 2026 fell on a Monday.

Short day name, or day and date together

Three d's give the short name, which fits narrow columns and chart labels. You can mix codes in one format to build a readable label in a single step.

=TEXT(A5,"ddd")&" "&TEXT(A5,"mmm d")

Result: Sat Nov 7

Sat Nov 7: the short day name followed by the month and day.

Keep the date, just show the day (custom format)

If the column should stay a real date (so it still sorts, filters and feeds other formulas), change how it looks instead of converting it. The cell keeps its value and simply displays the day name.

  1. Select the date cells.
  2. Press Ctrl+1 (Cmd+1 on Mac) to open Format Cells.
  3. Choose Custom and type dddd (or ddd, or ddd d mmm for "Sat 7 Nov").
  4. Click OK. The formula bar still shows the full date.

Weekday number with WEEKDAY

WEEKDAY returns a number, which is what you want for IF tests and counting. The second argument picks the numbering. With no second argument, Sunday is 1 and Saturday is 7 (the US default). With 2, Monday is 1 and Sunday is 7, which makes "weekend" simply anything above 5.

=IF(WEEKDAY(A5,2)>5,"Weekend","Weekday")

Result: Weekend

Weekend: November 7, 2026 is a Saturday, so WEEKDAY(A5,2) returns 6.

Total sales by day of the week

To compare Saturdays with weekdays, test the day name inside SUMPRODUCT. It checks every date, keeps the matching rows and adds their sales, with no helper column. It works in all versions without Ctrl+Shift+Enter.

For a full breakdown, add a helper column with =TEXT(A2,"ddd") and build a PivotTable or a SUMIF on that column.

=SUMPRODUCT((TEXT(A2:A8,"ddd")="Sat")*B2:B8)

Result: 7100

7,100: the two Saturdays, 3,620 and 3,480, added together.

Count weekend days in a list

The same idea counts rows. The double minus turns TRUE and FALSE into 1 and 0 so SUMPRODUCT can add them.

=SUMPRODUCT(--(WEEKDAY(A2:A8,2)>5))

Result: 3

3 of the 7 dates fall on a Saturday or Sunday.

Common mistakes

WEEKDAY says 6 for a Friday

WEEKDAY(A2) counts from Sunday = 1. If you think of Monday as day 1, always add the second argument: WEEKDAY(A2,2).

TEXT returns the same date instead of a day name

The date is stored as text (for example imported from a CSV), so TEXT has nothing to format. Convert the column to real dates first with Data > Text to Columns > Finish, or DATEVALUE.

Day names come out in another language

TEXT uses the language of your Windows or Mac regional settings, and some languages use different codes (German Excel uses TTTT for the day name). Use the codes for your language, or a custom number format, which adapts automatically.

Sorting by day name gives Friday, Monday, Saturday...

Text sorts alphabetically. Sort by a WEEKDAY(A2,2) helper column instead, or use Data > Sort > Order > Custom List and pick the Monday-to-Sunday list.

FAQ

How do I convert a date to a day of the week in Excel?

Use =TEXT(A2,"dddd") for Monday or =TEXT(A2,"ddd") for Mon. To keep the date and only change the display, apply the custom number format dddd.

What does WEEKDAY return in Excel?

A number from 1 to 7. By default 1 is Sunday; with WEEKDAY(date,2), 1 is Monday and 7 is Sunday.

How do I check if a date is a weekend in Excel?

Use =WEEKDAY(A2,2)>5. It returns TRUE for Saturday and Sunday, and you can wrap it in IF to show a label.

Can I get the day name without a formula?

Yes. Select the dates, press Ctrl+1, choose Custom and enter dddd. The cell still holds the full date but displays the day name.

Functions used

Related guides

Updated