Exceljet

Quick, clean, and to the point

Excel WEEKDAY Function

Excel WEEKDAY function
Summary 

The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.

Purpose 
Get the day of the week as a number
Return value 
A number between 0 and 7.
Syntax 
=WEEKDAY (serial_number, [return_type])
Arguments 
  • serial_number - The date for which you want to get the day of week.
  • return_type - [optional] A number representing day of week mapping scheme. Default is 1.
Version 
Usage notes 

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_typeSerial_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:

Result Meaning
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

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.

Return type Numeric result Day mapping
none 1-7 Sunday-Saturday
1 1-7 Sunday-Saturday
2 1-7 Monday-Sunday
3 0-6 Monday-Sunday
11 1-7 Monday-Sunday
12 1-7 Tuesday-Monday
13 1-7 Wednesday-Tuesday
14 1-7 Thursday-Wednesday
15 1-7 Friday-Thursday
16 1-7 Saturday-Friday
17 1-7 Sunday-Saturday

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 fore 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

Notes

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

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.