The WEEKDAY function in Excel is used to return the day of the week from a given date, as a number from 1 to 7. 📆 For example, =WEEKDAY("2024-05-01") returns 4, which corresponds to Wednesday by default. Whether you’re building a timesheet, calendar, project timeline, or automation formula, this function helps you extract weekday information quickly and accurately. ✅
🧠 What Does WEEKDAY Do in Excel?
The WEEKDAY function converts a standard date into a number (1 through 7) representing the day of the week. You can also customize which day is considered the start of the week.
Syntax:
=WEEKDAY(serial_number, [return_type])
serial_number: The date (e.g., “2024-05-01” or a cell reference likeA2)[return_type]: Optional. Determines which day of the week is considered day 1
🗓️ WEEKDAY Return Type Options
| Return Type | Week Start | Day Numbers |
|---|---|---|
| 1 (default) | Sunday | 1=Sun, 2=Mon, …, 7=Sat |
| 2 | Monday | 1=Mon, 2=Tue, …, 7=Sun |
| 3 | Monday | 0=Mon, 1=Tue, …, 6=Sun |
| 11–17 | Custom | You define start day |
✏️ Example Formulas
=WEEKDAY("2024-04-22") → 2 (Monday)
=WEEKDAY(A2, 2) → Converts date in A2, Monday as day 1
Use WEEKDAY to:
- Sort data by weekday
- Filter weekends vs weekdays
- Highlight specific weekdays (e.g., all Mondays)
🔄 Combine WEEKDAY with TEXT to Get Day Names
If you prefer the day name instead of a number:
=TEXT(A2, "dddd") → Monday
=TEXT(A2, "ddd") → Mon
This is great for formatting dashboards and reports with friendly day labels. 🗒️
🔧 Keyboard Shortcuts for WEEKDAY in Excel
| Action | Windows | macOS | Ubuntu (LibreOffice Calc) |
|---|---|---|---|
| Start formula | = | = | = |
| Insert function dialog | Shift + F3 | Shift + F3 | Ctrl + F2 |
| Open cell formatting | Ctrl + 1 | Cmd + 1 | Ctrl + 1 |
| Accept formula | Enter | Return | Enter |
👉 Use Shift + F3 and search “WEEKDAY” in the Insert Function window to autofill the syntax.
📊 Real-World Use Cases for WEEKDAY
| Scenario | Formula Example | Output |
|---|---|---|
| Determine if date is a weekend | =IF(WEEKDAY(A2, 2)>5, "Weekend", "Weekday") | “Weekend” or “Weekday” |
| Schedule email only on Mondays | =IF(WEEKDAY(A2,2)=1, "Send Email", "Wait") | Logic automation |
| Highlight all Fridays | Use conditional formatting with =WEEKDAY(A1,2)=5 | Colors all Friday cells |
| Split tasks by day | =TEXT(A2, "dddd") | Displays “Tuesday” |
💡 Bonus: WEEKDAY + CHOOSE for Custom Day Names
Want to show custom weekday labels (e.g., in another language)?
=CHOOSE(WEEKDAY(A2), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
You can replace the text with:
- Localized day names
- Emoji:
🌞,📅, etc. - Abbreviations like
Su,Mo, etc.
🐧 WEEKDAY in Ubuntu/Linux (LibreOffice Calc)
LibreOffice Calc also supports the WEEKDAY function:
=WEEKDAY(A2; 1)
- Use
;instead of,for arguments - Shortcut to insert function:
Ctrl + F2 - You can format the cell as
"NNN"or"NNNN"to show day names
🧩 Combine with IF, TEXT, or CHOOSE just like in Excel!
⚠️ Common Mistakes with WEEKDAY
- ❌ Forgetting to format your input as a valid date
- ❌ Mixing up return types (1 vs 2 can shift results)
- ❌ Using
TEXT()where you actually need logic (e.g., “Friday” ≠ 5) - ❌ Comparing day numbers without understanding return type logic
✅ Tip: Always specify your return_type to avoid confusion across teams or reports!
FAQs
What does WEEKDAY do in Excel?
It returns a number (1–7) representing the day of the week from a date.
Can I change the start of the week in WEEKDAY?
Yes! Use the return_type parameter (e.g., 2 = Monday as day 1).
How do I convert a date to a weekday name?
Use =TEXT(A1, "dddd") to display full day names like “Tuesday”.
Can I use WEEKDAY in conditional formatting?
Absolutely! Example: =WEEKDAY(A1,2)>5 to highlight weekends.
What if my date is in text format?
Wrap it in DATEVALUE() like =WEEKDAY(DATEVALUE("2024-05-01")).
Does WEEKDAY work in Google Sheets and LibreOffice?
Yes! Syntax is nearly identical across platforms.
✅ Final Thoughts on Excel WEEKDAY Function
The WEEKDAY function in Excel is a powerful tool that turns raw dates into actionable insights. Whether you’re categorizing tasks by days, filtering for business days, or labeling charts, WEEKDAY helps bring clarity and logic to your data. 📅🧠
Pair it with functions like TEXT, CHOOSE, or IF, and you’ll unlock a whole new level of Excel efficiency—no calendar app needed. 🚀
So next time you’re building a schedule or dashboard, remember: Excel knows what day it is. You just need to ask. 😉
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment