The Excel WEEKDAY function is a Date & Time formula that finds the day of the week of a given date. The WEEKDAY function returns an integer indicating the day's index according to the specified day of the week mapping scheme. In this guide, we’re going to show you how to use the Excel WEEKDAY function and also go over some tips and error handling methods.
- All Excel versions (return_type numbers between 11 and 17 were added in 2010 version)
Excel WEEKDAY Function Syntax
|serial_number||The date of the day you want. Microsoft recommends using the DATE or other similar functions that return a date as a serial number.|
|[return_type]||Optional. A number that determines the day of week mapping scheme. The dDefault value is 1. See the table below for available types.|
|Return_type||Number returned by Excel WEEKDAY
|1 or omitted||Numbers 1 (Sunday) through 7 (Saturday). Behaves like in previous versions of Microsoft Excel.|
|2||Numbers 1 (Monday) through 7 (Sunday).|
|3||Numbers 0 (Monday) through 6 (Sunday).|
|11||Numbers 1 (Monday) through 7 (Sunday).|
|12||Numbers 1 (Tuesday) through 7 (Monday).|
|13||Numbers 1 (Wednesday) through 7 (Tuesday).|
|14||Numbers 1 (Thursday) through 7 (Wednesday).|
|15||Numbers 1 (Friday) through 7 (Thursday).|
|16||Numbers 1 (Saturday) through 7 (Friday).|
|17||Numbers 1 (Sunday) through 7 (Saturday).|
To find the day of the week of a date, provide a date as the serial_number argument of the Excel WEEKDAY function. Microsoft recommends using serial numbers, instead of date strings like "2/5/2019". A serial number date can be generated via functions like DATE.
You can also set a number for the [return_type] argument. This number specifies the numbering scheme. You can omit the argument to return a number based on a week starts on a Sunday and ends on a Saturday.
- Use the DATE function to generate a date serial number.
- [return_type] numbers between 11 and 17 were added in Excel 2010. You can still use the Excel WEEKDAY function with numbers between 1 and 3.
- [return_type] 2 and 11 work identically.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
If the start_date is not a valid date, Excel WEEKDAY function returns the #VALUE! error value. Microsoft recommends using a formula to generate a valid date serial value.