The excel formula for day of the week is one of those small skills that quietly transforms how you handle dates inside spreadsheets. Whether you are scheduling shifts, building a payroll calendar, tracking project deadlines, or analyzing sales by weekday, knowing how to extract the day name or day number from any date opens up powerful filtering, sorting, and conditional formatting options. This guide walks through every method professionals use, from the classic WEEKDAY function to TEXT, CHOOSE, and SWITCH combinations.
Excel stores dates as serial numbers behind the scenes. January 1, 1900 is day 1, and every date after that is just an integer counting forward. Because of that internal numbering, Excel can calculate the day of the week instantly with a single formula. You do not need a lookup table, a macro, or a manual list of weekdays. You only need to understand which function returns what, and how to format the result so it reads as Monday, Mon, or 2 depending on your need.
In this article you will learn the syntax of WEEKDAY with all eleven return types, how TEXT can turn any date into a custom day label, and how CHOOSE and SWITCH let you convert numbers into your own custom strings like Workday or Weekend. We will also cover regional issues, common errors, and how to combine these formulas with conditional formatting to highlight weekends across an entire schedule automatically.
If you already use formulas like vlookup excel for matching customer data, the day-of-week functions will feel familiar. They follow the same argument-based syntax, return predictable results, and play well with other lookup and reference tools. Many professionals pair WEEKDAY with VLOOKUP to map weekday numbers to custom labels, holiday flags, or shift codes that are stored in a separate reference table.
This guide is written for analysts, accountants, project managers, students, and anyone preparing for Microsoft certification or job interviews where Excel proficiency matters. Every example uses real cell references, sample dates from 2026, and copy-ready formulas you can paste directly into your workbook. By the end you will be able to look at any date column and instantly produce the matching weekday in whatever format your report demands.
We will also point out where beginners get tripped up, especially with the return_type argument in WEEKDAY. Choosing the wrong return type silently produces correct-looking but wrong results, which can corrupt entire reports if a Sunday gets treated as the first day instead of the last. Reading this carefully now will save you hours of debugging later, and it will sharpen the date logic you bring to every future spreadsheet you build.
By the time you finish, you should be comfortable writing six different day-of-week formulas from memory, knowing exactly when each one is appropriate, and explaining the difference between WEEKDAY return types 1, 2, and 3 to a colleague without having to look it up.
The function uses =WEEKDAY(serial_number, [return_type]). The first argument is the date or cell reference, the second optional argument controls numbering from 1 to 17.
Default behavior where Sunday equals 1 and Saturday equals 7. Used in most U.S. business contexts, payroll systems, and legacy spreadsheets where Sunday begins the week.
Monday equals 1 and Sunday equals 7. This is the ISO standard used internationally and is the most common choice for European reports and project schedules.
Monday equals 0 and Sunday equals 6. Useful for zero-indexed arrays, programming-style logic, and certain date math involving modulo operations.
Each variant treats a different day as the first day of the week. Type 11 starts Monday, type 12 starts Tuesday, and so on through type 17 starting Saturday.
Once you understand WEEKDAY, the next leap is using the TEXT function to convert that number into a readable day name. WEEKDAY by itself returns an integer like 4, which is fine for calculations but unhelpful in a report. TEXT takes any date or serial number and a format code, and produces a formatted string. For day names, the magic codes are dddd for the full name like Wednesday, ddd for the three-letter abbreviation like Wed, and even dddddd in some locales for variations.
The simplest formula is =TEXT(A2,"dddd"). If A2 contains 5/21/2026, this returns Thursday. Change the format string to ddd and it returns Thu. Because TEXT outputs a string, you can concatenate it with other text directly, like ="Meeting on "&TEXT(A2,"dddd")&", "&TEXT(A2,"mmmm d"). That produces a sentence like Meeting on Thursday, May 21 without any helper columns. This is invaluable for building dynamic email drafts, dashboards, and printable calendars.
One critical point: TEXT respects your Windows or Office regional language settings. If your machine is set to Spanish, dddd returns jueves instead of Thursday. To force English regardless of region, wrap the format with the locale code: =TEXT(A2,"[$-409]dddd") forces U.S. English. Codes like 40C force French and 410 force Italian. This is how international teams build reports that always render in a chosen language no matter where the file is opened.
You can also combine TEXT with conditional logic. For example, =IF(WEEKDAY(A2,2)>5,"Weekend",TEXT(A2,"dddd")) returns the literal word Weekend for Saturday and Sunday, and the actual day name for weekdays. This kind of compound formula is common in time-tracking sheets where you want to visually separate billable from non-billable days without writing a macro or using how to create a drop down list in excel to pre-select day categories.
For situations where you want the day number but stored as text with leading characters, TEXT can handle padding too. =TEXT(WEEKDAY(A2),"00") returns 04 instead of 4, which is useful when day numbers are used as part of a sortable string key in a database extract. Pair this with month and year padding to create keys like 2026-05-21-04 that sort cleanly in any system.
It is worth memorizing the difference between TEXT and custom cell formatting. Custom formatting changes how a value displays but keeps the underlying serial number intact, which means formulas referencing that cell still see a date. TEXT actually converts the value to a string, which means downstream formulas cannot do math on it. Choose TEXT when you need a label or concatenation, and choose custom formatting when calculations must continue working.
Finally, remember that TEXT can produce ambiguous output in some edge cases, like format codes that conflict with regional separators. Always test your formula on a known date like January 1, 2026 which is a Thursday, and verify the output matches your expectation before deploying it across a large dataset.
The CHOOSE function pairs perfectly with WEEKDAY to turn a number into any custom label you want. The formula =CHOOSE(WEEKDAY(A2,2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday") returns the day name based on return type 2. The advantage is you control the exact spelling, language, and even shortened codes. You could replace those names with M, T, W, T, F, S, S or with shift codes like Day, Day, Day, Day, Day, Off, Off.
This approach is especially powerful when your custom labels have nothing to do with weekday names. A logistics company might use =CHOOSE(WEEKDAY(A2,2),"Route A","Route B","Route A","Route B","Route A","Maintenance","Closed") to assign delivery routes per weekday. CHOOSE accepts up to 254 arguments, so it scales to far more complex weekly cycles than just day names. Combine it with named ranges and your formulas become self-documenting and easy to audit.
SWITCH was added in Excel 2019 and Microsoft 365 as a cleaner alternative to nested IF statements. Use =SWITCH(WEEKDAY(A2,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7,"Sun") to map weekday numbers to abbreviations. The syntax is friendlier when you have many discrete cases because you do not have to count argument positions like in CHOOSE.
SWITCH also allows a default value, which is impossible with CHOOSE. Adding a trailing string like =SWITCH(WEEKDAY(A2,2),6,"Weekend",7,"Weekend","Weekday") elegantly classifies days into two buckets in a single formula. It is also faster to read for new team members because each case is paired visually with its result. For modern Excel work, SWITCH should generally be your default choice when mapping numeric outputs to labels.
You do not always need a formula at all. Right-click any cell with a date, choose Format Cells, select Custom, and type dddd in the Type box. The cell will display Thursday but the underlying value remains a serial date number. This means you can still subtract two cells to count days, sort by date, or use the cell in any formula that expects a date.
This method is the fastest way to add weekday labels to large datasets without slowing down recalculation. The trade-off is that the display follows your regional settings and cannot be concatenated as text. For dashboards and printed schedules, custom formatting is often the cleanest solution. For exports to other systems, TEXT or CHOOSE is safer because the value travels as a real string.
Never rely on the default WEEKDAY behavior. Always write =WEEKDAY(A2,2) instead of =WEEKDAY(A2). Explicit return types make formulas self-documenting and prevent the most common date bug in business spreadsheets: a weekend being silently treated as a weekday because someone assumed Monday was day 1.
Common errors with day-of-week formulas usually fall into three buckets: wrong return type, text-formatted dates, and regional locale mismatches. Each one is easy to fix once you know what to look for, but each can silently corrupt an entire report if missed. Spending a few minutes learning these failure modes will save hours of debugging later, and will make you the person your team trusts when a date column starts producing strange results in a year-end summary.
The most frequent error is forgetting that WEEKDAY returns 1 for Sunday by default. If your business considers Monday the start of the week, every weekend calculation will be off by one. The fix is simple: always pass 2 as the second argument so Monday equals 1 and Sunday equals 7. Better still, make this a standard in your team style guide. Add a comment like Return type 2 used per company standard next to every WEEKDAY formula in shared workbooks.
The second common pitfall is text-formatted dates. When data is imported from a CSV, web page, or system export, dates often arrive as strings like 21/05/2026 or 2026-05-21. WEEKDAY returns a VALUE error on text input. The fix is to wrap the reference in DATEVALUE: =WEEKDAY(DATEVALUE(A2),2). For huge datasets, it is faster to convert the whole column with Text to Columns or Power Query before applying any date formulas.
The third issue is regional locale mismatches. A file built in the U.S. with dddd will display Monday in English. Open the same file on a German laptop and it shows Montag. This is usually fine, but breaks reports that depend on exact string matching downstream. Always force locale with [$-409] for U.S. English, [$-407] for German, or [$-40C] for French inside your TEXT formula when consistency matters across users.
Another subtle problem occurs when WEEKDAY is used inside conditional formatting. If your formula references a relative cell like A1 instead of the active row, the formatting will apply incorrectly. Always test conditional formatting on a single row first, then expand the range. Use the Manage Rules dialog to inspect the applied range and confirm it matches the visible data.
Pivot tables introduce their own twist. If you group dates by weekday in a pivot, Excel uses your regional first-day-of-week setting, which is independent of any WEEKDAY formula in the source data. To make pivot groupings match your formulas, change the Windows first-day-of-week setting or add a helper column with WEEKDAY(A2,2) and group on that column instead.
Finally, watch out for hidden time components. A cell that displays 5/21/2026 might actually contain 5/21/2026 14:30:00. Time does not affect WEEKDAY, but it can confuse comparisons like A2=DATE(2026,5,21) which returns FALSE because of the trailing fractional time. Use INT(A2) to strip the time portion before comparison if needed.
Advanced use cases for day-of-week formulas go far beyond simply labeling a column. One of the most powerful patterns is using WEEKDAY inside conditional formatting to highlight weekends automatically across a schedule. Select your date range, open Conditional Formatting, choose New Rule, pick Use a formula to determine which cells to format, and enter =WEEKDAY(A1,2)>5. Choose a fill color and click OK. Every Saturday and Sunday across thousands of rows will be shaded instantly, and the highlighting updates dynamically as dates change.
Another advanced pattern is using WEEKDAY with SUMPRODUCT to count occurrences of a specific weekday in a date range. The formula =SUMPRODUCT((WEEKDAY(date_range,2)=1)*1) returns the number of Mondays in your range. Replace 1 with 5 to count Fridays. This is invaluable for payroll, attendance reports, and project planning where you need to know how many workdays of a specific type fall within a billing period.
You can also build a perpetual calendar with just WEEKDAY and DATE. Place the first day of a month in cell A1, then in B1 enter =A1-WEEKDAY(A1,2)+1 to find the Monday of that week. Fill across and down with SEQUENCE or a simple +1 increment, and you have a full month grid that auto-adjusts to any year. Pair it with TEXT for headings and you get a clean printable calendar with zero VBA. Combining this with skills like how to freeze a row in excel keeps your headers visible as you scroll.
WEEKDAY also combines beautifully with NETWORKDAYS and WORKDAY for business-day arithmetic. NETWORKDAYS counts working days between two dates while excluding weekends and an optional holiday list. WORKDAY adds a specific number of business days to a start date. Together with WEEKDAY, you can build SLA trackers, delivery estimators, and project burndown charts that respect real-world calendars without manual adjustment.
For dashboards, consider adding a Day Type column that classifies dates into categories beyond just Weekday and Weekend. Use SWITCH to map weekday numbers to Sales Day, Operations Day, Inventory Day, or whatever cadence your business follows. Slicing a pivot by Day Type instead of raw date gives stakeholders immediate insight into operational rhythm without showing them the underlying complexity.
If you work with international datasets, build a helper sheet with a single cell controlling the locale code. Reference it inside every TEXT formula as TEXT(A2,$Z$1&"dddd") where Z1 holds [$-409] or any other locale. Changing one cell instantly switches the entire workbook between English, French, Spanish, German, or any supported language. This pattern saves enormous time in multilingual reporting environments.
Finally, for power users, the LAMBDA function in Microsoft 365 lets you define your own DayName function once and reuse it everywhere. =LAMBDA(d,TEXT(d,"dddd")) named DayName lets you write =DayName(A2) throughout the workbook. This creates a personal mini-library of date utilities that makes formulas shorter, clearer, and more maintainable across complex models.
To consolidate everything, here are the practical habits professionals develop around day-of-week formulas. First, standardize on one return type across your entire team. Whether you choose 1 or 2 matters less than ensuring everyone uses the same value. Add it to your onboarding documentation and template files so new analysts inherit the standard automatically. Consistency prevents the slow accumulation of inconsistent formulas that eventually causes a high-stakes report to deliver wrong numbers.
Second, always validate your formula on known dates before deploying. January 1, 2026 is Thursday. July 4, 2026 is Saturday. December 25, 2026 is Friday. Memorize a few anchor dates so you can spot-check any new workbook in seconds. If WEEKDAY says July 4, 2026 is a Tuesday, something is wrong with your formula, your data, or your return type, and catching it early saves embarrassment later.
Third, prefer TEXT over CHOOSE when you need standard day names in a local language. CHOOSE is better when labels are custom strings unrelated to weekday names. Custom formatting is best when the value must remain a real date for downstream math. Use SWITCH when you need a default fallback or when mapping more than seven values. Each tool has a clear sweet spot, and choosing wrong creates fragility.
Fourth, document your formulas. Add a small explanation in a nearby cell or use the Name Manager to give complex formulas readable names. Future-you and your colleagues will thank you when reviewing a workbook six months from now. Excel files routinely outlive the people who built them, and clear documentation is the difference between a useful asset and an inscrutable black box.
Fifth, take advantage of practice tests and certification questions to lock in your understanding. Working through scenario-based problems trains you to recognize when to reach for WEEKDAY versus TEXT versus a custom format. The more variations you have seen, the faster you solve real-world problems on the job. Hands-on repetition with realistic data sets is the fastest way to build durable fluency with date functions.
Sixth, learn the related date functions that pair with WEEKDAY. EOMONTH gives the last day of a month, EDATE shifts dates by months, YEARFRAC computes fractional years between two dates, and ISOWEEKNUM returns the ISO week number. Mastering this family together turns you into the date expert on any team, capable of solving scheduling and reporting problems that stop other analysts cold.
Finally, remember that Excel is constantly evolving. Microsoft 365 introduces new functions and array behaviors that change best practices. Functions like SEQUENCE, LET, and LAMBDA dramatically simplify day-of-week calendars and reporting. Stay current by reading release notes, taking refresher quizzes, and exploring updated function lists so your formulas continue to use the cleanest available tools rather than legacy workarounds.