Yes, Excel can show the weekday for any date. The quickest way is to use the TEXT function: =TEXT(A2,"dddd") returns the full weekday name (e.g., Monday), and =TEXT(A2,"ddd") returns the abbreviated name (e.g., Mon). If you need the weekday as a number (1=Sunday, 2=Monday, etc.), use =WEEKDAY(A2). For a more robust, locale-independent approach, use =CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"). This article explains these methods in detail, plus how to handle blank cells, custom formats, and common pitfalls.
Why You Might Need the Weekday
Knowing the weekday is useful for scheduling, reporting, and data analysis. For example, you might want to filter sales data by day of the week, calculate the number of Mondays in a month, or create a Gantt chart that highlights weekends. Excel stores dates as serial numbers (1 = January 1, 1900), so you can use any date function on them. The weekday functions and formats work on any valid Excel date, whether it’s typed, referenced, or calculated.
Method 1: The TEXT Function (Fastest for Display)
The TEXT function converts a value to text in a specified format. It’s ideal for creating a readable weekday label next to a date.
- Full weekday name:
=TEXT(A2,"dddd")→ returns “Monday” - Abbreviated name:
=TEXT(A2,"ddd")→ returns “Mon” - Two-digit weekday number (e.g., 01 for Sunday):
=TEXT(A2,"dd")→ returns “01” (but this is rarely used)
Example: With the date 2025-03-17 in cell A2, =TEXT(A2,"dddd") returns “Monday”.
Note: The result of TEXT is text, not a date. If you later need to sort or perform date arithmetic, keep the original date in a separate column.
Method 2: The WEEKDAY Function (For Numbers and Logic)
The WEEKDAY function returns a number representing the day of the week. By default, Sunday=1, Monday=2, …, Saturday=7. You can change the return type with the second argument.
=WEEKDAY(A2)→ 1 for Sunday, 7 for Saturday (default)=WEEKDAY(A2,2)→ Monday=1, Sunday=7 (ISO standard)=WEEKDAY(A2,3)→ Monday=0, Sunday=6
Example: For 2025-03-17 (a Monday), =WEEKDAY(A2,2) returns 1.
Use this number in formulas, such as =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") to flag weekends.
Method 3: Custom Number Format (Changes the Cell Display)
If you don’t want a separate column, you can format the date cell itself to show the weekday. This does not change the underlying value—the date remains a real date.
- Select the cell(s) with dates.
- Press Ctrl+1 (or Cmd+1 on Mac) to open Format Cells.
- Go to the Number tab, choose Custom.
- In the Type box, enter one of these:
dddd→ shows full weekday name (e.g., Monday)ddd→ shows abbreviated name (e.g., Mon)dddd, mm/dd/yyyy→ shows “Monday, 03/17/2025”
- Click OK.
This method is great for reports where you want the date to appear as “Monday, March 17, 2025” without extra columns.
Method 4: CHOOSE with WEEKDAY (Custom Names, Any Language)
If you need weekday names in another language or custom abbreviations, combine CHOOSE with WEEKDAY. This works in all Excel versions.
Assuming the date is in A2, enter:
=CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
This returns “Sun” for Sunday, “Mon” for Monday, etc. You can replace the names with any text, e.g., Spanish: =CHOOSE(WEEKDAY(A2),"Dom","Lun","Mar","Mié","Jue","Vie","Sáb").
For the full name in another language, you’d need a more complex formula or a lookup table, but the TEXT function with a locale code (like =TEXT(A2,"[$-es-ES]dddd")) works in Excel 365 and 2021.
Handling Blank Cells and Errors
If you apply these formulas to empty cells, you’ll get errors or unwanted results. To avoid that, wrap the formula in an IF check:
=IF(A2="","",TEXT(A2,"dddd"))
This returns a blank if A2 is empty. For WEEKDAY, use =IF(A2="","",WEEKDAY(A2,2)).
If your dates are actually text (e.g., “March 17, 2025”), Excel may not recognize them as dates. Use DATEVALUE to convert: =TEXT(DATEVALUE(A2),"dddd"). But note that DATEVALUE depends on your system’s date settings.
Common Problems and Solutions
- Problem: The formula returns a number instead of a name.
Solution: You’re usingWEEKDAYwhich returns a number. UseTEXTorCHOOSEto get a name. - Problem: The weekday is off by one day.
Solution: Check thereturn_typeinWEEKDAY. Different systems use different conventions. Use2for Monday=1 to match ISO standards. - Problem: The date shows as a number like 45000.
Solution: The cell is formatted as General. Apply a date format via Format Cells. - Problem: The
TEXTfunction returns a #VALUE! error.
Solution: The cell is not a valid date. Ensure it’s a date serial number or a date string recognized by Excel.
Frequently Asked Questions
How do I get the weekday as a number in Excel?
Use the WEEKDAY function. For example, =WEEKDAY(A2) returns 1 for Sunday, 2 for Monday, etc. To make Monday=1, use =WEEKDAY(A2,2).
Can I show the weekday without a formula?
Yes. Use a custom number format as described in Method 3. This changes the display only, not the value, so you can still use the date in calculations.
How do I display the weekday in a different language?
Use the TEXT function with a locale code, e.g., =TEXT(A2,"[$-fr-FR]dddd") for French. Alternatively, use CHOOSE with your own translated names.
Why does my WEEKDAY formula return a different day than I expect?
Check the return_type argument. The default (1) makes Sunday=1. If you expect Monday=1, use 2. Also, ensure your computer’s date system isn’t set to 1904 (Mac default) if you’re sharing files across platforms.
Can I filter by weekday in a pivot table?
Yes. Add a helper column with =TEXT(A2,"dddd") and use that as a row or column field in the pivot table. Or use =WEEKDAY(A2,2) to group by weekday number.
Does the TEXT function work in Google Sheets?
Yes, the TEXT function works similarly in Google Sheets. However, the WEEKDAY function in Google Sheets uses a different default (Sunday=1) but you can specify the return type as well.
Related
- Excel Power Query Issues: Fixes & Common Errors
- Excel Power Query Load from Folder: Full Guide
- Excel Power Query Merge 3 Queries: Step-by-Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Dropdowns From Named Ranges: How to Set UpYes. In Google Sheets, create a named range for your list, then use that name as the source…
- Excel Import CSV Decimal Separator: How to Set ItTo import a CSV into Excel with the intended decimal separator, use Data > From Text/CSV, choose the…
- Excel Date Validation: How to Restrict Dates in CellsExcel date validation restricts what dates users can enter in a cell. In Microsoft Excel for Microsoft 365…
- PowerPoint Template: How to Create Your OwnTo create a custom PowerPoint template, design your theme and slide layouts in Slide Master view, then save…
- Excel Cut Shortcut: How to Move Cells with Ctrl+XIn Excel for Microsoft 365 on Windows, select the cell or range and press Ctrl+X to cut it;…
- Excel Unmerge Cells and Fill Every Cell with Its DataTo unmerge cells in Excel for Microsoft 365 on Windows, select the merged range and choose Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.