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])
  • 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.

How to use 

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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.