WEEKDAY Function

The WEEKDAY function takes a date and returns a number between 1-7 representing the day of the week. The WEEKDAY function takes two arguments: serial_number and return_type. Serial_number should be a valid Excel date in serial number format. Return_type is an optional numeric code that controls which day of the week is considered the first day. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, as seen in the table below:

WEEKDAY supports several numbering schemes, controlled by the return_type argument. Return_type is optional and defaults to 1. The table below shows available return_type codes, the numeric result of each code, and which day is the first day in the mapping scheme.

Note: the WEEKDAY function will return a value even when the date is empty. Take care to trap this result if blank dates are possible.

Examples

By default and without a value for return_type, WEEKDAY starts counting on Sunday:

=WEEKDAY("3-Jan-21") // Sunday, returns 1
=WEEKDAY("4-Jan-21") // Monday, returns 2

To configure WEEKDAY to start on Monday, set return_type to 2:

=WEEKDAY("3-Jan-21",2) // Sunday, returns 7
=WEEKDAY("4-Jan-21",2) // Monday, returns 1

In the example shown above, the formula in D5 (copied down) is:

=WEEKDAY(B5) // Sunday start

The formula in E5 (copied down) is:

=WEEKDAY(B5,2) // Monday start

Day names instead of numbers

WEEKDAY always returns a number. If you want a day name like "Mon" or "Monday" instead, you don't need WEEKDAY at all. To display a date as a day name, apply a custom number format like "ddd" (Mon) or "dddd" (Monday); the cell keeps the full date, so it still works in other formulas. To get the day name as text in another cell, use the TEXT function:

=TEXT("4-Jan-21","ddd") // returns "Mon"
=TEXT("4-Jan-21","dddd") // returns "Monday"
=UPPER(TEXT("4-Jan-21","ddd")) // returns "MON"

For more detail, see Get day name from date. To create your own abbreviations (like "M" or "Tu"), you can combine WEEKDAY with CHOOSE, as explained in Custom weekday abbreviation.

Notes

• By default, WEEKDAY returns 1 for Sunday and 7 for Saturday.
• WEEKDAY returns a value (7) even if the date is empty.