Excel WEEKDAY Function

Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Weekday-Function

Search this site:

Custom Search

Related Function:

Weeknum

Basic Description

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

The syntax of the function is :

WEEKDAY( serial_number, [return_type] )

where the function arguments are as follows:

serial_number

The date that you want to return the weekday of

An optional argument, which can be set to either 1, 2 or 3 to specify 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.

Excel Weekday Function Example

One use for the Weekday function is if want to analyse the peak weekdays for a large number of events. The spreadsheet below shows part of a spreadsheet in which the days of specific events are stored in column A (note that these dates are displayed in the UK date format, dd/mm/yyyy).

The Weekday function has been inserted in every cell of column B, to show the weekday of each date in column A.

The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

Formulas:

Example of use of the Excel Weekday Function

Results:

The data in the spreadsheet 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 the above data is shown below.

Tip

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
Ensure the Number tab is selected select the Category Custom
Under the Type heading, type ddd
Click OK

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

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 Friday.

Further information 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 Error

#NUM!

Occurs if either:

	-	the supplied serial_number is outside of the valid date range
or
	-	the supplied return_type is < 1 or > 3

#VALUE!

Occurs if either of the supplied arguments are non-numeric

Return to the Excel Date & Time Functions Page

Return to the List of All Built-In Excel Functions

Disclaimer

Privacy Policy