Syntax. WEEKDAY(serial_number;return_type)
Definition. This function converts a date (serial number) into a weekday number. The weekday is returned as an integer from 1 (Sunday) through 7 (Saturday).
Arguments
serial_number (required). The date for which the weekday number is calculated.
return_type (optional). A number (1, 2, or 3) indicating the type of the return value. The types are defined as follows:
1 (or no value). 1 indicates Sunday, 2 indicates Monday, and so on, with 7 indicating Saturday.
2. 1 indicates Monday, 2 indicates Tuesday, and so on, with 7 indicating Sunday.
3. 0 indicates Monday, 1 indicates Tuesday, and so on, with 6 indicating Sunday.
In Excel 2010, the return types are extended. You can choose these additional types:
11. 1 indicates Monday, 2 indicates Tuesday, and so on, with 7 indicating Sunday.
12. 1 indicates Tuesday, 2 indicates Wednesday, and so on, with 7 indicating Monday.
13. 1 indicates Wednesday, 2 indicates Thursday, and so on, with 7 indicating Tuesday.
14. 1 indicates Thursday, 2 indicates Friday, and so on, with 7 indicating Wednesday.
15. 1 indicates Friday, 2 indicates Saturday, and so on, with 7 indicating Thursday.
16. 1 indicates Saturday, 2 indicates Sunday, and so on, with 7 indicating Friday.
17. 1 indicates Sunday, 2 indicates Monday, and so on, with 7 indicating Saturday.
Background. This function is useful for extracting the day of the week from a date.
Remember that you can use the TEXT() function (see Chapter 8) instead of the WEEKDAY() function to return a weekday as string:
=TEXT(TODAY(),"dddd")
See Also
For more information about saving dates in Excel, see the section titled The Excel Date System in Chapter 2.
Example. You might want to format Sundays in a date column in red. To do this, use conditional formatting. Select the date column. In Excel 2003 and earlier, select the Format/Conditional Formatting menu option and then select Formula in the list box. In Excel 2007 or Excel 2010, click the Conditional Formatting button in the Style group on the Start tab and select New Rule. Select Use A Formula To Determine Which Cells To Format as the rule type (see Figure 7-7).
Enter the formula
=WEEKDAY($B11,1)=1
and click the Format button to format the text (see Figure 7-8).
Figure 7-8. The Sundays in the date column are formatted in bold and red by using conditional formats.
The following examples show how the type parameter works:
=WEEKDAY("08/03/2008",1)returns1(Sunday).=WEEKDAY("08/03/2008",2)returns7.=WEEKDAY("08/03/2008",3)returns6.