WEEKDAY Function in Microsoft Excel
Part 1: Introduction
Definition
The WEEKDAY function in Microsoft Excel is a date/time function that returns the day of the week corresponding to a date. By default, the day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday).
Purpose
The purpose of the WEEKDAY function is to provide the day of the week for a specific date. This can be useful for scheduling, planning, and data analysis tasks based on the day of the week.
Syntax & Arguments
The syntax for the WEEKDAY function is as follows:
WEEKDAY(serial_number,[return_type]) The WEEKDAY function syntax has the following arguments:
Serial_number: Required. A sequential number that represents the date of the day you are trying to find. Dates should be entered using the DATE function or as a result of other formulas or functions.
Return_type: Optional. A number that determines the type of return value. It can be a number from 1 to 17, each representing a different numbering system for the days of the week.
Return Value
The WEEKDAY function returns an integer from 1 to 7, representing the day of the week. The specific value returned depends on the return_type argument.
Remarks
Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900. If serial_number is out of range for the current date base value, a #NUM! Error is returned if return_type is out of the range specified in the table above, a #NUM! Error is returned.
Part 2: Examples
Let’s look at examples of using the WEEKDAY function in business.
Example 1
Purpose: To find the day of the week for a specific date.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date |
| 2 | 2023-07-29 |
| 3 | =WEEKDAY(A2) |
Explanation: In this example, we want to find the day of the week for the date in cell A2. The formula =WEEKDAY(A2) returns the day of the week for the date in cell A2.
Example 2
Purpose: To find the day of the week for a specific date, with Monday as 1 and Sunday as 7.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date |
| 2 | 2023-07-29 |
| 3 | =WEEKDAY(A2, 2) |
Explanation: In this example, we want to find the day of the week for the date in cell A2, with Monday as 1 and Sunday as 7. The formula =WEEKDAY(A2, 2) returns the day of the week for the date in cell A2.
Example 3
Purpose: To find the day of the week for a specific date, with Monday as 0 and Sunday as 6.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date |
| 2 | 2023-07-29 |
| 3 | =WEEKDAY(A2, 3) |
Explanation: In this example, we want to find the day of the week for the date in cell A2, with Monday as 0 and Sunday as 6. The formula =WEEKDAY(A2, 3) returns the day of the week for the date in cell A2.
Example 4
Purpose: To find the day of the week for a specific date, with Tuesday as 1 and Monday as 7.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date |
| 2 | 2023-07-29 |
| 3 | =WEEKDAY(A2, 12) |
Explanation: In this example, we want to find the day of the week for the date in cell A2, with Tuesday as 1 and Monday as 7. The formula =WEEKDAY(A2, 12) returns the day of the week for the date in cell A2.
Example 5
Purpose: To find the day of the week for a specific date, with Wednesday as 1 and Tuesday as 7.
Data tables and formulas:
| A | |
|---|---|
| 1 | Date |
| 2 | 2023-07-29 |
| 3 | =WEEKDAY(A2, 13) |
Explanation: In this example, we want to find the day of the week for the date in cell A2, with Wednesday as 1 and Tuesday as 7. The formula =WEEKDAY(A2, 13) returns the day of the week for the date in cell A2.
Example 6: Using WEEKDAY with IF Function
Purpose: To classify whether a given date is a weekday or a weekend.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Date | Day Type |
| 2 | 2023-07-29 | =IF(WEEKDAY(A2,2)<6, “Weekday”, “Weekend”) |
Explanation: In this example, we want to classify whether the date in cell A2 is a weekday or a weekend. The formula =IF(WEEKDAY(A2,2)<6, "Weekday", "Weekend") returns “Weekday” if the date is a weekday and “Weekend” if the date is a weekend.
Example 7: Using WEEKDAY with VLOOKUP Function
Purpose: To find the name of the day for a specific date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Date | Day Name |
| 2 | 2023-07-29 | =VLOOKUP(WEEKDAY(A2,2), $E$2:$F$8, 2, FALSE) |
And the lookup table:
| E | F | |
|---|---|---|
| 1 | Number | Day |
| 2 | 1 | Monday |
| 3 | 2 | Tuesday |
| 4 | 3 | Wednesday |
| 5 | 4 | Thursday |
| 6 | 5 | Friday |
| 7 | 6 | Saturday |
| 8 | 7 | Sunday |
Explanation: In this example, we want to find the name of the day for the date in cell A2. The formula =VLOOKUP(WEEKDAY(A2,2), $E$2:$F$8, 2, FALSE) returns the name of the day for the date in cell A2.
Example 8: Using WEEKDAY with SUM Function
Purpose: To sum values corresponding to a specific day of the week.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Value | Sum of Mondays |
| 2 | 2023-07-29 | 10 | =SUMIF(A2:A4, WEEKDAY(A2:A4,2)=1, B2:B4) |
| 3 | 2023-07-30 | 20 | |
| 4 | 2023-07-31 | 30 |
Explanation: In this example, we want to sum column B values corresponding to Monday. The formula =SUMIF(A2:A4, WEEKDAY(A2:A4,2)=1, B2:B4) returns the sum of the values that correspond to Monday.
Example 9: Using WEEKDAY with AVERAGE Function
Purpose: To calculate the average values corresponding to a specific day of the week.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Value | Average of Mondays |
| 2 | 2023-07-29 | 10 | =AVERAGEIF(A2:A4, WEEKDAY(A2:A4,2)=1, B2:B4) |
| 3 | 2023-07-30 | 20 | |
| 4 | 2023-07-31 | 30 |
Explanation: In this example, we want to calculate the average values in column B that correspond to Monday. The formula =AVERAGEIF(A2:A4, WEEKDAY(A2:A4,2)=1, B2:B4) returns the standard of the values that correspond to Monday.
Example 10: Using WEEKDAY with COUNT Function
Purpose: To count the number of specific days of the week on various dates.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Date | Count of Mondays |
| 2 | 2023-07-29 | =COUNTIF(A2:A4, WEEKDAY(A2:A4,2)=1) |
| 3 | 2023-07-30 | |
| 4 | 2023-07-31 |
Explanation: In this example, we want to count the number of Mondays in the range A2:A4. The formula =COUNTIF(A2:A4, WEEKDAY(A2:A4,2)=1) returns the count of Mondays in the range A2:A4.
Example 11: Using WEEKDAY with MAX Function
Purpose: To find the maximum value corresponding to a specific day of the week.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Value | Max of Mondays |
| 2 | 2023-07-29 | 10 | =MAXIFS(B2:B4, A2:A4, WEEKDAY(A2:A4,2)=1) |
| 3 | 2023-07-30 | 20 | |
| 4 | 2023-07-31 | 30 |
Explanation: In this example, we want to find the maximum value in column B that corresponds to Monday. The formula =MAXIFS(B2:B4, A2:A4, WEEKDAY(A2:A4,2)=1) returns the maximum weight that fits Monday.
Example 12: Using WEEKDAY with MIN Function
Purpose: To find the minimum value corresponding to a specific day of the week.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Value | Min of Mondays |
| 2 | 2023-07-29 | 10 | =MINIFS(B2:B4, A2:A4, WEEKDAY(A2:A4,2)=1) |
| 3 | 2023-07-30 | 20 | |
| 4 | 2023-07-31 | 30 |
Explanation: In this example, we want to find the minimum value in column B that corresponds to Monday. The formula =MINIFS(B2:B4, A2:A4, WEEKDAY(A2:A4,2)=1) returns the minimum value that corresponds to Monday.
Example 13: Using WEEKDAY with INDEX and MATCH Functions
Purpose: To find the value corresponding to the first Monday in a range of dates.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Value | Value on First Monday |
| 2 | 2023-07-29 | 10 | =INDEX(B2:B4, MATCH(1, WEEKDAY(A2:A4,2)=1, 0)) |
| 3 | 2023-07-30 | 20 | |
| 4 | 2023-07-31 | 30 |
Explanation: In this example, we want to find the value in column B corresponding to the first Monday in the range A2:A4. The formula =INDEX(B2:B4, MATCH(1, WEEKDAY(A2:A4,2)=1, 0)) returns the value that corresponds to the first Monday in the range A2:A4.
Part 3: Tips and Tricks
The WEEKDAY function can be combined to create more complex formulas. For example, you can use it with the IF function to perform different calculations based on the day of the week.
Remember that Excel stores date as sequential serial numbers so that you can use these numbers in calculations.
The WEEKDAY function can be helpful in scheduling and planning tasks that are based on the day of the week.
The return_type argument allows you to adjust the numbering system for the days of the week to suit your needs.