What is Excel WEEKDAY Function?
The Excel WEEKDAY function is one of Excel DATE and TIME function. This function identifies the day of the week of a specified date and returns a number between 1 and 7 that represents the day of the week of the specified date.
Excel WEEKDAY function is useful in business and financial analysis, as well as planning and scheduling project tasks.
How Excel WEEKDAY Function Works?
Excel WEEKDAY function finds out which day of the week a particular date is and returns a number between 1 and 7 that represents the corresponding day of the week for the specified date.
The syntax for Excel WEEKDAY function is:
And the Input Arguments are as follows:
- serial_number – This is the serial number of the date you want to find the weekday number.
- [return_type] – This is optional. You choose how the numbers are assigned to each weekdays. The default setting is set to Sunday as the start of the week which means that, if the date specified is a Sunday, it will return 1, if Monday, it returns 2, etc.
Furthermore, Excel WEEKDAY function takes several number codes controlled by the [return_type] argument. The table below shows the number codes available for the [return_type] and the weekday numbering for each number code.
When Excel WEEKDAY Function is Used
Excel WEEKDAY function is used when you want to get what day of the week a particular date is. The days are then displayed as numbers (between 1 to 7) for the days of the week.
For example, if you wish to find out what day of the week a particular project will be completed given the end date of the project.
- Only options 1, 2 & 3 are available in Excel 2007 and earlier versions.
- The default value for the [return_type] argument is set to 1 (Sunday = 1, Monday =2,…….., Saturday = 7). So even if this optional argument is not specified in the formula, WEEKDAY function will return the default value.
- The serial_number argument for Excel WEEKDAY function can also be entered as:
- a cell reference containing the date
- a date stored as text
- date entered as text string (in double quotes)
- a date result returned from another formula or function.
- Also, when using this function, it is advised that you do not type the dates directly into the function as Excel may interpret text representation of dates differently according to your computer’s date settings.
How to Use Excel WEEKDAY Function
In the above table, you can see four different formulas used with the WEEKDAY function to get the days of the week.
If you enter the formula =WEEKDAY(A15) without specifying the [return_type] in the formula, WEEKDAY function returns the values using the default setting which counts weekdays starting from Sunday.
Similarly, you will get the same result if you specify the [return_type] using the number code 1 which is: =WEEKDAY(A15,1) which also starts counting weekday from Sunday.
However, to start counting weekday from Monday, you set the [result_type] to 2 as shown:
Consequently, you can see the result differs from the two formulas used initially.
Display Weekdays as Text
When Excel WEEKDAY function displays weekdays in numbers, you can change the weekday numbers to be displayed as “Sun, Mon, Tues,….Sat” instead, or you can add the CHOOSE function to WEEKDAY to display the weekday names.
So, you will need to format the cells to show the weekday names instead of the numbers.
Now, to format the cells so that WEEKDAY function will display the names,
- Select the cells containing the number codes of the weekdays
- Press Ctrl + 1 or right-click on the selection and click on Format Cells
- In the Format Cells dialog box, click on Number tab and select the Custom category.
- In the Type field, type ddd
- Click Ok
As you can see, all the weekdays are displayed in their names and no more in numbers as shown below:
Common Errors in Excel WEEKDAY Function
If you get an error when using Excel WEEKDAY function, it is likely as a result of the following:
- #NUM! – This occurs if the specified argument for the serial_number is numeric but it’s out or range of the current date in Excel; or if the specified [return_type] argument is not one of the optional values (1-3 or 11-17) provided in Excel.
- #VALUE! – This is the error you get if the specified serial_number or [return_type] is non-numeric.
Other Related Excel Functions
Thank you for learning Excel WEEKDAY function. You can upgrade your skills level to master Excel by learning other Excel functions.
To learn more, check out these other functions in Excel:
- EDATE Function