Day of Week in Google Sheets: Formulas & Tips
Learn day-of-week calculations in Google Sheets with WEEKDAY, TEXT, and conditional logic. Get practical formulas and templates for scheduling, dashboards, and data validation.
By the end of this guide you will be able to extract the day of week from any date in Google Sheets using WEEKDAY, TEXT, and SWITCH, plus practical templates for dashboards. You’ll understand numeric and name formats, locale considerations, and real-world examples for scheduling, validation, and reporting. This quick answer sets you up for a hands-on, step-by-step dive.
Why the day of week matters in Google Sheets
According to How To Sheets, day-of-week calculations underpin scheduling, forecasting, and reporting in business spreadsheets. In Google Sheets you often need to orient dates within weeks to create weekly dashboards, determine business days, or group data by weekdays. When you know the weekday for each date, you can automate scheduling, flag weekends, or route tasks to specific team members. This foundational concept is essential for building time-aware templates that scale from student projects to small-business dashboards. By mastering weekday logic, you unlock a wide range of automations and validations that save time and reduce errors in daily data work.
For students, professionals, and small business owners, weekday-aware sheets enable you to quickly summarize weekly trends, plan workloads, and coordinate across teams. As you practice, you’ll gain confidence in choosing the right function (WEEKDAY, TEXT, SWITCH) for each scenario and in handling edge cases like blank cells or non-date inputs. This knowledge forms the backbone of robust Google Sheets workflows that you can reuse across projects.
Quick formulas to extract day of week as a number
The simplest way to get the day of week as a number is WEEKDAY. Use the default type to start on Sunday or adjust to start on Monday. Examples:
- =WEEKDAY(A2) returns a number 1–7 where 1 corresponds to Sunday when A2 is a date.
- =WEEKDAY(A2, 2) starts the week on Monday, so Monday = 1 and Sunday = 7.
- =WEEKDAY(A2, 3) returns 0–6 with Monday = 1 and Sunday = 0, depending on the type you pick.
If A2 contains a non-date value, wrap WEEKDAY in IFERROR to avoid errors, e.g. =IFERROR(WEEKDAY(A2,2),"-").
Turn a date into a weekday name (Mon, Tue, etc.)
To display the actual day name, TEXT is the most flexible approach. It respects your spreadsheet locale and can show full or abbreviated names:
- =TEXT(A2, "dddd") returns the full weekday name (e.g., Monday).
- =TEXT(A2, "ddd") returns the abbreviated name (e.g., Mon).
Combine with IF to handle blanks or non-dates: =IF(ISDATE(A2), TEXT(A2, "dddd"), "").
Tip: TEXT’s output depends on your locale settings, so verify language consistency across sheets and workbooks.
Locale-aware day names and formats
Day names are locale-sensitive. If your sheet’s locale is set to Spanish, the same formula =TEXT(A2, "dddd") will yield lunes, martes, etc. To ensure consistency, set the spreadsheet locale in File > Settings > Locale. This is especially important when sharing dashboards with teammates who read different languages. If you regularly switch locales, consider creating a small helper cell that uses the locale-aware TEXT to render the name in the user’s language.
When distributing sheets globally, document the locale used for day names so readers aren’t surprised by language differences. This awareness also helps when exporting data to other apps that rely on localized date strings.
Practical use cases: scheduling, dashboards, and data validation
Day-of-week data powers a range of practical tasks:
- Scheduling: label entries as Weekday vs. Weekend and route tasks accordingly. Example: =IF(WEEKDAY(A2, 2)>5, "Weekend", "Weekday").
- Dashboards: build weekly summaries by grouping rows with =TEXT(A2, "dddd") and aggregating by week-day labels.
- Data validation: restrict date inputs to workdays with =IF(WEEKDAY(A2, 2)>5, FALSE, TRUE).
- Dash-ready flags: combine with conditional formatting to highlight Sundays and Saturdays for quick visuals.
Copy-paste-ready patterns:
- Weekday name column: =TEXT(A2, "dddd")
- Weekday short name: =TEXT(A2, "ddd")
- Weekend check (Mon-start week): =IF(WEEKDAY(A2, 2)>5, "Weekend","Weekday")
Common mistakes and how to fix them
Common pitfalls include treating dates as text, which breaks WEEKDAY and TEXT calculations. Always verify input types: dates should be real date serial numbers, not strings. If dates come from external sources, wrap them with DATEVALUE to convert, e.g. =DATEVALUE(A2) when needed. Another pitfall is locale mismatch: if a sheet’s locale differs from your readers, day names may appear in unexpected languages. Finally, beware using TEXT for numeric sorting; if you sort by the day name, you’ll sort alphabetically rather than chronologically. Use WEEKDAY for sorting when precise weekday order is required.
Step-by-step examples you can copy-paste
- Ensure your date column exists (A2:A). Enter a sample date in A2, such as 2026-03-21.
- In B2, get the weekday number starting on Monday: =WEEKDAY(A2, 2)
- In C2, display the full day name: =TEXT(A2, "dddd")
- In D2, display the short day name: =TEXT(A2, "ddd")
- In E2, label Weekend or Weekday (Mon-start): =IF(WEEKDAY(A2, 2)>5, "Weekend", "Weekday")
- Copy these formulas down the column to fill your date range. Optional: wrap with IFERROR to handle blanks or non-dates: =IFERROR(WEEKDAY(A2,2),"") and similarly for TEXT.
Tip: If you plan to apply the logic to an entire column, consider using ARRAYFORMULA to auto-fill results for A2:A, e.g., =ARRAYFORMULA(IF(A2:A="",,TEXT(A2:A, "dddd"))).
Extending with logic: combining day-of-week with IF, SWITCH, or IFS
You can drive complex decisions based on day-of-week by combining WEEKDAY with SWITCH or IFS. For example, to assign tasks by weekday names: =IFS(TEXT(A2, "dddd")="Monday", "Team A", TEXT(A2, "dddd")="Tuesday", "Team B", TRUE, "Team C"). For numeric control, you can use =SWITCH(WEEKDAY(A2, 2), 1, "Mon", 2, "Tue", 3, "Wed", 4, "Thu", 5, "Fri", 6, "Sat", 7, "Sun"). These patterns scale to larger decision trees and dashboards.
Quick-reference cheat sheet for day of week in Sheets
- Numeric day (Mon-start): =WEEKDAY(A2, 2)
- Full day name: =TEXT(A2, "dddd")
- Short day name: =TEXT(A2, "ddd")
- Weekend check with Mon-start: =IF(WEEKDAY(A2, 2)>5, "Weekend", "Weekday")
- Locale-aware names: Ensure spreadsheet locale is set under File > Settings > Locale
- ArrayFormula for names: =ARRAYFORMULA(IF(A2:A="",,TEXT(A2:A, "dddd")))
Tools & Materials
- Google Sheets access(Use any date in column A for demonstrations.)
- Sample date values (ISO format)(Dates in column A (YYYY-MM-DD) to ensure consistency.)
- Locale settings reference(Set to your preferred language in File > Settings > Locale.)
- Template for weekday dashboards(Optional copy for quick-start dashboards.)
- Conditional formatting rules(Have rules ready to colorize weekends or weekdays.)
- Pivot-ready date table(Prepare a table for pivot-based analysis by weekday.)
Steps
Estimated time: 15-20 minutes
- 1
Identify the date column
Locate your date column (A) and confirm values are real dates, not text. This ensures WEEKDAY and TEXT return correct results.
Tip: If dates come from text, convert with =DATEVALUE() or ensure the source data is properly formatted. - 2
Compute weekday number
In the target cell, use =WEEKDAY(A2, 2) to get a 1–7 value starting on Monday. This numeric baseline is great for sorting and logic.
Tip: Use type 2 to align with common Monday-start calendars. - 3
Create a weekday name
Convert the date to a readable day name with =TEXT(A2, "dddd"). For abbreviated names use =TEXT(A2, "ddd").
Tip: Remember to escape quotes in JSON strings when including these formulas. - 4
Handle blanks and non-dates
Wrap formulas with IF(ISDATE(A2), ... , "") or IFERROR to avoid errors when dates are missing.
Tip: This keeps your dashboard clean and error-free. - 5
Apply to an entire column
If you need results for many rows, use ARRAYFORMULA to fill automatically, e.g., =ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A, "dddd"))).
Tip: ARRAYFORMULA saves time when working with large datasets. - 6
Integrate with rules
Link the day-of-week results to conditional formatting or data validation rules to automate styling and checks.
Tip: Test on a small sample before applying to large ranges.
FAQ
What is the best function to get the day of week in Sheets?
WEEKDAY returns a numeric day that you can map to names, while TEXT converts a date directly to a day name. For most dashboards, use WEEKDAY for logic and TEXT for display.
Use WEEKDAY for numeric day calculations and TEXT for readable day names in your dashboards.
How do I start the week on Monday in WEEKDAY?
Use WEEKDAY(date, 2). This sets Monday as 1 and Sunday as 7, which aligns with many business calendars.
Set the second parameter to 2 to start the week on Monday.
Can I get 3-letter day names easily?
Yes. Use =TEXT(date, "ddd") to get short names like Mon, Tue, Wed. For full names use "dddd".
Use TEXT with the 'ddd' format for short names.
What about locale differences?
Day names honor the spreadsheet locale. Change Locale in File > Settings to match your audience.
Locale affects day names; set your locale to match your readers.
Can I apply this in data validation?
Yes. Use WEEKDAY to restrict input to weekdays, e.g., =WEEKDAY(A2,2)<=5 in a validation rule.
You can enforce weekday-only inputs with a WEEKDAY check.
Why might my date not be recognized as a date?
If inputs come from external data, they may be text. Convert with =DATEVALUE() or ensure source data is date-formatted.
Check the data type; convert strings to dates before calculations.
Watch Video
The Essentials
- Use WEEKDAY for a quick numeric day
- TEXT(A2, "dddd") yields full day names
- Locale settings affect language of day names
- Combine with IF/SWITCH for conditional logic
- Test with sample dates to ensure accuracy
