Excel WEEKDAY Function – How to Use

The WEEKDAY function is a DATE and TIME function that can be used to find the weekday for a given date. The date is supplied to the function as an argument, and consequently, the function returns an integer between 1 – 7, each number representing a day of the week. By default, 1 represents Sunday and 7 represents Saturday, but these values are configurable.

The WEEKDAY function has several uses cases for financial analysts. For instance, think about a capital work-in-process account that requires the addition of labor costs. The financial analyst may be able to estimate this cost simply by computing the anticipated duration for completing the project, removing weekends, and calculating the per-day labor cost for the remainder of the days.

Excel WEEKDAY Function

Syntax

The syntax of the WEEKDAY function is as follows:

=WEEKDAY(serial_number, [return_type])

Arguments:

serial_number – This is a required argument that may be supplied with either the serial number or a cell reference to the date for which we want to find the day.

return_type – This is an optional argument and is set to 1 by default, i.e., the first day is set to Sunday. Alternatively, we may use the table illustrated below for specifying which day of the week we want to consider as day 1.

Possible 'return_type' values

'return_type' valueDay Represented by 'return_type'Numerical ResultNumerical Result Day Mapping
none (default)Sunday1-7Sunday-Saturday
1Sunday1-7Sunday-Saturday
2Monday1-7Monday-Sunday
3Tuesday0-6 (since Monday = 0 in this case)Monday-Sunday
11Monday1-7Monday-Sunday
12Tuesday1-7Tuesday-Monday
13Wednesday1-7Wednesday-Tuesday
14Thursday1-7Thursday-Wednesday
15Friday1-7Friday-Thursday
16Saturday1-7Saturday-Friday
17Sunday1-7Sunday-Saturday

Important characteristics of the WEEKDAY function

  • The WEEKDAY function returns 1 for Sunday (and 7 for Saturday) by default when the return_type argument is left empty.
  • If the serial_number argument is empty, the WEEKDAY function still returns '7.'
  • If the value supplied in the serial_number argument is non-numeric, the function returns a #VALUE error.
  • If the serial_number argument falls outside of Excel's database or the return_type argument is unrecognized (i.e., isn't empty, 1–3, or 11–17), then the function returns a #NUM error.

Examples of WEEKDAY Function

Let’s try to see some examples of the WEEKDAY function.

Example 1 – Plain Vanilla Formula for the WEEKDAY Function

Let's start with the basic WEEKDAY formula. All we want to do here is have the WEEKDAY function tell us which day it is for a set of dates we supply.

We'll use the following formula.

=WEEKDAY(A2)

Excel-WeekDay-Function-Example-01

So, we have our output as 3, 3, 5, and 1 which we know means Tue, Tue, Thu, and Sun. We're just warming up here. Let's tweak this formula a little.

Example 2 – Configuring the WEEKDAY Function to Start from Thursday

As mentioned while discussing the syntax, we can configure the WEEKDAY function to set any day as day 1. We'll need to refer to the table showing the return_type values, and check which value we need to use to set a day of our choice as the first day. In our case, we want Thursday as our day 1. Therefore, we'll use 14 in the return_type argument, like so:

=WEEKDAY(A2, 14)

Excel-WeekDay-Function-Example-02

Since Thu is Sunday + 3 days, notice that all output values are greater by 3 than the output values of our previous example. Also, note that March 31, 2002, is actually a Sunday. Since we have now set Thursday as day 1, the output is 4 (i.e., 4 days from Thursday), which is Sunday.

Child's play, isn't it?

Example 3 – Display the Name of Days Instead of Integer Values

Excel sheets are already numerically intense. Any opportunity we have to use text is a Godsend. As you see in the previous examples, the WEEKDAY function always gives us a number between 1–7 and the onus is on us to check which day the number represents. This is especially true for example 2 where we set Thursday as our day 1.

However, we can remedy this using the CHOOSE function. This is the formula we will use:

=CHOOSE(WEEKDAY(A2, 14), "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed")

Excel-WeekDay-Function-Example-03

It's pretty obvious what the formula does, correct? If it's not clear, let me break it down. The CHOOSE function uses the value relayed by the WEEKDAY function as an index and outputs the value in (n+1)th comment. For instance, if the WEEKDAY function relays 2, the CHOOSE function returns the 3rd (2+1) comment which is "Fri".

As you can see, our output checks out with our previous example. We're only just getting started with the cool stuff we can do with the WEEKDAY function. Alright, on to the next one.

WEEKDAY Function with Conditional Formatting

We all know the happiness of delivering a successful project. And for a project to be really successful, it must be delivered within the deadlines. So project managers are under the constant stress of meeting the project deadlines.

One such manager has been making attempts to see how many days during the project timeline are weekends. Since those days are off for the employees anyway, they can be overlooked while coming up with an action plan for tackling deadlines.

Let's help him with this, shall we?

Here's the formula we will use to help the manager find out if a day is a Weekend or a Working Day.

=IF((WEEKDAY(A2, 2) < 6),"Working Day","Weekend")

You'll see all the dates are now categorized either as a working day or weekend.

Excel-WeekDay-Function-Example-04a

In this formula, the WEEKDAY function checks the date in column A with return_type as 2. This means that instead of Sunday first day is set as Monday. Based on this logic, Saturday becomes 6 and Sunday becomes 7. Outside we have an IF function that checks if the result of the WEEKDAY function is less than 6, then display "Working Day", otherwise display "Weekend".

The formula does the job, but there's a way to improve this output aesthetically. Let's play around with some conditional formatting to achieve a more polished output.

To do this, select the output range and go to Home > Styles > Conditional Formatting > New rule. This should open a dialog box. Select "Use a formula to determine which cells to format" as the rule type.

Excel-WeekDay-Function-Example-04b

At the bottom of the dialog box, insert the following formula:

=WEEKDAY($A2, 2) > 5

Next, click on the Format button and head over to the Fill tab. Choose the color "green" (or any other color of your choice) and click OK. Click OK again to close the conditional formatting dialog box.

Excel-WeekDay-Function-Example-04c

When you're done, you should see all the Weekends highlighted in green.

Use WEEKDAY Function for Calculating Wages at Different Rates

Based on the above scenario, the manager comes to the conclusion that he does not have enough work days to deliver the project on time. To tackle this, he needs to request any one of the employees to work on weekends as well till the first phase of delivery.

It's only fair to offer the employee a higher wage for the weekend, wouldn't you agree? There's just one hiccup – how do you calculate the total wages for the employee far more conveniently rather than doing all the work manually?

We can have Excel make these computations for us using the IF function and the WEEKDAY function, like so:

=IF(WEEKDAY(A3, 2) > 5, C3*$G$8,C3*$G$7)

Excel-WeekDay-Function-Example-05

Let's put this formula in words. Essentially, the formula checks what the output value is for the WEEKDAY function. If it's equal to 6 or 7 (i.e., it is Saturday or Sunday), it multiplies the number of hours worked (from cell C3) with the premium rate mentioned in cell G7. If the value is equal to 1, 2, 3, 4, or 5 (i.e., it is a working day), the value in C3 is multiplied by the usual rate in cell G8.

Once we have the day-wise wages calculated for the employee, we can use the SUM function to calculate the total wages payable for the month, like so:

=SUM(D3:D17)

So, the total wages payable to the employee is $ 3,900.00 in our example.

That wraps up the WEEKDAY function. It allows you to do plenty of cool things, can often come in handy with its unique use-cases, and saves you a ton of time. Keep practicing this highly utilitarian function, and when you're done, we'll have the next function loaded for you ready to fire away!

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.