Get the Day of the Week (1 to 7) for a Date in Excel - WEEKDAY
Use a function in Excel to get the number of the day in a week, from 1 to 7.
This allows you to use your dates to perform more complicated calculations, such as working hours and compensation, scheduling, shipping, and many other types of calculations where it is important to know on what day of the week a date falls.
For this, we use the WEEKDAY() function.
Syntax
=WEEKDAY(serial_number, [return_type])
| Argument | Description |
|---|---|
|
Serial_number |
This is the date that you want to use for the calculation. You simply need to reference a cell that contains a date for this argument to work. If you want, you can also enter the date by hand using the DATE() function, but that is almost never recommended as it makes the spreadsheet harder to use and maintain. |
|
[Return_type] |
This argument tells the function how to number the days of the week. If you leave this argument empty, Sunday will be 1 and Monday will be 2 etc. If you put a 2 in for this argument, Monday will be 1 and Sunday will be 7. Look below for a full list of options for this argument. |
[] means the argument is optional.
Return_type Arguments for the WEEKDAY Function
There are 10 different values that can be entered for the return_type argument.
|
Value |
Description |
|
1 or omitted |
Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel. |
|
2 |
Numbers 1 (Monday) through 7 (Sunday). |
|
3 |
Numbers 0 (Monday) through 6 (Sunday). |
|
11 |
Numbers 1 (Monday) through 7 (Sunday). |
|
12 |
Numbers 1 (Tuesday) through 7 (Monday). |
|
13 |
Numbers 1 (Wednesday) through 7 (Tuesday). |
|
14 |
Numbers 1 (Thursday) through 7 (Wednesday). |
|
15 |
Numbers 1 (Friday) through 7 (Thursday). |
|
16 |
Numbers 1 (Saturday) through 7 (Friday). |
|
17 |
Numbers 1 (Sunday) through 7 (Saturday). |
Return the Day of the Week Basic Example
- Make sure you have a cell with a date in it.
- Type =WEEKDAY( in the desired cell:
- Select the Date:
- Hit Enter.
By default, Sunday is the first day of the week. Look to the example below to change which day starts the week.
Return the Day of the Week with Different Starting Numbers
This is almost exactly the same as the above example except that we now use the second argument.
- Make sure to have a cell with a date.
- Type =WEEKDAY( into a cell:
- Select the cell that contains the date:
- Type a comma to go to the next argument and then type a number for the corresponding option or select it from the drop-down menu that appears:
- Hit Enter and you're done.
By putting a 2 in for the second argument, you can see that Sunday is now the 7th day of the week for this calculation.
Notes
I almost always put a 2 in for the return_type argument for the WEEKDAY function because it just seems to make more sense that the first day of the regular work week should be 1.
The main thing is to remember that the number that is output for a given day can be changed; you should not blindly trust the output of this function if it was created by someone other than yourself.
Make sure to download the accompanying spreadsheet so you can see this in action.
Question? Ask it in our Excel Forum
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)