How to use WEEKDAY function – MS Excel

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 typeNumber returned by excel
1 – Default1 for Sunday to 7 for Saturday
21 for Monday to 7 for Sunday
30 for Monday to 6 for Sunday
111 for Monday to 7 for Sunday
121 for Tuesday to 7 for Monday
131 for Wednesday to 7 for Tuesday
141 for Thursday to 7 for Wednesday
151 for Friday to 7 for Thursday
161 for Saturday to 7 for Wednesday
171 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 of WEEKDAY function in Excel

=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
1 as on 28-August-2017 its Monday and start of week is on Sunday

Use of WEEKDAY function in Excel

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.

=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.