When working with datasets in Excel—whether it is a sales log, an employee attendance sheet, or a project timeline—dates are rarely just numbers. Knowing that "2024-05-15" is a Wednesday can be the difference between identifying a mid-week slump and just seeing another row of data.

Excel offers several ways to convert a date into a day of the week, ranging from simple visual formatting to complex logical formulas. This article covers every reliable method to help you master day-of-the-week extractions.

Quick Answer: Which Method Should You Use?

If you are in a hurry, here is the fastest way to choose:

  • To just see the name (e.g., "Monday"): Use Custom Formatting (Ctrl + 1).
  • To get the name as text for a report: Use the TEXT function: =TEXT(A1, "dddd").
  • To use the day in a calculation (e.g., IF it is Sunday, then...): Use the WEEKDAY function: =WEEKDAY(A1).

Method 1: The TEXT Function (Best for Labels and Reports)

The TEXT function is perhaps the most popular way to convert a date into a readable day name. This function converts a numeric date value into a text string based on a specific format code.

The Basic Formula

If your date is in cell A2, use the following:

  • Full Day Name (e.g., Monday): =TEXT(A2, "dddd")
  • Abbreviated Day Name (e.g., Mon): =TEXT(A2, "ddd")

How It Works Under the Hood

Excel stores dates as sequential serial numbers. For instance, January 1, 1900, is stored as "1". The TEXT function looks at that serial number and maps it to the corresponding day of the week, then formats it as text.

Pros and Cons of the TEXT Function

  • Pros: It is easy to read, and you can combine it with other text strings (e.g., ="Due on " & TEXT(A2, "dddd")).
  • Cons: The result is a text string, not a number. If you try to sort a column of TEXT results, Excel will sort them alphabetically (Friday, Monday, Saturday...) rather than chronologically.

Method 2: Custom Cell Formatting (The Non-Destructive Method)

If you want to keep the underlying date value for calculations but want it to look like a day of the week, custom formatting is the superior choice. This is what professional data analysts use to keep their workbooks clean and functional.

Steps to Apply Custom Formatting

  1. Select the cell or range containing your dates.
  2. Press Ctrl + 1 (or right-click and select Format Cells).
  3. Go to the Number tab and select Custom.
  4. In the Type box, delete the existing text and enter one of these codes:
    • dddd: Displays only the full day name (Monday).
    • ddd: Displays only the short day name (Mon).
    • m/d/yyyy (dddd): Displays the date followed by the day name (e.g., 5/15/2024 (Wednesday)).
  5. Click OK.

Why This is Better Than a Formula

When you use formatting, you aren't changing the data—you are only changing the "mask" over the data. If cell A2 contains "5/15/2024" and you format it as "dddd", the cell shows "Wednesday". However, if you point another formula to A2, it still sees the date value. This allows you to sort by date and perform date math while keeping the display user-friendly.

Method 3: The WEEKDAY Function (The Analytical Standard)

When your goal isn't just to see the day but to do something with it, the WEEKDAY function is your primary tool. This function returns a number from 1 to 7 representing the day of the week.

Syntax

=WEEKDAY(serial_number, [return_type])

  • serial_number: The date you are analyzing.
  • return_type (Optional): A number that tells Excel which day the week starts on.

Understanding Return Types

Choosing the right return_type is critical for accurate logical tests.

Return Type Week Start Range Notes
1 (or omitted) Sunday 1 (Sun) to 7 (Sat) The standard North American format.
2 Monday 1 (Mon) to 7 (Sun) The ISO 8601 standard; best for business math.
3 Monday 0 (Mon) to 6 (Sun) Useful for developers and index-based logic.
11 Monday 1 (Mon) to 7 (Sun) Same as type 2.

Practical Example: Finding Weekends

If you want to check if a date in A2 is a weekend (Saturday or Sunday) using the Type 2 setting: =IF(WEEKDAY(A2, 2) > 5, "Weekend", "Workday") Since Type 2 makes Saturday = 6 and Sunday = 7, anything greater than 5 is a weekend.

Method 4: Combining CHOOSE with WEEKDAY for Custom Labels

Sometimes "Monday" or "1" isn't enough. You might need custom labels like "Work Day 1" or specific regional abbreviations. The CHOOSE function works perfectly with WEEKDAY for this.

The Formula Structure

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

In this setup, WEEKDAY(A2) returns a number (1-7), and CHOOSE picks the corresponding item from the list. This is highly flexible. You can replace "Sun" with anything—like "Holiday" or "Shift A".

