You’ve got a massive spreadsheet of transaction dates. Maybe it's a list of employee clock-in times or a marketing calendar for the next fiscal year. Honestly, looking at a column of dates like "2026-05-14" doesn't tell you much at a glance. You need to know if that was a Tuesday or a Sunday because, let's face it, nobody buys enterprise software on a Sunday morning. Getting the day of week in excel from date is one of those tasks that sounds like it should be a single click, but then you find yourself staring at four different formulas wondering why Excel keeps returning "1/7/1900" instead of "Wednesday."
It’s frustrating.
Most people just want the word "Monday." Microsoft, however, thinks in serial numbers. To Excel, dates are just numbers counting up from January 1, 1900. If you don't speak Excel’s language, you're going to get weird results.
The TEXT Function is Honestly Your Best Friend
Forget the complex nested logic for a second. If your goal is strictly visual—meaning you just want to see the word "Monday" or "Mon" in the cell next to your date—the TEXT function is the goat. It’s basically a formatting tool disguised as a formula.
You type something like =TEXT(A2, "dddd").
That "dddd" is a secret code. Four "d"s tell Excel to spit out the full name of the day. If you only use three "d"s, like =TEXT(A2, "ddd"), you get the abbreviated version like "Tue" or "Fri." It’s clean. It’s fast. But there is a catch that most "experts" forget to mention. When you use the TEXT function, Excel no longer sees that cell as a date or a number. It sees it as "string" or text.
Why does that matter?
Well, try sorting a list of "Monday," "Tuesday," and "Wednesday" alphabetically. You’ll get Friday, Monday, Saturday, Sunday. It’s a mess. If you need to sort your data chronologically, the TEXT function might actually break your workflow. It’s great for reports, but it’s kinda terrible for data analysis.
When You Actually Need Numbers: Enter WEEKDAY
Sometimes you don't want the word. You want a number that represents the day because you’re building a conditional formatting rule or a complex IF statement. This is where the WEEKDAY function comes in.
The basic syntax is =WEEKDAY(A2).
By default, Excel decides that Sunday is 1 and Saturday is 7. I’ve always found this a bit annoying because, in my head, the week starts on Monday. Thankfully, the software engineers at Microsoft added a second argument called return_type.
- If you use
=WEEKDAY(A2, 2), Monday becomes 1 and Sunday becomes 7. - If you use
=WEEKDAY(A2, 3), Monday is 0 and Sunday is 6 (perfect for programmers).
Microsoft's own documentation on the WEEKDAY function lists about ten different return types, including specific ones for Bulgarian and Chinese calendars. Most of us will never touch those. Stick to type 2 if you want a standard Monday-to-Sunday work week.
Custom Formatting: The "No-Formula" Hack
There is a way to see the day of the week without actually changing the data in the cell. This is the "pro" move.
Imagine you have the date in cell A2. You don't want to create a new column B. You just want A2 to look different. You can right-click the cell, go to Format Cells, and hit Custom. In the "Type" box, just type dddd, mmmm dd, yyyy.
Suddenly, "01/17/2026" transforms into "Saturday, January 17, 2026."
The magic here is that the underlying value is still a date. You can still do math with it. You can subtract two dates to find the number of days between them. Excel still sees the serial number, but your human eyes see the day of the week. This is usually the best way to handle day of week in excel from date if you’re tight on screen real estate and don't want to clutter your sheet with extra columns.
Dealing with the "Value" Error
We've all been there. You put in the formula and get #VALUE!.
Usually, this happens because your "date" isn't actually a date. It’s text that looks like a date. If you imported data from a CSV or some old accounting software, Excel might not recognize "17.01.2026" as a valid entry.
Quick fix: Use the DATEVALUE function to convert that text back into a number Excel can understand. Or, better yet, use the "Text to Columns" tool under the Data tab to force Excel to re-evaluate the formatting of the entire column. It’s a lifesaver.
Advanced Use Cases: Power Query and Beyond
If you’re working with 500,000 rows, formulas will start to lag your computer. You’ll see that little "Calculating (4 Threads): 10%" at the bottom of the screen and want to pull your hair out.
For big data, use Power Query.
- Select your data and go to Data > From Table/Range.
- In the Power Query editor, go to Add Column.
- Click Date > Day > Name of Day.
Power Query writes the code for you. It’s essentially creating a metadata layer that handles the conversion before the data even hits your spreadsheet. It’s much more robust than cell-based formulas. Plus, if you update your source data, you just hit "Refresh" and the new days of the week pop in automatically.
Common Misconceptions About Excel Dates
A huge mistake people make is assuming Excel knows about every calendar change in history.
Excel's date system actually has a built-in error: it treats the year 1900 as a leap year. It wasn't. This was a deliberate choice by the original developers to maintain compatibility with Lotus 1-2-3, which was the king of spreadsheets back in the day. If you’re doing historical research on the day of week in excel from date for any year before March 1, 1900, your days will be off by one.
For 99% of business users, this doesn't matter. But if you’re a historian trying to find out if a specific battle in the 1800s happened on a Tuesday, do not trust Excel.
Actionable Steps for Your Spreadsheet
Stop overthinking it. Choose the method based on your specific need:
👉 See also: Hyde Park Computer Center: Why Local Tech Support Still Beats Big Box Stores
To just see the day for a presentation: Use Custom Formatting. Press Ctrl + 1, go to Custom, and type dddd. No extra columns needed.
To use the day in a logic formula (like "If day is Saturday, then X"): Use =WEEKDAY(A2, 2). This gives you a clean number (1-7) that is easy to code against.
To create a readable list you can filter by name: Use =TEXT(A2, "dddd"). Just remember that it sorts alphabetically, not chronologically.
To handle massive datasets: Import the data into Power Query and use the built-in Date transformation tools to avoid formula lag.
If your dates are currently showing up as five-digit numbers like 46039, don't panic. That’s just Excel showing you the serial number. Change the cell format to "Short Date" or "Long Date" and it will return to a format you actually recognize. Excel is smart, but it's also very literal; it will only show you what you've specifically told it to show.