When you’re working with dates in a spreadsheet, you’ll often want to determine the day of the week from a given date. Microsoft Excel provides built-in Excel functions for this.
The WEEKDAY function in Excel accepts a date and returns a number from 1 to 7, representing the day of the week. By default, 1 represents Sunday and 7 represents Saturday, but you can customize this to match your needs.
This article takes a closer look at how to use the WEEKDAY function in Excel.
Let’s get started!
What is the Weekday Function?
The WEEKDAY function in Excel is a built-in date and time function that returns the day of the week as a number for a specified date. It’s part of the Date and Time functions in Excel.
The syntax is as follows:
=WEEKDAY(serial_number, [return_type])
The serial number is a sequential number representing the given date for which you want to find the day of the week. The date can be entered directly into the formula or referenced from a cell.
The second argument Excel recognizes, the return_type, is optional. The default value is 1, which returns a numeric value between 1 and 7, and the week starts on a Sunday.
However, you can change the numbering system to start with any other day of the week.
The return_type optional argument can take the following values:
1 or omitted: Returns a numeric value between 1 and 7, where the week begins
on Sunday and ends on Saturday.
2: Returns a numeric value between 1 and 7, where 1 represents Monday and 7 represents Sunday.
3: the return value is between 0 and 6, where 0 represents Monday and 6 represents Sunday.
Other return types (11, 12, 13, 14, 15, 16, and 17) are available (but not in earlier versions of Excel). They follow the same pattern as return types 1, 2, and 3, but with a different starting day for the week.
For example, return type 11 starts the week on Monday (1) and ends on Sunday (7), whereas return type 12 starts the week on Tuesday (1) and ends on Monday (7).
How to Use the Weekday Function in Excel
To use the function, follow these steps:
Type the equal sign (=) into a cell to start a formula.
type “WEEKDAY” (without the quotes) followed by an open parenthesis.
Enter the specific date value by typing it directly or providing a cell reference.
Optionally enter the return type (omitting it defaults to 1).
Finally, close the parenthesis and press Enter.
Here’s an example of what the formula might look like:
=WEEKDAY(“05/02/2023”, 1)
This formula would return the number 2 as the day of the week, which corresponds to Monday (since May 2, 2023 is a Monday).
Alternatively, you can provide a cell reference as in this picture:
Using the WEEKDAY function is straightforward if you understand the syntax and its parameters. However, it’s possible to run into errors.
In the next section, we look at two common errors and how you can deal with them.
2 Common Errors and How to Fix Them
There are two common errors that you may encounter when using the WEEKDAY function in Excel:
#NUM! error
#NAME! Error
Let’s look at each in turn.
1. #NUM! Error
This error occurs when the serial_number argument is not a valid date.
To fix this error, make sure that the date is entered correctly. Alternatively, you can use the DATE function to create a valid date.
It also occurs when you enter a return type that is an invalid number. For example, this picture shows the results of using 4 as the return type. To fix this, use a valid number.
2. #NAME! Error
This error occurs when the return_type argument is not a numeric value.
For example, inexperienced Excel users may enter “one” instead of the numerical 1. To fix this, make sure that the return_type is numeric.
Now that you know how to use the WEEKDAY function and how to handle common errors, let’s take a look at some scenarios where this Excel function is quite useful!
7 Scenarios When the Weekday Function Useful
The WEEKDAY function in Excel is a versatile tool that can be applied in various scenarios to improve data analysis, organization, and reporting.
Some use cases for the WEEKDAY function include:
Financial modeling through sorting and filtering data based on days of the week.
Designing personalized weekly schedules by calculating the days of the week for a given period.
Identifying patterns in sales, website traffic, or other metrics by examining performance data according to the day of the week.
Scheduling employee shifts based on the day of the week.
Project management and deadline tracking.
Calculating payment due dates or billing cycles based on specific days of the week, ensuring timely processing and accurate record-keeping.
Planning events or meetings by determining suitable days of the week.
These use cases represent just a few examples of how the WEEKDAY function can be employed to improve efficiency, organization, and decision-making in a variety of contexts.
Next, we cover how you can use WEEKDAY with other functions in the Excel toolbox!
How to Use Weekday with Other Date and Time Functions
The WEEKDAY function in Excel is just one of the many date and time functions that Excel has to offer.
Other functions include:
TODAY
NOW
DATE
TIME
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
These functions can be used in combination with WEEKDAY to perform more complex calculations.
For example, you can always get the day number of today’s date by combining WEEKDAY with NOW (or the TODAY function) like this:
=WEEKDAY(NOW())
Alternatively, place the NOW() function in a cell and use the WEEKDAY() function with the cell reference. This picture shows this usage:
You can combine these time functions with Power Query and Power BI for even more complex analysis. For example, you can group a pivot table by the day of the week. Here are some tutorials to get started:
This video will give you an overview of these tools:
In the next sections, we’ll look at how you can use WEEKDAY in different scenarios, including how to know if a day is a weekday or weekend, how to obtain the day of the week, how to work with sequential serial numbers, and more!
How to Determine if a Day is a Weekday or Weekend
You can use the WEEKDAY function to determine whether a day is a weekday or a weekend.
The following formula uses the IF function to return “Weekend” if the date in cell A1 is a Saturday or Sunday, and “Weekday” otherwise:
=IF(WEEKDAY(A1,2)>5,”Weekend”,”Weekday”)
Other Formulas and Functions
You can also investigate the WORKDAY function for similar working day calculations. The NETWORKDAYS function returns the number of workdays between two dates.
WEEKNUM is a related function where Excel returns the week number within a year.
How to Obtain the Day of the Week
The WEEKDAY function itself does not have a built-in option to return the day of the week as text.
However, you can easily obtain the day of the week as text by combining the WEEKDAY formula with the CHOOSE function or by using the TEXT function.
This is the syntax with CHOOSE:
=CHOOSE(WEEKDAY(serial_number, [return_type]), “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)
Replace “serial_number” with the date you want to find the day of the week for.
Replace “[return_type]” with the desired return type (1, 2, or 3).
The CHOOSE function will match the number returned by the WEEKDAY function with the corresponding day name in the list.
For example, the following formula will return the full name of the day of the week for the date in cell A1:
=CHOOSE(WEEKDAY(A1),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)
How to Work With Sequential Serial Numbers
The WEEKDAY function returns a number between 1 and 7, where 1 represents Sunday and 7 represents Saturday.
However, sometimes you may need to work with sequential serial numbers instead.
To do this, you can use the DATE function to create a date that Excel recognizes and then subtract the start date from the end date to get the number of days between them.
You can then use the WEEKDAY function to get the day of the week for each of those days.
How to Use Conditional Formatting to Highlight Specific Days
Conditional formatting is a powerful feature in Excel that allows you to format cells based on certain conditions.
For example, you can use conditional formatting to highlight weekdays or weekends in a date range. To do this, follow these steps:
Select the range of cells containing the dates you want to highlight.
Go to the “Home” tab in the Excel toolbar.
Click on “Conditional Formatting” in the “Styles” group.
Click on “New Rule.”
Select “Use a formula to determine which cells to format.”
In the “Format values where this formula is true” input box, enter the following formula (replace A1 with the cell reference of the top-left cell in your range):
=OR(WEEKDAY(A1, 1)=1, WEEKDAY(A1, 1)=7)
The next steps are to set the style you want to apply to the matching cells. For example, you may choose to have weekends formatted with a red background.
This picture shows the rule in action on the selected range:
How to Set a Custom Date Format in the Excel Weekday Function
You can customize the date format used by the WEEKDAY function.
For example, if you want the function to interpret dates in the format “dd/mm/yyyy”, you can use the following formula:
=WEEKDAY(TEXT(A1,”dd/mm/yyyy”))
This will return the day of the week (1 to 7) corresponding to the date in cell A1, using the custom date format.
How to Count Working Days and Weekends
If you want to count the number of working days or weekends in a range of dates, you can use the WEEKDAY function in combination with the SUM function.
For example, to count the number of weekends in a range A1:A7, you can use the following formula:
=SUM(–(WEEKDAY(A1:A7,2)>5))
This will return the number of weekends (i.e. days with a weekday number greater than 5) in the range.
To count the number of working days, you can use a similar formula:
=SUM(–(WEEKDAY(A1:A7,2)<6))
This will return the number of working days (i.e. days with a weekday number less than 6) in the range.
Final Thoughts
The WEEKDAY function in Excel is a powerful and versatile tool that enables users to analyze, organize, and manipulate data based on the days of the week.
The straightforward syntax and customizable return_type options offer flexibility and adaptability to a variety of use cases.
This article has shown you many ways to use the WEEKDAY function combined with other Excel functions and features for complex data analysis and reporting. Level up your Excel skills with this essential time function!
