Weekday Function

Purpose

Return value

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.

Using the 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 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.

Purpose

Return value

Syntax

=WEEKNUM(serial_num,[return_type])

• serial_num - A valid Excel date in serial number format.
• return_type - [optional] The day the week begins. Default is 1.

Using the WEEKNUM function

The WEEKNUM function takes a date and returns a number between 1 and 54 that corresponds to the week of the year. By default, the WEEKNUM function starts counting on the week that contains January 1 and increments week numbers on Sunday. Typically the last week number in a year is 53. However, WEEKNUM will return 54 at the end of some years, like 2000 and 2028.

The WEEKNUM function accepts two arguments , serial_num and return_type . The serial_num argument must have a valid Excel date . The return_type argument controls what day of the week begins a new week number. Return_type is optional and defaults to 1, which sets new week numbers to start on Sunday. When return_type is set to 2, week numbers begin on Monday.

With a return_type of 1-17, week number 1 in a given year is assigned to the week that contains January 1. With return_type 21, week 1 is the week containing the first Thursday of the year, following ISO 8601 . The table below summarizes return_type options .

Example #1 - basic usage

The formulas below return the week number for the last day of 2020 and the first day of 2021:

=WEEKNUM("31-Dec-2020") // returns 53
=WEEKNUM("1-Jan-2021") // returns 1

Example #2 - return type

The return_type argument controls what day of the week a new week number should begin. By default, return_type is 1, and numbers increment on Sunday. When return_type is provided as 2, week numbers begin on Monday. For example, January 3, 2021, is a Sunday and, WEEKNUM will return 2 since new numbers start on Sundays:

=WEEKNUM("3-Jan-2021") // returns 2

However, when return_type is set to 2, WEEKNUM will return 1 and start week 2 on Monday:

=WEEKNUM("3-Jan-2021",2) // returns 1
=WEEKNUM("4-Jan-2021",2) // returns 2

Note: the examples above show dates as text values for readability, but working with native Excel dates is more reliable. To create a date from scratch in a formula, you can use the DATE function .