Updated May 2, 2023
WEEKDAY in Excel (Table of Contents)
WEEKDAY Function in Excel
The weekday function is an inbuilt function in excel for getting the Weekday sequence as per the selected Return Type in the syntax. As we know that a week has 7 days starting from Sunday to Saturday, considering Sunday a Week Off. Here, we just have to select the day sequence and return type, whereas Return Type consists of all the possible and known combinations of Weekday sequences used globally.
Definition
WEEKDAY Function returns a numeric or integer value from 1 to 7 that corresponds to the day of the week.
WEEKDAY Formula in Excel
The Formula for the WEEKDAY Function in Excel is as follows:
Where
serial_number: Serial number is the date value that you want to find out the weekday for or day of the week
return_type: a return type is a number from 1 to 7 that identifies the week of the week date.
It will help out which day to use as the first day of the week for calculations.
It provides a list so that you can choose how you would like the number to be returned.
e.g. Sunday = 1 to Saturday = 7, or Monday = 1 to Sunday = 7.
It is an optional parameter or argument if it is vomited; the weekday function considers as 1 by default, the first day of the week is considered Sunday.
| Return type | The number returned by excel |
| 1 – Default | 1 for Sunday to 7 for Saturday |
| 2 | 1 for Monday to 7 for Sunday |
| 3 | 0 for Monday to 6 for Sunday |
| 11 | 1 for Monday to 7 for Sunday |
| 12 | 1 for Tuesday to 7 for Monday |
| 13 | 1 for Wednesday to 7 for Tuesday |
| 14 | 1 for Thursday to 7 for Wednesday |
| 15 | 1 for Friday to 7 for Thursday |
| 16 | 1 for Saturday to 7 for Wednesday |
| 17 | 1 for Sunday to 7 for Saturday |
How to Use the WEEKDAY Function in Excel?
This WEEKDAY Function is very simple easy to use. Let us now see how to use the WEEKDAY Function in Excel with the help of some examples.
Example #1
With the help of the weekday function, I need to find out a day of the week in the cell “E8.”
Prior to applying the weekday function, if the “E8” cell is in date format, we have to convert that cell format into the general format.
Let’s apply the WEEKDAY Function in cell “E8”. Select the cell “E8” where weekday function needs to be applied, Click the insert function button (fx) under formula toolbar, a dialog box will appear, type the keyword “WEEKDAY” in the search for a function box, WEEKDAY Function will appear in select a Function box.
Double click on WEEKDAY Function, A dialog box appears where arguments for WEEKDAY Function needs to be filled or entered i.e. =WEEKDAY (serial_number, [return_type])
Serial_number is the date or reference cell or date in number format, either of them is entered to get the desired output. Here I mentioned the reference cell, i.e. “C8.”
Return_type: I entered here as 1, 1 is a default value, where it considers 1 for Sunday to 7 for Saturday
Click ok after entering both the arguments.
In the “E8” CELL, the WEEKDAY Function returns the day of the week corresponding to November 16th, 2018, i.e. 6.
Example #2
Usually, WEEKDAY Function in Excel returns the value or output as a serial number.
Suppose I want the name of that numeric value for the output of weekday function, i.e. I want the day to be displayed as Sun, Mon, Tue, Wed, Thur, Fri, and Sat instead of a serial number, then choose function is used along weekday function to get the output
In this example, the WEEKDAY function returns the output or results on the day of the week as a serial number. Then, the CHOOSE function will consider that number as index_num (the first argument), where it indicates which value from the list of mentioned value arguments to return
Here I want the output or day to be displayed as Sun, Mon, Tue, Wed, Thur, Fri and Sat
The formula to be used will be:
=CHOOSE(WEEKDAY(C13),”Sun”,”Mon”,”Tue”,”Wed”,”Thur”,”Fri”,”Sat”)
In the below-mentioned example, cell “C13” contains the date value in “mm/dd/yy” format. CHOOSE function along with WEEKDAY function is used in the cell “E13.”
I.E. =CHOOSE(WEEKDAY(C13),”Sun”,”Mon”,”Tue”,”Wed”,”Thur”,”Fri”,”Sat”)
It returns the day of the week corresponding to 11/16/18, i.e. FRI
If you need to display the full form of a day of the week, i.e. as “FRIDAY”, you can format the cell as “dddd”, OR below mentioned formula below is used in the cell “F13.”
i.e. =TEXT(C13,”dddd”)
let’s check how it works; here TEXT formula will help out to display the day of the week
TEXT(value, format_text)
Where,
The value represents the date. It is a compulsory argument.
Text Format here it is entered as “DDDD”, which is the custom date format to know the day of the week
=TEXT(C13,”dddd”) is applied in the cell “F13.”
It returns the day of the week corresponding to 11/16/18, i.e. Friday
Things to Remember
Most Common Errors in the WEEKDAY Function in Excel:
- #VALUE! error – Occurs if the given serial_number or the given [return_type] is a non-numeric value
- #NUM! error – occurs if:
The given serial_number argument is numeric, but it is out of range for the currently existing database.
The given return_type argument is not one of the permitted values (11-17 or 1-3).
- Dates should not be directly entered into the argument because it interprets text representation of the date differently. Therefore it is better to use cell reference instead of entering the date directly.
- If you leave the return_type argument blank or set it to 1, Sunday will be considered the first day of the week in the weekday function.
Recommended Articles
This has been a guide to WEEKDAY in Excel. Here we discuss the WEEKDAY Formula in Excel and how to use the WEEKDAY Function in Excel along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –
