What Is WEEKDAY Function In Google Sheets?
The WEEKDAY function in Google Sheets fetches the number in the form of a positive integer representing the day of the week, i.e. the weekday, of selected date.
The Google Sheets WEEKDAY function, for any valid date, allocates a number to each weekday as 1 for Sunday, 2 for Monday and so on till 7 for Saturday.
For example, we will apply the WEEKDAY Google Sheets function to get the weekday on the date in cell A1.
Use this WEEKDAY Function In Google Sheets Template to follow along with the examples in this article.
Download Excel Template
Select cell B2, enter the formula =WEEKDAY(A2) and press the “Enter” key.
The output is ‘4’, as shown above. Therefore, the day of the date is “Wednesday”.
Key Takeaways
- The WEEKDAY function in Google Sheets is a Date function, that helps us find the weekday for a given day in a selected dataset for valid date values.
- Ensure that the dates fall within the range, accepted by Google Sheets and are in the right format.
- We can use the Weekday function along with some other functions, such as the VLOOKUP(), CHOOSE, etc.
- We can also use the Conditional Formatting feature to highlight required data, as we learnt in Example #2, where we highlighted only the weekends. It helps during generating reports.
- We must ensure to enter only the positive numbers as the 2nd argument value and never a negative number to avoid errors.
Syntax
The syntax of the WEEKDAY Formula in Google Sheets is,
The arguments of the WEEKDAY Formula in Google Sheets are,
- date – It is the date for which the last numeric day value from 1 to 7 must be retrieved. It is a mandatory argument.
- [type] – It is an optional argument that tells the function from which number, 1 to 7, to start the week. If no value is entered, by default, it will take 1 as the value and assign as Sunday. Any value more than 7, will repeat the days of the week in the same order. For example, if we enter 8, we will get Sunday as a return value, as after 7 days of the week then again, it’s a Sunday.
[Special Note: The table below helps us comprehend the output according to the “type” value entered.
| [type] | Number returned |
|---|---|
| 1 | From 1 (Sunday) to 7 (Saturday) |
| 2 | From 1 (Monday) to 7 (Sunday) |
| 3 | From 0 (Monday) to 7 (Sunday) |
| 11 | From 1 (Monday) to 7 (Sunday) |
| 12 | From 1 (Tuesday) to 7 (Monday) |
| 13 | From 1 (Wednesday) to 7 (Tuesday) |
| 14 | From 1 (Thursday) to 7 (Wednesday) |
| 15 | From 1 (Friday) to 7 (Thursday) |
| 16 | From 1 (Saturday) to 7 (Friday) |
| 17 | From 1 (Sunday) to 7 (Saturday) |
How To Use WEEKDAY Function In Google Sheets?
We can use the WEEKDAY Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output à select the “Insert” tab – click the “Function” option right arrow – click the “Date” option right arrow – select the “WEEKDAY” function, as shown below.
Step 2: The “WEEKDAY” formulaappears, as shown below. Enter the arguments as cell values or cell references and press “Enter”.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =WEEKDAY( in the cell, as shown below. [Alternatively, type =W or =WEEK and double-click the WEEKDAY from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references.
Step 4: Close the brackets and press “Enter”.
Examples
We will consider some WEEKDAY Function In Google Sheets examples to fetch the numeric value of the day along with some functions, such as VLOOKUP(), CHOOSE(), etc.
Example #1
Consider the dataset below that consists of some random dates. We will find the Weekday number using the function.
The steps to find the number of the day using the WEEKDAY function are,
Step 1: Select cell B2, enter the formula =WEEKDAY(A2) and press “Enter”, as shown below.
Step 2: Drag the formula from cell B2 to B6 using the fill handle, to get the following results. Column C is for our reference.
Example #2 – WEEKDAY in Conditional formatting to highlight weekands
Consider the dataset given below that consists of the weekday names, for our reference, in column A and the list of dates in an order for 12 days. We will find the Weekday number and then apply Conditional Formatting only for the weekends.
The steps to find the number of the day and apply formatting for weekends using the WEEKDAY function are as follows:
Step 1: Select cell C2. Enter the formula =WEEKDAY(A2) and press “Enter”, as shown below.
Step 2: Drag the formula from cell C2 to C13. Do it using the fill handle, to get the following results.
Step 3: Let us now apply Conditional Formatting for only the weekends.
Therefore, choose cells B2:B13 à select the “Format” tab – click the “Conditional Formatting” option. The “Conditional format rules” window appears on the right, click the “Add another rule” option, as shown below.
Step 4: In the “Conditional format rules” pane, select the “Single color” tab and under the “Format rules” section enter data as follows:
- Click the “Format cells if…” drop-down, select the “Custom formula is” option and enter the formula =OR(C2=1,C2=7) in the field that appears below.
- Select the desired color to highlight the required data, here yellow, in the “Formatting style” options.
- Finally, click the “Done” option, as shown below.
The output is shown below, where only the weekends are highlighted.
Example #3 – WEEKDAY with VLOOKUP
In the following table we have the details of a school’s uniform code to be followed each day of the week. We will retrieve the dress code for particular dates given in cells F2:F4 using the VLOOKUP and the Weekday functions.
The steps to retrieve the uniform to be worn on the dates. using the VLOOKUP are as follows:
Step 1: Select cell G2 and enter the formula =VLOOKUP(weekday(F2,1),A1:C8,3,0), as shown below.
Step 2: Press “Enter” and drag the formula from cells G2 to G4 using the fill handle, to get the following results.
Example #4 – WEEKDAY and CHOOSE
The dataset below consists of 10 dates in an order. We will find the day instead of the number of the day, using the WEEKDAY with the CHOOSE function.
The steps to fetch the names of the weekday using the Choose function are,
Step 1: Select cell B2, enter the formula =Choose(Weekday(A2,1),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”) and press “Enter”, as shown below.
Step 2: Drag the formula from cell B2 to B11 using the fill handle, to get the following results.
Important Things To Note
- If we do not provide any value for the [type] argument, then, by default, the formula takes the value as 1 and assigns it as Sunday.
- If either of the arguments are non-numeric or alpha-numeric, then we get the “#NAME!” error.
- The date argument can be a cell reference containing a date or a date returned from another function or formula. Regardless of how it is selected, we must ensure that the date is in valid format.
- Any input greater than 7 for the [type] argument, repeats the weekdays.
- If the [type] argument is a negative integer value, then, we will get the “#NUM!” error.
- If the date value selected in not in the valid format or is in the text format, then we will get the “#VALUE!” error.
Frequently Asked Questions (FAQs)
The WEEKDAY Google Sheets function might not work due to the following reasons;
One or both of the argument values are not provided.
A negative number is entered for the 2nd argument value.
The argument values entered is non-numeric.
If the dates entered as cell values, cell references or the results of the difference of two dates are not in a valid format, then the formula will not work.
We often forget in which category a function falls, here, the “WEEKDAY” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab -click the “Function” option right arrow – click the “All” option right arrow – select the “WEEKDAY” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the Google Sheets WEEKDAY by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “WEEKDAY” function, as shown below.
Use this WEEKDAY Function In Google Sheets Template to follow along with the examples in this article.
Download Excel Template