Excel Day of the Week Formula: What Most People Get Wrong

You’ve probably been there. You have a massive spreadsheet of sales data or project deadlines, and you’re staring at a column of dates that mean absolutely nothing to your brain. Dates like 05/14/2024 don't tell a story. But "Tuesday" does. Knowing the day of the week is basically the secret sauce for figuring out why your retail shop is dead on Mondays or why everyone submits their expense reports on a Friday afternoon. Honestly, finding the day of the week formula in excel is one of those tiny skills that makes you look like a wizard to your boss, even though it’s actually pretty simple once you stop overcomplicating it.

Most people think there’s just one way to do this. They’re wrong. Depending on whether you want a number, a full name, or a weirdly specific abbreviation, Excel gives you a few different paths.

The WEEKDAY Function is the Backbone

If you want a number, use =WEEKDAY(). That’s the classic.

📖 Related: Finding Your Way Through the Massive List of US Naval Ships and What They Actually Do

But here’s the kicker: Excel thinks Sunday is 1. Why? Because the default "return type" is set to a system where the week starts on Sunday. If you’re in the UK or Europe, or you just have a brain that functions on a Monday-to-Sunday cycle, this is going to drive you nuts. You’ll see a "2" and think it’s Tuesday, but Excel is sitting there thinking it’s Monday.

To fix this, you use the second argument in the formula. It looks like this: =WEEKDAY(A2, 2). Adding that "2" tells Excel, "Hey, Monday is the first day of the week, okay?" If you leave it blank, it defaults to 1 (Sunday). There are actually about ten different return types, including ones that start on Friday if you're working in certain Middle Eastern markets, but for 99% of us, it’s either the default or type 2.

Making it Human with the TEXT Function

Numbers are fine for math, but they're terrible for reading. If you want the cell to actually say "Wednesday," the WEEKDAY formula is useless on its own. You need the TEXT function. This is arguably the most powerful way to handle the day of the week formula in excel because it turns a serial date into a string of text.

Try this: =TEXT(A2, "dddd").

Four "d"s gives you the full name. Three "d"s—=TEXT(A2, "ddd")—gives you the short version, like "Mon" or "Tue." It’s clean. It’s fast. And the best part? It doesn’t care about your regional settings as much as the weekday function does because you’re explicitly telling it how to look.

👉 See also: Is Signal the Most Secure Messaging App? Why Everyone From Hackers to Grannies Is Switching

Wait. There's a catch.

When you use the TEXT function, that cell is no longer a number. It's a "string." That means you can’t easily sort it chronologically anymore. If you sort a list of "Monday," "Tuesday," and "Wednesday" alphabetically, Friday comes first. That’s a nightmare for reporting.

A Quick Workaround for Sorting

If you need the name of the day but you also need to sort your data, don't use a formula at all. Just link the cell (e.g., =A2) and change the Number Format. Go to "Custom" and type dddd. The cell still holds the underlying date value, so it sorts perfectly, but it looks like a day of the week. It’s a visual trick. It’s honestly the "pro move" that saves you from writing complex nested formulas later on.

Dealing with Workdays and Weekends

Business isn't always seven days a week. Sometimes you need to know if a date falls on a weekend so you can exclude it from a calculation. This is where the day of the week formula in excel gets a bit more "mathy."

If you want to check if a date in cell A2 is a weekend, you can use a simple logical test: =IF(WEEKDAY(A2, 2) > 5, "Weekend", "Workday").

Since we used return type "2," Monday through Friday are 1 through 5. Anything higher than 5 is a Saturday or Sunday. Easy.

But what if your "weekend" is Friday and Saturday? This happens a lot in global logistics. You’d have to shift your return type logic. Excel's NETWORKDAYS.INTL function is actually better for this if you're trying to calculate the number of days between two dates while ignoring specific days of the week. It lets you define exactly which days are "off" using a string of zeros and ones. It's a bit hardcore, but if you’re managing a project in Dubai while living in New York, it’s a lifesaver.

Common Blunders to Avoid

1. The 1900 Bug: Excel’s date system starts on January 1, 1900. If you accidentally type a small number like "5" into a cell and format it as a date, Excel thinks you mean January 5, 1900. If you’re getting weird days of the week, check that your source data hasn't been accidentally converted to small integers.
2. Empty Cells: If you point a TEXT or WEEKDAY formula at an empty cell, Excel often treats it as "0." In Excel-land, Day 0 is January 0, 1900, which Excel considers a Saturday. So, if your spreadsheet is full of "Saturday" for rows that don't have dates yet, that's why. Wrap your formula in an IF statement: =IF(A2="", "", TEXT(A2, "dddd")).
3. The "Text that looks like a date" Trap: This is the worst. Sometimes you export data from a database and it looks like 01/01/2024, but Excel thinks it's just a bunch of characters. The day of the week formula in excel will return a #VALUE! error. You have to convert those to real dates first using "Text to Columns" or the DATEVALUE function.

Power User Tip: The SWITCH Function

If you're using a newer version of Excel (Microsoft 365 or Excel 2019+), you can get really fancy. Maybe you don't want "Monday." Maybe you want "M." Or maybe you want "Day 1 (Mon)."

You can use SWITCH with WEEKDAY to create totally custom labels:
=SWITCH(WEEKDAY(A2, 2), 1, "M", 2, "T", 3, "W", "Other")

This is much cleaner than writing a "Nested IF" statement that looks like a bowl of spaghetti. Honestly, nested IFs are the hallmark of someone who learned Excel in 2005 and never looked back. Don't be that person.

Why This Actually Matters for Data Analysis

Think about seasonality. If you’re looking at website traffic, Saturdays are usually garbage. If you’re looking at B2B email opens, Tuesday morning is the gold mine. By using the day of the week formula in excel, you can create a Pivot Table that groups your data by day.

Suddenly, you aren't looking at 365 rows of noise. You're looking at 7 rows of insight. You might see that your highest sales volume happens on Thursdays, but your highest average order value happens on Sundays. You can't see that without these formulas.

Microsoft's own documentation on the WEEKDAY function is a good reference, but it's pretty dry. They don't tell you that the real magic happens when you combine it with Conditional Formatting. Imagine a schedule where every Saturday and Sunday automatically turns light grey so you can see the work week clearly. You just set a conditional formatting rule using the formula =WEEKDAY(A1, 2) > 5.

Practical Next Steps

First, go to your current project and find a column of dates. Create a new column next to it.

Try the TEXT method first: =TEXT(A2, "dddd"). It’s the most satisfying because you get instant results you can actually read. If you need to do math—like calculating a "Friday Surcharge"—then switch to WEEKDAY.

If you're dealing with a massive dataset, always double-check your "Return Type." Nothing ruins a report faster than a Sunday being counted as a Monday because you forgot a comma and a "2."

Lastly, if your dates are coming in as text from a CSV, use the VALUE function or the DATEVALUE function to "wake up" the cells so Excel recognizes them as numbers. Only then will your day of the week formulas actually start working.

Clean your data, pick your formula, and stop counting days on your fingers. You've got this.