Excel WEEKDAY Function

Related Function:

WEEKNUM

Basic 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
1 Sunday = 1, Monday = 2, ... , Saturday = 7
2 Monday = 1, Tuesday = 2, ... , Sunday = 7
3 Monday = 0, Tuesday = 1, ... , Sunday = 6
11 Monday = 1, Tuesday = 2, ... , Sunday = 7
12 Tuesday = 1, Wednesday = 2, ... , Monday = 7
13 Wednesday = 1, Thursday = 2, ... , Tuesday = 7
14 Thursday = 1, Friday = 2, ... , Wednesday = 7
15 Friday = 1, Saturday = 2, ... , Thursday = 7
16 Saturday = 1, Sunday = 2, ... , Friday = 7
17 Sunday = 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).

Only options 1, 2 & 3 above are available in Excel 2007 and earlier.


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

    or

Weekday Function Examples

Column A of the spreadsheet below stores the dates of a series of events. The Weekday function has been inserted into the cells of column B, to show the weekday of each date in column A.

 Formulas:

  A B
1 Date Weekday
2 31-Dec-2012 =WEEKDAY( A2 )
3 01-Jan-2013 =WEEKDAY( A3 )
4 03-Jan-2013 =WEEKDAY( A4 )
5 04-Jan-2013 =WEEKDAY( A5 )
6 08-Jan-2013 =WEEKDAY( A6 )
7 12-Jan-2013 =WEEKDAY( A7 )
8 15-Jan-2013 =WEEKDAY( A8 )
9 16-Jan-2013 =WEEKDAY( A9 )
10 18-Jan-2013 =WEEKDAY( A10 )
11 22-Jan-2013 =WEEKDAY( A11 )
12 23-Jan-2013 =WEEKDAY( A12 )
13 29-Jan-2013 =WEEKDAY( A13 )
14 03-Feb-2013 =WEEKDAY( A14 )
15 05-Feb-2013 =WEEKDAY( A15 )

 Results:

  A B
1 Date Weekday
2 31-Dec-2012 2
3 01-Jan-2013 3
4 03-Jan-2013 5
5 04-Jan-2013 6
6 08-Jan-2013 3
7 12-Jan-2013 7
8 15-Jan-2013 3
9 16-Jan-2013 4
10 18-Jan-2013 6
11 22-Jan-2013 3
12 23-Jan-2013 4
13 29-Jan-2013 3
14 03-Feb-2013 1
15 05-Feb-2013 3

The Weekday function 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 right.

The pivot table enables you to clearly see that the peak weekday for the events was Tuesday (represented by day number 3), which accounts for a total of 18 events.

Excel Weekday Pivot Table

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 this menu
  • From within the Number tab, select the Category Custom
  • Under the Type heading, type ddd
  • Click OK

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

The resulting pivot table is shown on the right.

Excel Weekday Pivot Table

Further information and examples of the Excel Weekday function can be found on 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
#VALUE! - Occurs if the supplied serial_num or the supplied [return_type] cannot be recognised as a numeric value.
#NUM! - Occurs if either:
-   the supplied serial_num argument is numeric but is out of range for the current date base
or
-   the supplied [return_type] argument is not one of the permitted values (1-3 or 11-17)
Return to the Excel Date and Time Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net