Updated July 5, 2023
Excel Formula for Weekday
The Excel Formula for Weekday takes a date, finds out what day it is on that date (Monday, Tuesday, etc.), and assigns a number to the day (E.g., 1 for Sunday, 2 for Monday, and so on). If we enter a date using this function, it will return the assigned number.
For example, if you want to determine the day your birthday falls, like June 3, 2023, you can use the WEEKDAY function in Excel. The function will give the result as 6, considering that June 3, 2023, falls on the 6th day of the week, i.e., on Saturday.
In Excel, each day of the week has a number associated. For instance, Sunday is day number 1, Monday is day number 2, and Saturday is day number 7. We can change the starting day of the week as per our requirements. The weekday function helps find this day number for any date. It comes in handy when planning schedules, events, calculating workdays, or organizing dates for various tasks.
How to Find Day of Week?
Let’s understand how to find a day of the week using the weekday formula in Excel.
Example #1 Using the WEEKDAY function
Syntax:
- Serial Number: It indicates the date you want to determine the day of the week.
- Return Type: It is an optional argument where you can tell Excel the starting day of the week. By default, Excel considers Sunday the first day of the week.
Consider a list of dates we want to determine the numerical day of the week using the WEEKDAY function.
Solution:
Step 1: In an empty cell (cell B6), type =WEEKDAY(A6)
First, we need to input the serial number and the date; next, we need to enter a return_type. The return_type are integers representing various starting days of the week. We can choose any day as the first day of the week. Here we choose 2 indicating Monday.
Step 2: Enter the formula =WEEKDAY(A6,2)
The Excel formula for weekday returns 4, which means the given date falls on the 4th day of the week.
Step 3: Drag the formula to the last row to obtain the numerical day for the remaining dates.
The below table shows the numerical day of the week for all the given dates.
Other Ways to Find Weekday in Excel
Example #2 Using the “Format Cells” Option
Suppose you have a list of dates and want to display the day’s name. We shall use the ‘Format Cells’ option to accomplish this task. Let’s see how we can do it-
Solution:
Step 1: Copy the dates in an empty column (column B)
Step 2: Select the range B6:B13 and right-click the selection
Step 3: Choose Format Cells in the context menu.
It will open a “Format Cells” dialog box
Step 4: Choose a Date from the category
Step 5: Select” Wednesday, 14 March 2012″ from the Type drop-down list as it displays the name of the day.
Step 6: Click OK.
It converts the dates in column B to a different format displaying the day’s name with a date, as shown in the image below.
Example #3 Using IF and WEEKDAY Function Together to Find Weekend Date
We can find the next weekend’s date using the IF and WEEKDAY functions together in Excel.
Solution:
Step 1: In an empty cell (cell B6), enter the below formula
Explanation:
WEEKDAY(A6,2)=1: The Excel formula for WEEKDAY provides the weekday for the date you specify (A6), and Monday is the starting day of the week; hence, 2 is given as the second argument. Therefore, WEEKDAY(A6,2)=1 evaluates to true if the date in A6 is a Monday.
The IF function then tests the result of the WEEKDAY function for each possible day of the week (Monday through Friday) and adds a certain number of days to the input data based on the test result.
- If the date in A6 is a Monday, the formula adds 5 days to it (to get to the following Saturday, i.e., the weekend).
- If it is a Tuesday, it adds 4 days (to reach the following Saturday).
- If it is a Wednesday, it adds 3 days(to reach the following Saturday).
- If it is a Thursday, the formula adds 2 days (to reach the following Saturday).
- If it is a Friday, it adds only 1 day (to reach the following Saturday).
- If the WEEKDAY function provides any other value (i.e., the input date is on a Saturday or Sunday), the formula returns the output “It is the Weekend Date”.
Step 2: Hit the Enter key to see the weekend date, shown in the image below
Step 3: Drag down the formula to the last row to see the weekend dates for other cells:
The below image shows the output for the remaining cells.
Step 4: Drag the formula to other cells to get the results
Example #4 Using the CHOOSE and WEEKDAY functions together to find the Day
Consider the below list of dates, and we want to see the day each date falls using the CHOOSE and WEEKDAY functions in Excel.
Solution:
Step 1: In an empty cell (cell B6), enter the below formula =CHOOSE(WEEKDAY(A6,2), “Mon”, “Tue”, “Wed”, “Thur”, “Fri”, “Sat”, “Sun”)
Explanation:
WEEKDAY(A6,2): The WEEKDAY function provides the day of the week for the given date(A6) and considers Monday as the first day of the week. Hence, we give 2 as the second argument. If the date in cell A6 is a Monday, WEEKDAY(A6,2) returns 1; if it is a Tuesday, it returns 2, and so on.
The CHOOSE function then takes the result of the WEEKDAY function and provides the corresponding value from the list of day names, from Mon through Sat.
Step 2: Hit the Enter key to see the resulting day
Step 3: To get the day names for the remaining dates, drag the formula to the last row
The below table displays the day names for all the dates in the list.
Common Errors While Using Excel Formula for WEEKDAY
While working with the WEEKDAY function in Excel, a few errors might occur when we input the wrong arguments. Let us explore them:
1. #VALUE!: It occurs when we input a non-numeric value as the return_type. For example, when we write “Monday” to indicate the starting day of the week instead of 2 as the return type, the formula yields the #VALUE! Error.
2. #NUM!: It occurs when we input the return_type from the standard range (1-3) or (11-17). For instance, if we give the return_type as 4, it will give the #NUM! Error.
Things to Remember
- Syntax: When using the Excel formula for weekdays, it is important to ensure the correct syntax. The syntax requires at least one argument, i.e., the date.
- Starting day of the week: In the weekday Excel formula, you need to specify the starting day number in the range (1-3) or (11-17). Any value beyond the given range will result in an error.
- Date format: The Excel formula for WEEKDAY requires a valid date as its first argument. Ensure you provide a date in a valid format. For example, the default format for the date in the US is “mm-dd-yyyy.” However, in other countries, the date format is “dd-mm-yyyy”.
- Return_type argument: If you do not provide the return_type argument, the function considers the default value 1.
Frequently Asked Questions (FAQs)
Q1. What is the result of the weekday function?
Answer: The WEEKDAY function results in an integer representing the weekday for a specified date. An Excel formula for Weekdays provides a number between 1 and 7, where 1 means Sunday, 2 represents Monday, and so on, up to 7, which means Saturday.
Q2. Why is my weekday function not working in Excel?
Answer: The most common reason the WEEKDAY function does not work is a non-numeric serial number or return_type. For example, if the date 26 January 2023 is written in an incorrect format, such as 01-26-2023 (Correct format: 26-01-2023 ), the WEEKDAY function will give a #VALUE! Error.
Q3. Does the weekday function return the current date and time?
Answer: No, the WEEKDAY function does not return the current date and time. It provides the weekday for a date you specify.
Recommended Articles
The article above provides a helpful guide on using the Excel Formula for Weekdays. It covers finding specific days of the week and offers insights on using functions like IF and CHOOSE to determine weekend dates and day names. We recommend reading the articles below to discover useful Excel functions and formulas.
