Day of Week in Excel: Practical Formulas, TEXT, and Tips
Learn how to derive the day of week from a date in Excel using WEEKDAY, TEXT, and related techniques. Includes locale considerations, practical examples, and a reusable reporting template.
Learn how to convert any date to its day of the week in Excel using WEEKDAY with an adjustable return type and TEXT for full or abbreviated day names. This guide covers practical formulas, locale considerations, and ready-to-use templates for reporting.
What the term means in Excel
In Excel, the phrase day of week refers to identifying which day a given date falls on, such as Monday or Friday. This capability is essential for scheduling, reporting, and analytics. The core idea is to transform a date value into a day label or number that you can use in calculations or appearances in your workbook. When you work with day-of-week data, you gain a predictable axis for weekly patterns, due dates, and calendar-driven insights. This section sets the stage for practical, repeatable steps you can apply in any workbook, whether you’re a student, analyst, or planner. The keyword day of week excel should appear naturally as you explore examples and formulas designed for real work.
The WEEKDAY function: core behavior
WEEKDAY is the central tool for extracting a numeric day-of-week from a date. The syntax is WEEKDAY(serial_number, [return_type]). The serial_number is your date cell, and return_type controls how the days are numbered. With the default return_type of 1, Sunday = 1 and Saturday = 7. This simple structure lets you quickly convert dates into consistent weekday numbers for further logic, such as IF statements or conditional formatting. Understanding the default helps you plan how to reshape the results for your needs and makes it easier to pair WEEKDAY with other functions like IF or CHOOSE.
Return types and what they mean
Excel offers several return_type options to fit different conventions. Return_type 1 assigns Sunday as 1; Return_type 2 assigns Monday as 1 (ending Sunday as 7); Return_type 3 assigns Monday as 1 but Sunday as 0. These variations let you align weekday values with regional calendars or downstream logic. Selecting the right option matters when you’re building dashboards or performing week-based comparisons. If your organization uses Monday-first weeks, return_type 2 is often the most intuitive choice.
Getting full day names with TEXT
TEXT provides a human-friendly way to display the day-name instead of a number. For example, =TEXT(A2, "dddd") returns the full weekday name (Monday, Tuesday, etc.), while =TEXT(A2, "ddd") yields abbreviated forms (Mon, Tue, ...). Combined with a date, TEXT creates readable reports without additional lookup tables. The day-name result can be used directly in charts, pivot tables, or export-ready summaries. TEXT is especially useful when you want the final report to show clear, descriptive labels.
Abbreviated day names and custom formats
If you prefer shorter labels, use "ddd" for three-letter abbreviations or create a custom format to show initial letters (e.g., using =LEFT(TEXT(A2, "dddd"),1) to extract the first letter). Custom formats can also be used in conditional formatting to color-code weekends and weekdays based on the day name. By combining TEXT with conditional logic, you can produce compact, readable outputs suitable for dense worksheets or dashboards.
Locale and calendar considerations
Day names are locale-sensitive. In some locales, weekends or week-start days differ, which can shift how your formulas read dates. To ensure consistency, set the workbook locale or explicitly format days using TEXT with the appropriate locale settings (e.g., use the locale-aware TEXT function variants if your Excel supports them). When sharing workbooks internationally, document the chosen convention and test with sample dates to verify accuracy across regions.
Examples: converting a date column to days
Suppose column A contains dates. To get the day number with Sunday as 1, use =WEEKDAY(A2,1). To get Monday as 1, use =WEEKDAY(A2,2). To display the full day name, combine with TEXT: =TEXT(A2, "dddd"). For weekends-only logic, you can test with =IF(WEEKDAY(A2,2)>5, "Weekend","Weekday"). These examples illustrate how to move from raw dates to readable, actionable weekday data.
Practical example: building a weekly schedule
Create a small table with Date, DayName, DayNumber. In DayName, fill with =TEXT(A2, "dddd"). In DayNumber, use =WEEKDAY(A2,2). This structure supports sorting by date while grouping tasks by weekday. You can extend the table with a shift indicator or reminders that trigger on specific days, enabling reliable planning for teams and projects.
Working with date ranges and array-style calculations
For a range of dates, you can fill down formulas to derive DayName and DayNumber across the entire period. If you need a compact array result, you can use dynamic array functions (where supported) like =TEXT(FILTER(A2:A100, A2:A100<>""), "dddd"). For older versions, drag formulas down, ensuring relative references adapt correctly as you copy them. This approach supports weekly analysis and consistent reporting across dates.
Troubleshooting common issues (dates stored as text)
Dates stored as text can break WEEKDAY and TEXT results. Convert with VALUE or DateValue, or use DATE(year, month, day) to reconstruct a true date. Always verify your date cells are truly numeric dates by checking with ISNUMBER or by applying a date format. If your data source uses mixed date formats, standardize them before applying weekday calculations to avoid inconsistent results.
Best practices and a reusable template for day-of-week in reports
Create a small, reusable template that includes: a date input column, a DayName column using TEXT, and a DayNumber column using WEEKDAY with your preferred return_type. Document the chosen locale and return_type in the header of your workbook for future users. Save the template as a workbook template (.xltx) to accelerate weekly reporting and ensure consistency across projects.
Tools & Materials
- Microsoft Excel (Windows or macOS) or compatible spreadsheet app(Excel 2013+ recommended; ensure date cells are true dates, not text)
- A sample date column(Include dates like 2026-03-31 or 03/31/2026 for testing)
- Locale settings access(Set region to test locale-specific day names)
- Text editor or notes app(For documenting conventions and template notes)
Steps
Estimated time: 15-25 minutes
- 1
Identify the date column
Select the column that contains your dates and confirm they are entered as proper Excel dates. If dates are mixed with text, standardize them before applying weekday formulas. This step ensures results are reliable across the workbook.
Tip: Check a sample of dates by changing their cell format to General and verifying a numeric serial appears. - 2
Choose a baseline return_type
Decide whether you want Sunday as 1 (return_type 1) or Monday as 1 (return_type 2). This choice affects subsequent calculations and how you present day numbers in reports.
Tip: For most Western workweeks, return_type 2 aligns with Monday-first planning. - 3
Get the day number with WEEKDAY
Enter =WEEKDAY(A2, 1) for Sunday-start or =WEEKDAY(A2, 2) for Monday-start. Copy down to cover your date range. This numeric result is the backbone for conditional logic and scheduling rules.
Tip: Keep the formula dynamic to handle new dates added later. - 4
Display full day name with TEXT
Convert the date to a readable name using =TEXT(A2, "dddd"). This yields Monday, Tuesday, etc., suitable for dashboards and reports that require descriptive labels.
Tip: TEXT can be combined with WEEKDAY for hybrid results if needed. - 5
Get abbreviated day name
If you prefer short labels, use =TEXT(A2, "ddd"). This returns Mon, Tue, etc., which can save space on tight dashboards.
Tip: Abbreviations work well in charts with limited axis space. - 6
Handle blanks gracefully
Wrap your formula in an IF to avoid errors on empty rows, e.g., =IF(A2="","",TEXT(A2, "dddd")). This keeps your sheet clean as you add daily data.
Tip: Blank handling prevents misleading results during analysis. - 7
Localize day names when needed
If your workbook serves multiple locales, test day-name outputs across regions and adjust formatting or locale settings accordingly.
Tip: Document locale choices in a README within the workbook. - 8
Apply to a range or table
If you’re using Excel Tables, XLOOKUP, or dynamic arrays, ensure the formulas fill automatically as you add new dates. Consider using structured references for clarity.
Tip: Tables reduce maintenance and prevent broken references.
People Also Ask
How do I get the day name from a date in Excel?
Use TEXT with a date, e.g., =TEXT(A2, "dddd") for full day names or =TEXT(A2, "ddd") for abbreviations. Combine with WEEKDAY if you need numeric day data too.
You can get the day name by using TEXT with your date, like =TEXT(A2, "dddd"), or use WEEKDAY to get the day number first.
What is the difference between return_type 1 and 2 in WEEKDAY?
Return_type 1 starts weeks on Sunday (Sunday = 1). Return_type 2 starts weeks on Monday (Monday = 1). Choose based on your regional calendar or reporting needs.
Return_type 1 starts on Sunday, return_type 2 starts on Monday; pick the one that matches your workflow.
How can I handle dates stored as text when calculating the day of week?
Convert text to a real date with VALUE or DATEVALUE, or reconstruct with DATE(year, month, day). Then apply WEEKDAY or TEXT to get the day.
If dates are text, convert them first, then use WEEKDAY or TEXT to get the day.
Can I get the day name for a whole column without dragging formulas?
Yes. Use Excel Tables or dynamic array functions (where available) to spill results automatically as dates are added. Adjust the references accordingly.
If you use a table or dynamic array, the day names can spill and update automatically.
How does locale affect day names in Excel?
Day names depend on the system locale. Ensure workbook locale matches your audience or explicitly format names to avoid misalignment.
Locale settings can change how days appear; test with representative dates.
Watch Video
The Essentials
- Use WEEKDAY to extract weekday as a number with a clear convention
- TEXT converts dates to human-friendly day names for reports
- Wrap formulas with IF to handle blanks safely
- Choose a return_type that matches your locale and planning style
- Create a reusable template for consistent weekday reporting
