Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# How to Use The Weekday Function in Excel

by | 4:35 pm EDT | May 12, 2023 | Excel

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:

1. Type the equal sign (=) into a cell to start a formula.

2. type “WEEKDAY” (without the quotes) followed by an open parenthesis.

3. Enter the specific date value by typing it directly or providing a cell reference.

4. Optionally enter the return type (omitting it defaults to 1).

5. 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:

1. Financial modeling through sorting and filtering data based on days of the week.

2. Designing personalized weekly schedules by calculating the days of the week for a given period.

3. Identifying patterns in sales, website traffic, or other metrics by examining performance data according to the day of the week.

4. Scheduling employee shifts based on the day of the week.

5. Project management and deadline tracking.

6. Calculating payment due dates or billing cycles based on specific days of the week, ensuring timely processing and accurate record-keeping.

7. 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:

1. Select the range of cells containing the dates you want to highlight.

2. Go to the “Home” tab in the Excel toolbar.

3. Click on “Conditional Formatting” in the “Styles” group.

4. Click on “New Rule.”

5. Select “Use a formula to determine which cells to format.”

6. 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!

## How to Calculate Percentile in Excel: 4 Great Methods

Are you looking to calculate a percentile in Excel? Well, you're in the right place. To calculate a...

## How to Find Relative Frequency in Excel: 4 Quick Methods

Calculating relative frequency is a fundamental concept in statistics, providing valuable insights into...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## How to a Take Screenshot in Excel: A Guide for Windows & Mac

As a data analyst or a spreadsheet enthusiast, you’re probably familiar with the many ways Excel can...

## How to Add Numbers in Excel: 3 Top Methods Explained

One of the fundamental operations you perform in Excel is adding numbers. Whether you’re managing...

## Excel Showing Formula Instead of Result? 5 Quick Fixes

Have you ever found yourself in a situation where instead of showing the results of your calculations,...

## How to Hard Code in Excel: 5 Easy Methods Explained

Have you ever wanted to input a value into a cell in Excel and make sure that it remains unchanged, no...

## How to Copy a Formula Down in Excel: A Quick Guide

Copying formulas down in Excel is a handy skill that can save you a lot of time and effort! Whether...

## How to Remove Time from Date in Excel: 4 Quick Ways

When you’re dealing with dates and times in Excel, you might find it necessary to separate the time...

## How to Sum a Column in Excel: 5 Ways (Shortcut Included)

One of the best things about Excel is how quickly and easily it can add up all the numbers in a column....

## How to Calculate Skewness in Excel: Formulas Explained

Do you want to calculate the skewness of your data to understand its distribution? Well, you're in the...

## How to Remove Apostrophe in Excel: 4 Quick Ways

Apostrophes have their place in Excel; however, when not needed, they can clutter your data and make it...