Method 5: Using the SWITCH Function (Excel 2019 and Microsoft 365)

If you have a modern version of Excel, the SWITCH function provides a cleaner alternative to CHOOSE. It is more readable when you have specific mappings.

Example Formula

=SWITCH(WEEKDAY(A2, 2), 1, "Start of Week", 5, "TGIF", 6, "Weekend", 7, "Weekend", "Mid-week")

This formula maps Monday (1) to "Start of Week", Friday (5) to "TGIF", and both 6 and 7 to "Weekend". Everything else defaults to "Mid-week". This level of granularity is excellent for dashboarding.

Advanced: Extracting Days in Power Query

If you are dealing with millions of rows, formulas can slow down your workbook. Power Query is the engine built into Excel designed for these heavy-duty transformations.

Steps in Power Query

  1. Select your data and go to the Data tab > From Table/Range.
  2. Once in the Power Query Editor, select your Date column.
  3. Go to the Add Column tab.
  4. Click Date > Day > Name of Day.
  5. Power Query will automatically create a new column with the day names (e.g., "Wednesday").
  6. Click File > Close & Load to bring the data back to Excel.

Power Query uses the "M" language. The underlying code generated is usually Date.DayOfWeekName([ColumnName]). This is much faster for large datasets than dragging a formula down 500,000 rows.

Troubleshooting Common Issues

Even with these simple formulas, things can go wrong. Here are the most common hurdles I have encountered in my years as a consultant.

1. The #VALUE! Error

This usually happens because the "date" in your cell isn't actually a date—it’s text that looks like a date.

  • The Test: Select the cell and look at the alignment. By default, Excel aligns numbers (dates) to the right and text to the left.
  • The Fix: Use the DATEVALUE function or the "Text to Columns" feature to convert the text back into a proper Excel serial number.

2. Wrong Day Names (Locale Issues)

The TEXT function is locale-dependent. If your Windows region is set to France, =TEXT(A2, "dddd") might return "mercredi" instead of "Wednesday".

  • The Fix: To force a specific language, you can use a language code in the TEXT function, though this is an advanced technique (e.g., [$-409] for US English).

3. Calculating the "First Monday of the Month"

A common request is finding a specific occurrence of a day. This requires a bit of "Date Math." To find the first Monday of any given month:

  1. Find the first day of the month: =DATE(YEAR(A2), MONTH(A2), 1)
  2. Apply a mathematical offset based on the WEEKDAY of that first day.

Real-World Applications

Creating a Dynamic Monthly Calendar

You can use the WEEKDAY function to determine where the "1st" of a month falls on a grid. By comparing the weekday of the 1st to the column index of your grid, you can create a calendar that updates automatically when you change the month.

Conditional Formatting for Weekends

To highlight every row in a report that falls on a Saturday or Sunday:

  1. Select your data range.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Enter: =WEEKDAY($A2, 2) > 5 (Assuming A2 is your date column).
  5. Set a fill color (like light gray) and click OK.

Frequently Asked Questions

What is the difference between "ddd" and "dddd"?

"ddd" returns a three-letter abbreviation (Mon, Tue, Wed), while "dddd" returns the full name (Monday, Tuesday, Wednesday).

How do I get the day of the week as a number starting from Monday?

Use =WEEKDAY(A1, 2). In this mode, Monday is 1 and Sunday is 7. This is often preferred for business reporting where Monday is the first day of the work week.

Can I get the day name in another language?

Yes, using the TEXT function with a locale ID. For example, =TEXT(A1, "[$-0407]dddd") will return the day name in German.

Why does Excel think 1/1/1900 is a Sunday?

Excel's date system was originally designed to be compatible with Lotus 1-2-3, which mistakenly treated 1900 as a leap year. While technically incorrect for that specific year, the system has been maintained for decades to ensure backward compatibility.

Summary and Key Takeaways

Extracting the day of the week in Excel is a fundamental skill that enhances both the readability and the analytical power of your spreadsheets.

  • Use Custom Formatting to keep the data as a date while displaying it as a name.
  • Use the TEXT Function when you need the day name to be part of a text-based report or concatenated string.
  • Use the WEEKDAY Function for logical tests, conditional formatting, and sorting.
  • For Big Data, leverage Power Query’s built-in date transformation tools to save processing power.

By understanding the difference between how Excel stores a date (as a number) and how it displays a date (as a format), you can manipulate time-based data with much greater precision. Whether you are building a simple schedule or a complex financial model, these tools will ensure your data remains clear and actionable.