Excel Return Day of Week Based on Date: 3 Easy Ways
To return the day of the week from a date in Excel, use the TEXT function with a format like =TEXT(A1,"dddd") for the full name or =TEXT(A1,"ddd") for the abbreviated name. Alternatively, the WEEKDAY function returns a number (1–7) that you can pair with a lookup table to display the name. This article explains these methods, how to choose between them, and how to handle common issues like blank cells or regional date formats.
Why Excel Needs a Special Function for Weekdays
In Excel, dates are stored as serial numbers—each day is a whole number counting from January 1, 1900 (with the well-known 1900 leap year bug that Excel intentionally retains for compatibility). For example, January 1, 2024, is serial number 45292. The day of the week is not stored directly; it must be derived from the serial number using a function or custom number format. This is why you cannot simply read a “day� value from a date cell—you must ask Excel to interpret the serial number as a weekday.
Method 1: TEXT Function for Day Names
The simplest way to display a day name is the TEXT function, which converts a number to text using a format code. To get the full day name (e.g., “Monday�), use:
=TEXT(A1,"dddd")
To get the abbreviated name (e.g., “Mon�), use:
=TEXT(A1,"ddd")
The TEXT function returns a text string, so the result cannot be used in further date calculations. It is ideal for reports, labels, or when you need a static display. Note that the result depends on your system’s language settings—if Excel is in Spanish, dddd will return “lunes,� not “Monday.�
Method 2: WEEKDAY Function and Lookup
The WEEKDAY function returns a number from 1 to 7 representing the day of the week. By default, Sunday=1 and Saturday=7. You can change the return type with a second argument; for example, =WEEKDAY(A1,2) makes Monday=1 and Sunday=7 (the ISO standard). To convert that number to a name, use a lookup table or an IF chain. A clean approach is to create a small table on a worksheet:
- Column A: 1, 2, 3, 4, 5, 6, 7 (the numbers from WEEKDAY with default type)
- Column B: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Then use =VLOOKUP(WEEKDAY(A1),$D$1:$E$7,2,FALSE). This method is more flexible if you need to customize names (e.g., “Sun,� “Sun.�, or “Sunday�) or if you need to use the day number in calculations like counting weekdays.
Method 3: Custom Number Format (Without a Formula)
If you only need to see the day name in the same cell (without changing the underlying date), you can apply a custom number format. Right-click the cell, choose Format Cells, go to the Number tab, select Custom, and enter dddd in the Type box. The cell will display “Monday� but still contain the full date serial number. This is useful when you want to keep the date for sorting or calculations but change its appearance. However, the displayed text is not a real string—if you reference the cell in a formula, you get the serial number, not the day name.
How to Choose the Right Method
Your choice depends on what you need the result for:
- Display only: Use a custom number format (no formula) or the TEXT function if you need the result in a separate cell.
- Further calculations: Use WEEKDAY to get a number, then use that number in logic (e.g., to flag weekends with
=IF(WEEKDAY(A1,2)>5,"Weekend","Weekday")). - Localized names: TEXT follows your system language; for a specific language, use WEEKDAY with a lookup table containing your own names.
Common Problems and Solutions
Problem: The result shows a number like 3 instead of “Tuesday.� That happens when you use WEEKDAY alone. Wrap it in a lookup or use TEXT.
Problem: The day name is in a different language. TEXT uses your Excel language. To force English, use a lookup table with WEEKDAY.
Problem: The cell shows “#VALUE!� This occurs if the cell contains text that Excel cannot recognize as a date. Ensure the cell is actually a date, not a string. Use =ISDATE(A1) to check (in newer Excel versions).
Problem: The date is in a different system (1904 date system). Some Mac versions use the 1904 system. The WEEKDAY and TEXT functions still work, but the serial number basis differs. You can check by going to File > Options > Advanced and looking for “Use 1904 date system.�
Frequently Asked Questions
How do I get the day of the week as a number in Excel?
Use the WEEKDAY function: =WEEKDAY(A1) returns 1 for Sunday, 2 for Monday, etc. To make Monday=1, use =WEEKDAY(A1,2). The second argument is the return type; there are several, but type 2 is the most common for a standard Monday-first week.
Can I use CHOOSE with WEEKDAY to get the day name?
Yes. =CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") works because WEEKDAY returns 1–7. You can replace the names with any text you like. This is a compact alternative to a lookup table.
How do I show the day of the week for today’s date?
Use =TEXT(TODAY(),"dddd") to display the full name of today’s day. The TODAY function returns the current date, and TEXT formats it. This updates automatically whenever the worksheet recalculates.
Why does my TEXT function return “#VALUE!�?
This usually means the cell does not contain a valid date. If the cell contains text like “January 5, 2024� that Excel does not recognize as a date, TEXT cannot format it. Convert the text to a date using DATEVALUE, or ensure the cell is formatted as a date.
Is there a way to return the day of the week without a formula?
Yes. Use a custom number format. Select the cell, press Ctrl+1, go to Number > Custom, and type dddd in the Type box. The cell will display the full day name, but the underlying value remains the date serial number. This is not a formula, so it does not recalculate—it changes only the display.
How do I count weekdays between two dates?
Use the NETWORKDAYS function: =NETWORKDAYS(start_date, end_date) returns the number of working days (Monday–Friday) between two dates, excluding weekends and optionally holidays. For a custom definition of “weekday,� use the WEEKDAY function in a SUMPRODUCT formula.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.