You've got a massive spreadsheet. Rows of dates stretch down until they basically disappear off the bottom of your screen. Now, your boss wants to know which of those dates fell on a Tuesday. Or maybe you're trying to figure out if your sales spike on weekends. Looking at 2024-05-14 doesn't immediately scream "Tuesday" to the human brain unless you're some kind of calendar savant.
Honestly, it's frustrating.
Most people start manually checking their phone calendars. Please, don't do that. It’s a recipe for a headache and a lot of data entry errors. Getting the day of week from date excel is actually one of those things that feels like a "hack" but is really just basic math under the hood. Excel doesn't see "May 14th." It sees a serial number. Every date in Excel is just the number of days elapsed since January 1, 1900. Once you realize that, the rest is just formatting and logic.
The TEXT Function: The Quickest Way to See the Name
If you just want to see the word "Monday" or "Mon" in the cell next to your date, the TEXT function is your best friend. It’s simple. It’s clean. It works.
The syntax is basically TEXT(value, format_text). Suppose your date is in cell A2. You’d type =TEXT(A2, "dddd").
Boom.
Excel looks at that serial number in A2 and translates it. Using four "d's" gives you the full name—Wednesday, Saturday, whatever. If you’re tight on space and just want "Wed" or "Sat," use three "d's" instead: =TEXT(A2, "ddd"). It’s surprisingly flexible. You can even wrap it into a sentence if you’re feeling fancy, like ="The report was due on " & TEXT(A2, "dddd").
One thing to keep in mind, though: when you use the TEXT function, Excel treats the result as text, not a number. If you try to sort a list of "Mondays" and "Tuesdays" created this way, Excel will sort them alphabetically. You'll get Friday, Monday, Saturday, Sunday. That is almost never what you want. If you need to sort chronologically, you’re better off using a different method or keeping the original date column nearby.
Using WEEKDAY for Logic and Calculations
Now, if you’re doing actual data analysis, the WEEKDAY function is the real powerhouse. It doesn’t give you a name. It gives you a number.
👉 See also: iPhone 16 Pink Pro Max: What Most People Get Wrong
By default, Excel thinks Sunday is 1 and Saturday is 7.
The formula is =WEEKDAY(A2).
This is huge for conditional formatting. Say you want to highlight every row that happens on a weekend. You’d set up a rule that checks if WEEKDAY(A2) is either 1 or 7. It’s much faster for the computer to process a single digit than a string of text.
The "Return Type" Confusion
Here is where people usually mess up. The WEEKDAY function has an optional second argument called return_type. If you ignore it, you get the Sunday=1 setup. But what if you’re in Europe or just prefer Monday being the start of the week?
- Type 1: Sunday (1) to Saturday (7).
- Type 2: Monday (1) to Sunday (7). This is usually what project managers want.
- Type 3: Monday (0) to Sunday (6). Great for programmers who are used to zero-based indexing.
If you use =WEEKDAY(A2, 2), Monday becomes 1. It makes filtering for "weekdays vs. weekends" way easier because you can just look for any number greater than 5. Simple math.
Custom Formatting: The "Invisible" Trick
Sometimes you don't want a new column at all. You just want the date that's already there to look like a day of the week. This is the cleanest way to handle a spreadsheet because you aren't adding extra data that clutters things up.
- Right-click your date cell.
- Hit "Format Cells."
- Go to the "Number" tab and pick "Custom."
- In the "Type" box, delete whatever is there and type
dddd.
The cell still contains the full date. If you look at the formula bar, it still says 5/14/2024. But the cell itself displays "Tuesday." This is the pro move because you can still do math with the date, sort it perfectly, and use it in Pivot Tables without Excel getting confused by text strings.
Handling the "Empty Cell" Problem
Excel has a quirk. If you point a TEXT or WEEKDAY formula at a blank cell, it thinks you're talking about Day Zero. In Excel's world, Day Zero is January 0, 1900, which was a Saturday.
✨ Don't miss: The Singularity Is Near: Why Ray Kurzweil’s Predictions Still Mess With Our Heads
So, if you drag your formula down 500 rows but only have 200 rows of data, you're going to see a lot of "Saturdays" or "6s" at the bottom. It looks sloppy.
You should wrap your formula in an IF statement. Try something like =IF(A2="", "", TEXT(A2, "dddd")). This basically tells Excel: "If the date cell is empty, leave this cell empty too. Don't go guessing about 1900."
Why This Actually Matters for Business
It’s not just about aesthetics. In supply chain management or retail, the day of week from date excel is a critical variable. Think about it. Do you ship more on Mondays? Do your customers complain more on Fridays?
I’ve seen companies realize they were overstaffed on Tuesdays just by pulling the weekday out of their historical order data and throwing it into a Pivot Table. You can’t group a Pivot Table by "Wednesday" if all you have are raw dates like 12/03/2023. You need that helper column or the specific grouping functionality.
Using CHOOSE for Custom Names
Maybe you don't want "Monday." Maybe you want "Mon-Payday" or something specific to your office. You can nest WEEKDAY inside a CHOOSE function.
=CHOOSE(WEEKDAY(A2), "Sun", "Work Day", "Work Day", "Work Day", "Work Day", "Work Day", "Sat")
This looks at the number returned by WEEKDAY and picks the corresponding item from your list. It's a bit more manual to set up, but it gives you total control over the output.
Power Query: The Modern Approach
If you’re dealing with millions of rows, formulas will slow your workbook down to a crawl. Every time you change a cell, Excel has to recalculate those thousands of TEXT functions.
🔗 Read more: Apple Lightning Cable to USB C: Why It Is Still Kicking and Which One You Actually Need
Instead, use Power Query.
- Select your data and go to the "Data" tab.
- Click "From Table/Range."
- In the Power Query editor, right-click your date column.
- Go to "Transform" > "Date" > "Day" > "Name of Day."
Power Query does the work during the data loading process. It’s much more efficient for large datasets and keeps your actual spreadsheet snappy. Plus, it’s reproducible. Every time you drop new data into that table, Power Query will automatically turn those dates into day names with one click.
Common Pitfalls and Limitations
Excel isn't perfect. The biggest limitation is the "1900 Leap Year Bug." Excel's creators intentionally (and incorrectly) treated 1900 as a leap year to maintain compatibility with Lotus 1-2-3. This means if you are a historian working with dates in January or February of 1900, your day-of-the-week calculations might be off by a day.
For anything after March 1, 1900, you’re totally fine.
Another weird one: Regional settings. If you share a workbook with someone in France, and you used the TEXT function with "dddd," their Excel might not recognize the "d" format code if their system uses "j" for jour. However, the WEEKDAY function and Custom Formatting are generally more stable across different language versions of Office.
Actionable Next Steps
To master this, start by auditing your current sheets. If you have a column where you've been manually typing "Monday," delete it. Use the =TEXT(A2, "dddd") formula to populate it instantly. Once you have that working, try creating a Pivot Table and dragging your new "Day" column into the "Rows" area. This will immediately show you the distribution of your data across the week, which is usually where the most interesting insights live. If the file starts lagging, remember to convert those formulas to "Values" or switch over to the Power Query method for better performance.