WEEKDAY in Excel (Table of Contents)
WEEKDAY Function in Excel
- WEEKDAY Function: It’s an in-built function in excel that is categorized under DATE & TIME function. It is used as worksheet & VBA function in excel.
- It is used as a part of a formula in a cell of a worksheet & also integrated with other complex excel time & date functions and other excel formulas.
- Excel considers Sunday as the first day of the week by default, not Monday.
- WEEKDAY helps out to find the day of the week for a given date
- Excel is very easy to use and simple function
- Its most commonly used by a financial & statistical analyst to calculate project completion time (excluding weekends) & helpful in scheduling & planning the work for company projects.
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:
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 day of the week of the 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, weekday function considers as 1 by default, the first day of the week is considered Sunday.
|Return type||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|
Note: From Excel 2010 onwards, 11 to 17 return type values were introduced
How to Use 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.
WEEKDAY in Excel Example #1 -WEEKDAY Function
With the help of weekday function, I need to find out a day of the week in the cell “E8”
Prior to applying weekday function, if the “E8” cell is in date format, we have to convert that cell format into the general format.
Let’s apply 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, WEEKDAY Function returns the day of the week corresponding to November 16th, 2018 i.e. 6.
WEEKDAY in Excel Example #2 – T0 Find out WEEKDAY Name (CHOOSE + WEEKDAY FUNCTION)
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 in 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:
In the below-mentioned example, cell “C13” contains date value in “mm/dd/yy” format. CHOOSE function along with WEEKDAY function is used in the cell “E13”
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 is used in the cell “F13”
let’s check how it works, here TEXT formula will help out to display the day of the week
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 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.
- In the weekday function, if the return_type argument is mentioned 1 or its vomited or the value not entered, then by default, the first day of the week is considered Sunday.
You can download this WEEKDAY Function Excel template here –WEEKDAY Function Excel Template
This has been a guide to WEEKDAY in Excel. Here we discuss the WEEKDAY Formula in Excel and how to use WEEKDAY Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –