This guide covers everything you need to know about the Google Sheets WEEKDAY function, including its definition, syntax, use cases, and how to use it.
What is the WEEKDAY Function? How Does It Work?
The WEEKDAY function in Google Sheets gives you the day of the week as a number based on a given date. You can use different sources for the date: directly type it in, take it from another cell, or get it from another function.
What’s neat is that you can choose how you want the days numbered. Normally, it will give Sunday as 1 and Saturday as 7. But if you want, you can make Monday 1 and Sunday 7, or even Monday 0 and Sunday 6.
Remember, the function won’t say “Monday” or “Mon” but will return a number. If you want the day’s name, you’d have to use a different function called TEXT or change how the cell shows numbers.
Lastly, make sure the date you put in looks like a date. If it just looks like a regular number, the function won’t recognize it.
WEEKDAY Syntax
The syntax and arguments for the function are as follows:
WEEKDAY(date, [type])
Where:
- “date” is the date for which you want to determine the day of the week. This argument must be a reference to a cell containing a date, a function returning a date type, or a number.
- “[type]” is an optional argument. It’s a number indicating which numbering system to use to represent weekdays. By default, it counts starting with Sunday = 1.
Here are the specifics for the [type] argument:
- If [type] is 1 (or omitted), days are counted from Sunday, and the value of Sunday is 1, so the value of Saturday is 7.
- If [type] is 2, days are counted from Monday, and the value of Monday is 1, so the value of Sunday is 7.
- If [type] is 3, days are counted from Monday, and the value of Monday is 0, so the value of Sunday is 6.
Some important notes on using the WEEKDAY function:
- The “date” input to the function must either reference a cell containing a date, a function that returns a date object such as DATE, DATEVALUE, or TO_DATE, or a date serial number of the type returned by the N function. Google Sheets represents dates internally as numbers for ease of use in calculation, but this function does not perform the conversion from a string to a date.
- WEEKDAY does not auto-convert number formats in the same way that Google Sheets does upon direct entry into cells. For example, WEEKDAY(10/10/2000) is interpreted as WEEKDAY(0.0005) because it’s seen as the quotient of 10 divided by 10 divided by 2000.
- The WEEKDAY function returns the day of the week in numeric form, not as a letter (like ‘M’ or ‘F’), an abbreviation (like ‘Tue’ or ‘Thu’), or a full day name (like ‘Wednesday’). If you want the name of the weekday, you’ll need to use the TEXT function or change the cell formatting.
Examples of How to Use the WEEKDAY Function
Here are some practical examples of how to use the WEEKDAY function in Google Sheets.
Example #1: Basic Use of WEEKDAY Function
To begin with, let’s look at a simple use of the WEEKDAY function. Suppose you have a date in cell A1 (e.g., “2022-01-01”) and want to know which day of the week it falls on. You would use the WEEKDAY function as follows:
=WEEKDAY(A1)
This formula will return a number from 1 to 7, representing the day of the week starting from Sunday (1) to Saturday (7). So, if “2022-01-01” is a Saturday, the formula will return 7.
Example #2: WEEKDAY Function with Different Type
The WEEKDAY function allows you to change the starting day of the week using the optional ‘type’ argument. For instance, if you want the week to start on Monday (1) and end on Sunday (7), you can use the WEEKDAY function as follows:
=WEEKDAY(A1, 2)
So, if “2022-01-01” is a Saturday, this formula will return 6.
Example #3: Using WEEKDAY Function to Highlight Weekends
You can also use the WEEKDAY function in conditional formatting rules to highlight weekends in a list of dates. For example, if you have dates in column A and want to highlight the cells that fall on a weekend, you could create a conditional formatting rule with this custom formula:
=WEEKDAY(A1, 2)>5
This rule uses the WEEKDAY function to check if the day number of the date in each cell is greater than 5 (i.e., Saturday or Sunday when using type 2) and applies the formatting to those cells.
Example #4: Using WEEKDAY Function in Conjunction with Other Functions
The WEEKDAY function can be combined with other functions to perform more complex operations. For example, you can use the WEEKDAY and COUNTIF functions to count how many Mondays are in a list of dates. If your dates are in cells A1 to A10, you could use this formula:
=COUNTIF(ARRAYFORMULA(WEEKDAY(A1:A10, 2)), 1)
This formula uses the WEEKDAY function to convert each date into a day number (with Monday as 1), and then the COUNTIF function counts how many of those day numbers are 1 (i.e., Monday).
Why Is WEEKDAY Not Working? Troubleshooting Common Errors
If you are experiencing problems with the WEEKDAY function in Google Sheets, it might be due to a few common errors. This section will help you troubleshoot these issues by identifying the causes and providing solutions.
#VALUE! Error
Cause: This error usually occurs when the input value is not a valid date or if the date is not formatted correctly.
Solution: Make sure that the date you are entering into the WEEKDAY function is valid and formatted correctly. Google Sheets requires dates to be entered in the format YYYY-MM-DD. For example, if you want to enter the date January 1, 2021, you would enter it as 2021-01-01.
#NUM! Error
Cause: The #NUM! error occurs when the type of argument entered in the formula does not match the expected type. In the case of the WEEKDAY function, this error will appear if the second argument is not between 1 and 3.
Solution: Make sure that the second argument in the WEEKDAY function is a number between 1 and 3. Remember that this argument determines the type of return value, and it is optional. If you don’t provide a second argument, Google Sheets will assume it to be 1 by default.
#REF! Error
Cause: The #REF! error occurs when the cell reference in the formula is not valid. This can happen because the cell being referenced does not exist or the formula is trying to reference a cell outside of the spreadsheet.
Solution: Make sure that the cell you are referencing in the formula exists and is within the boundaries of the spreadsheet. If you are referencing a cell in another sheet, ensure that the sheet name is correct and that the cell you are referencing exists in that sheet.
#N/A Error
Cause: The #N/A error typically occurs when the formula cannot find the value it is looking for. In the context of the WEEKDAY function, this error can occur if the date being referenced does not exist in the data set.
Solution: Ensure that the date you reference in the WEEKDAY function exists in your data set. If you are referencing a cell that contains a date, make sure that the cell contains a valid date.
Circular Reference Error
Cause: This error occurs when a formula refers back to its own cell either directly or indirectly. This creates an infinite loop that Google Sheets cannot resolve.
Solution: Check your formula to ensure it does not refer back to its own cell. If you’re using the WEEKDAY function in a formula, make sure that the cell you’re outputting the result to is not being referenced within the formula itself.
Using WEEKDAY With Other Google Sheets Functions
Combining the WEEKDAY function with other Google Sheets functions can yield powerful results. This allows you to perform more complex operations on your data. Here, we’ll look at how to combine it with the IF, INDEX, and MATCH functions.
With IF
Usage: The IF function returns one value if a condition is true and another if it’s false. When combined with the WEEKDAY function, it can be used to categorize days of the week as weekdays or weekends.
Example: Suppose you have a column of dates in column A, starting from A2. If you want to categorize each date as a “Weekday” or “Weekend”, you can use the following formula:
=IF(WEEKDAY(A2,2)<6,”Weekday”,”Weekend”)
This formula uses the WEEKDAY function to get the day of the week for the date in cell A2. If the day is less than 6 (Monday to Friday), it returns “Weekday”. Otherwise, it returns “Weekend”.
With INDEX and MATCH
Usage: The INDEX and MATCH functions are often used together to perform lookups. The WEEKDAY function can be used in combination with these functions to find data based on the day of the week.
Example: Suppose you have a table of data in range A2:B7, with dates in column A and corresponding values in column B. If you want to find the value for the first Friday in the data, you can use the following formula:
=INDEX(B2:B7,MATCH(6,WEEKDAY(A2:A7,2),0))
This formula uses the WEEKDAY function to get the day of the week for each date in the range A2:A7. The MATCH function then finds the position of the first 6 (Friday) in this array. The INDEX function uses this position to return the corresponding value from the range B2:B7.
For more details on the WEEKDAY function, check out the official documentation at the Google Docs Editors Help Center.
