Get the Day Name From a Date in Excel

To get the day name from a date in Excel, use the TEXT function with the format code "dddd" for the full name or "ddd" for the short one. =TEXT(A2,"dddd") turns 15/03/2025 into Saturday. This guide also shows how to display the day name without losing the date, how to make your own labels, and how to count dates by day of the week.

How to get the day name with TEXT#

Use =TEXT(date,"dddd"). TEXT takes a value and a format code and returns the value as text in that style. The number of d's sets what you get:

So for delivery dates in column B:

=TEXT(B2,"dddd")

Fill it down (Windows: Ctrl+D; Mac: ⌘D):

One and two d's give the day of the month as a number. Three and four give the weekday name. The TEXT function guide lists the other format codes.

How to show the day name and keep the date#

Apply a custom number format instead of a formula. The cell keeps its real date for sorting and maths, but displays the day name too:

1. Select the date cells.
2. Open Format Cells (Windows: Ctrl+1; Mac: ⌘1).
3. Choose Custom and type a format in the Type box.
4. Click OK.

Some useful formats:

This is the better choice in schedules and timesheets, where people want to see the day but you still need real dates underneath. Use TEXT when you need the day name as separate text, for example to filter or count by it.

How to make custom day labels with CHOOSE and WEEKDAY#

Use CHOOSE with WEEKDAY when you want your own labels. WEEKDAY returns a number for the day of the week, and CHOOSE picks a label by that number:

=CHOOSE(WEEKDAY(B2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

With its default setting, WEEKDAY returns 1 for Sunday through 7 for Saturday, so the labels start with Sunday. For 15 March 2025, WEEKDAY returns 7 and CHOOSE picks Sat.

This approach helps in two cases. First, you can use any labels you like, such as "Weekend" or "Team A". Second, the labels don't depend on Excel's language, while TEXT returns day names in the language of the computer. The article on how the WEEKDAY function works explains its return types in detail.

How to turn a weekday number into a name#

If a cell holds a weekday number from 1 to 7, where 1 is Sunday, TEXT can name it directly:

=TEXT(B2,"dddd")

This works because Excel's calendar starts on a Sunday: the number 1 is the first day of its calendar, and Excel shows that day as a Sunday. So 1 gives Sunday, 2 gives Monday, and so on to 7 for Saturday.

If your numbers start with Monday as 1, this trick is off by one day. Use CHOOSE with your own list instead, starting with "Mon".

How to count dates by day of the week#

Use SUMPRODUCT with TEXT to count how many dates fall on a given day. To count Mondays in B2:B5:

=SUMPRODUCT(--(TEXT(B2:B5,"ddd")="Mon"))

TEXT turns each date into a short day name, the comparison returns TRUE or FALSE for each row, and -- turns those into 1s and 0s. SUMPRODUCT adds them up. On the delivery table, the result is 2.

To count weekends, compare the weekday number instead: =SUMPRODUCT(--(WEEKDAY(B2:B5,2)>5)) returns 1, for the Saturday delivery. With return type 2, Monday is 1 and Sunday is 7, so anything above 5 is a weekend.

The same idea helps with month labels. See getting the month name from a date for those, and getting the quarter from a date for quarterly reports.

How to label weekends and today's day#

Wrap WEEKDAY in IF to turn the day into a simple Weekend or Weekday label. This is often more useful than the name itself, for example in a delivery or shift schedule:

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

For 15 March 2025, a Saturday, the result is Weekend. To colour weekend rows instead of labelling them, use the same test in a conditional formatting rule.

For the name of the current day, put TODAY inside TEXT: =TEXT(TODAY(),"dddd"). It updates by itself each day, which is handy at the top of a daily report or checklist.

Common mistakes with day names#

Most day-name problems come from the data, not the formula. Check these:

1. Empty cells show Saturday. An empty cell counts as 0, which Excel shows as a Saturday. Use =IF(B2="","",TEXT(B2,"dddd")).
2. Dates stored as text. TEXT can't read a text date, so it returns it unchanged. Convert it to a real date first.
3. Wrong language. TEXT follows Excel's language, so a shared file may show other day names on another computer. Use CHOOSE for fixed labels.
4. Sorting by name. Day names sort alphabetically: Friday, Monday, Saturday. Sort by the date or by WEEKDAY instead.

If you're planning ahead, you can combine these with date maths. For example, the guide to adding days, months and years shows how to move a date forward, and TEXT then tells you which day it lands on.

Practise it#

Day names are quick to learn and often appear in scheduling tasks in job tests. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you type the formula, and it checks your answer instantly. Or try the exercise on this page first.