The WEEKDAY function in excel when used to input value of a date returns a number between 1 and 7 for day on that date. By default 1 refers to Sunday and 7 refers to Saturday. For use of WEEKDAY function below syntax can be used
Syntax of WEEKDAY function
=WEEKDAY(serial_number,[return_type])
Where
Serial number – All dates in excel are represented as numbers. Serial number is number for mentioned date. Formula will not work correctly if date is entered as text.
Return type – This argument is optional
1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel. Default week start for WEEKDAY function is Sunday.
| Return type | Number returned by excel |
| 1 – Default | 1 for Sunday to 7 for Saturday |
| 2 | 1 for Monday to 7 for Sunday |
| 3 | 0 for Monday to 6 for Sunday |
| 11 | 1 for Monday to 7 for Sunday |
| 12 | 1 for Tuesday to 7 for Monday |
| 13 | 1 for Wednesday to 7 for Tuesday |
| 14 | 1 for Thursday to 7 for Wednesday |
| 15 | 1 for Friday to 7 for Thursday |
| 16 | 1 for Saturday to 7 for Wednesday |
| 17 | 1 for Sunday to 7 for Saturday |
Note: Return type values from 11 to 17 were introduced from Excel 2010 onwards. This gives added flexibility if you want your week start day to change.
Example use of WEEKDAY function in Excel
Get Weekday from a Date with Weekday function in Excel
This example has date in cell A1 of excel and with help of Excel weekday function we will derive which day of week was on specified date.
Example
=WEEKDAY(A1)
Result
2 as on 28-August-2017 its Monday and start of week is on Sunday by default.
=WEEKDAY(DATE(2017,8,28))
Result
2 as on 28-August-2017 its Monday and start of week is on Sunday
Change week start day when using WEEKDAY function
If you want change first day of your week you can use optional argument. For example when using WEEKDAY function in excel and you want to change week start to Monday.
Example
=WEEKDAY(A2,11)
Result
1 as on 28-August-2017 its Monday and start of week is on Monday.
=WEEKDAY(A2,12)
Result
7 as on 28-August-2017 its Monday and start of week is on Tuesday.
=WEEKDAY(DATE(2017,8,28),12)
Result
7 as on 28-August-2017 its Monday and start of week is on Tuesday.
Get Day name from Date using weekday function in Excel 2019
Weekday function does not have option of returning name of day directly but you can get Day by using Choose function along with Weekday.
Example
=CHOOSE(WEEKDAY(B4),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)
Here WEEKDAY function get date details from cell B4 and return number between 1-7 and CHOOSE will use this number to return day from its list. In case you change week start in WEEKDAY by changing return type you will have to change list accordingly
Example
=CHOOSE((WEEKDAY(B4),11),”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”,”Sun”)
In this example Week start is changed to Monday and CHOOSE list is changed accordingly.
