Excel Show Weekday for Date: 4 Easy Ways

How-to basics · Updated November 22, 2026

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.

  1. Select the cell(s) with dates.
  2. Press Ctrl+1 (or Cmd+1 on Mac) to open Format Cells.
  3. Go to the Number tab, choose Custom.
  4. 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”
  5. 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 using WEEKDAY which returns a number. Use TEXT or CHOOSE to get a name.
  • Problem: The weekday is off by one day.
    Solution: Check the return_type in WEEKDAY. Different systems use different conventions. Use 2 for 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 TEXT function 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

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.