If supplied with a date, the Excel WEEKDAY function returns an integer representing the day of the week for the supplied date.
The format of the function is:
WEEKDAY( serial_number, [return_type] )
where the arguments are as follows:
serial_number
The initial date
return_type
This is an optional argument, which can be set to either 1, 2 or 3
to determine the integers to be assigned to each weekday. The values of 1, 2 & 3 are interpreted by the WEEKDAY function as follows:
return_type = 1
-
Sunday = 1, Monday = 2, ... , Saturday = 7
return_type = 2
-
Monday = 1, Tuesday = 2, ... , Sunday = 7
return_type = 3
-
Monday = 0, Tuesday = 1, ... , Sunday = 6
By default the return_type argument is set to 1, so if you omit this argument,
the function will return 1 for Sunday, 2 for Monday, etc.
Example
One use for the WEEKDAY function is if you have a large number of events and you want to analyse the peak weekdays.
The spreadsheet below shows part of a spreadsheet in which the days of specific events are stored in column A.
The function has then been inserted in every cell of column B, to show the weekday of each date in column A.
The function format is shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.
Formulas
Results
The WEEKDAY data can now be used in an Excel Pivot Table, to show the frequency of each weekday.
A pivot table made from the small sample of data above is shown below.
Note that the column showing the weekdays in
the above pivot table has been formatted to show the weekday names (see tip).
The pivot table enables you to clearly see that the peak weekday for the data is Tuesday.
