ThisCalculator

Everyday

Excel Get Day Name from Date: 4 Easy Ways

Updated · Standard formula, shown below

Excel Get Day Name from Date: 4 Easy Ways

To get the day name from a date in Excel, use the TEXT function with a format code like =TEXT(A1,"dddd") for the full weekday name or =TEXT(A1,"ddd") for the abbreviated name. Alternatively, you can use the CHOOSE function with the WEEKDAY function, or change the cell’s number format to display the day name without altering the underlying date value.

Why You Might Need the Day Name

Dates in Excel are stored as serial numbers, where each whole number represents a day since January 1, 1900. The day of the week is not stored separately; it is derived from the date itself. You might need to display the day name for reports, schedules, or data analysis—for example, to see if sales are higher on weekends, to group transactions by weekday, or to create a readable calendar view. Excel offers several ways to extract this information, each with its own strengths.

Using the TEXT Function (Simplest Method)

The TEXT function converts a number to text using a format code. For dates, the format codes dddd and ddd return the full and abbreviated day names, respectively.

  • Full day name: =TEXT(A1, "dddd") returns e.g., “Monday”.
  • Abbreviated day name: =TEXT(A1, "ddd") returns e.g., “Mon”.

This method is straightforward and works in all modern Excel versions. The result is text, so you cannot use it in date calculations. Also, the day name is in the language of your Excel installation (or the system locale).

Using CHOOSE and WEEKDAY (More Control)

If you need a custom day name (e.g., “Mon” or “Monday” in a specific language or with a prefix), you can combine WEEKDAY and CHOOSE. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) by default. You can then map those numbers to your own labels.

  1. Use =WEEKDAY(A1) to get the day number (1-7).
  2. Use =CHOOSE(WEEKDAY(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat") to return your custom label.

This method gives you full control over the output text. Note that the default WEEKDAY numbering starts with Sunday as 1; you can change the return type (e.g., WEEKDAY(A1, 2) makes Monday=1).

Changing the Cell Format (Keeps Date Value)

If you want to see the day name but keep the date as a true date for calculations, apply a custom number format to the cell. This does not change the underlying value—only the display.

  1. Select the cell(s) with the dates.
  2. Press Ctrl+1 to open Format Cells.
  3. Go to the “Number” tab, choose “Custom” at the bottom.
  4. Type dddd for the full day name, or ddd for the abbreviated one.
  5. Click OK.

The cell will show e.g., “Wednesday”, but the formula bar will still show the original date. This is ideal when you need to keep the date for sorting or calculations while displaying the day name.

Common Problems and How to Fix Them

  • Day name appears as a number: If you see a number instead of a name, the cell is probably formatted as General or the TEXT function is missing quotes. Ensure you use =TEXT(A1, "dddd") with the format code in quotes.
  • Wrong language: The TEXT function and custom formats use the Excel language. To get English day names in a non-English Excel, you can use a formula like =CHOOSE(WEEKDAY(A1), "Sunday", ...) or change the system locale.
  • Date stored as text: If your date is text (e.g., “2025-03-15”), TEXT will not work. Convert it to a real date using =DATEVALUE(A1) first, or use the DATE function.
  • Blank cells show as Saturday: If a cell is empty, WEEKDAY returns 7 (Saturday) by default. Use =IF(A1="", "", CHOOSE(WEEKDAY(A1), ...)) to avoid this.

Comparing the Methods

Each approach has trade-offs. The TEXT function is quick and concise, but returns text and is locale-dependent. CHOOSE+WEEKDAY is flexible and locale-independent, but longer. Changing the number format preserves the date type and is best for display purposes. For a quick one-off, TEXT is fine; for a reusable report, consider a custom format or a helper column with CHOOSE.

Frequently Asked Questions

How do I get the day name in Excel without a formula?

You can use a custom number format. Select the cells, press Ctrl+1, go to Number > Custom, and type dddd for the full name or ddd for the abbreviation. The cell will display the day name, but the underlying date remains unchanged.

Why is my TEXT function returning a number?

If you use =TEXT(A1, dddd) without quotes around the format, Excel treats it as a reference and returns an error or a number. Always enclose the format code in double quotes: =TEXT(A1, "dddd").

Can I get the day name in a different language?

Yes, but the TEXT function uses the Excel language. To get English names in a non-English Excel, use the CHOOSE method with your own list: =CHOOSE(WEEKDAY(A1), "Sunday", "Monday", ...). Alternatively, change the system locale or use a custom VBA function.

How do I get the day name from a date in Google Sheets?

Google Sheets uses the same TEXT function: =TEXT(A1, "dddd"). The CHOOSE method also works. Custom number formats are also available via Format > Number > Custom.

What is the difference between ddd and dddd?

ddd returns the abbreviated day name (e.g., “Mon”), while dddd returns the full name (e.g., “Monday”). Both work in TEXT and custom formats.

How do I get the day name from a date in a pivot table?

In a pivot table, you can group dates by day. Right-click a date field, choose Group, and select “Days”. To show day names, add a helper column with =TEXT(A1, "dddd") and use that in the pivot table.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.