Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to get the day of the week from a date in Excel and Google Sheets
Excel & Google Sheets
=TEXT(A2,"dddd")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your date in A2.
- 2Full day name (Monday, Tuesday, ...): =TEXT(A2,"dddd").
- 3Abbreviated name (Mon, Tue, ...): =TEXT(A2,"ddd").
- 4Day number with Sunday=1: =WEEKDAY(A2) or =WEEKDAY(A2,1).
- 5Day number with Monday=1 (ISO/European): =WEEKDAY(A2,2).
- 6Check for a specific day, e.g. "is this a Friday?": =WEEKDAY(A2,2)=5.
Tips
- Detect weekends with one comparison: =WEEKDAY(A2,2)>5 returns TRUE for Saturday and Sunday.
- Flag weekends with a label: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday").
- Sort by day name chronologically (Mon, Tue, ...) by hidden helper column with WEEKDAY(A2,2) — TEXT sorts alphabetically (Fri, Mon, Sat).
- Apply conditional formatting to highlight weekends: rule formula =WEEKDAY(A2,2)>5.
- Custom day labels: =CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") gives you full control over names (including non-English).
- Count Mondays in a date range: =SUMPRODUCT(--(WEEKDAY(A2:A100,2)=1)).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does TEXT(A2,"dddd") show a name in a different language?
TEXT respects your spreadsheet locale. Excel uses Windows system locale; Google Sheets uses File → Settings → Locale. The math is identical — only the display language differs. To force English regardless, build labels with CHOOSE: =CHOOSE(WEEKDAY(A2),"Sun","Mon",...).
How do I check if a date is a Monday?
Use WEEKDAY mode 2 so Monday=1: =WEEKDAY(A2,2)=1 returns TRUE for Mondays. For Friday: =WEEKDAY(A2,2)=5. For weekends: =WEEKDAY(A2,2)>5.
Why does my day number not match my expectation?
WEEKDAY's second argument controls which day is 1. Default (no second argument or 1) makes Sunday=1; mode 2 makes Monday=1; mode 3 makes Monday=0. Pick mode 2 for ISO/European weeks, mode 1 for US weeks.
How do I get just the weekday number for the current date?
Combine TODAY with WEEKDAY: =WEEKDAY(TODAY(),2) returns today's weekday number with Monday=1.
How do I sort data by day of the week in calendar order?
Add a helper column with =WEEKDAY(A2,2) and sort by that. Sorting by the TEXT name directly returns alphabetical order (Fri, Mon, Sat, Sun, Thu, Tue, Wed) — usually not what you want.
Does WEEKDAY work on a text-formatted date?
No — it returns #VALUE!. Wrap in DATEVALUE first: =WEEKDAY(DATEVALUE(A2),2). Better: fix the source so the cell holds a real date.