# Excel WEEKDAY Function

Related Function:

WEEKNUM

## Function Description

The Excel Weekday function returns an integer representing the day of the week for a supplied date.

The syntax of the function is:

WEEKDAY( serial_number, [return_type] )

Where the function arguments are as follows:

serial_number-The Excel date that you want to return the weekday of.
[return_type]-An optional integer argument that specifies which integers are to be assigned to each weekday. Possible values are:
[return_type]Weekday Numbering
1Sunday = 1, Monday = 2, ... , Saturday = 7
2Monday = 1, Tuesday = 2, ... , Sunday = 7
3Monday = 0, Tuesday = 1, ... , Sunday = 6
11Monday = 1, Tuesday = 2, ... , Sunday = 7
12Tuesday = 1, Wednesday = 2, ... , Monday = 7
13Wednesday = 1, Thursday = 2, ... , Tuesday = 7
14Thursday = 1, Friday = 2, ... , Wednesday = 7
15Friday = 1, Saturday = 2, ... , Thursday = 7
16Saturday = 1, Sunday = 2, ... , Friday = 7
17Sunday = 1, Monday = 2, ... , Saturday = 7

If omitted, the [return_type] argument is set to the default value 1 (i.e. Sunday = 1, Monday = 2, ... , Saturday = 7).

Note: only options 1, 2 & 3 above are available in Excel 2007 and earlier.

Note that it is advised that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date settings on your computer. Therefore the serial_number argument for the Weekday function should be input as either:

• A reference to a cell containing a date
or
• A date returned from another function or formula.

## Weekday Function Examples

Column A of the spreadsheet below stores the dates of a series of events. The Weekday function, in column B of the spreadsheet, shows the weekday of each date in column A.

Formulas:

AB
1DateWeekday
231-Dec-2012=WEEKDAY( A2 )
301-Jan-2013=WEEKDAY( A3 )
403-Jan-2013=WEEKDAY( A4 )
504-Jan-2013=WEEKDAY( A5 )
608-Jan-2013=WEEKDAY( A6 )
712-Jan-2013=WEEKDAY( A7 )
815-Jan-2013=WEEKDAY( A8 )
916-Jan-2013=WEEKDAY( A9 )
1018-Jan-2013=WEEKDAY( A10 )
1122-Jan-2013=WEEKDAY( A11 )
1223-Jan-2013=WEEKDAY( A12 )
1329-Jan-2013=WEEKDAY( A13 )
1403-Feb-2013=WEEKDAY( A14 )
1505-Feb-2013=WEEKDAY( A15 )

Results:

AB
1DateWeekday
231-Dec-20122
301-Jan-20133
403-Jan-20135
504-Jan-20136
608-Jan-20133
712-Jan-20137
815-Jan-20133
916-Jan-20134
1018-Jan-20136
1122-Jan-20133
1223-Jan-20134
1329-Jan-20133
1403-Feb-20131
1505-Feb-20133

The above data can now be used in an Excel Pivot Table, to show the number of events occurring on each weekday. A pivot table made from the sample of data above is shown on the rightabove.

## Displaying Weekdays as Text

If you use the Weekday function with the [return_type] argument set to 1, then you can use Excel formatting to display the weekday names instead of showing numbers.

To format the weekday to show the weekday name:

• Select the column containing the weekdays.
• Right click on this column with the mouse and select Format_Cells... from the right-click menu. This will bring up the 'Format Cells' dialog box.
• From within the Number tab of the dialog box, select the Category Custom.
• Under the Type heading, type ddd.
• Click OK.

Note, this only works if the [return_type] is set to 1.

For further information and examples of the Excel Weekday function, see the Microsoft Office website.

## Weekday Function Errors

If you get an error from the Excel Weekday function, this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:The supplied serial_number argument is numeric but is out of range for the current date baseorThe supplied [return_type] argument is not one of the permitted values (1-3 or 11-17). #VALUE! - Occurs if either the supplied serial_number or the supplied [return_type] is non-numeric.