WEEKDAY function

1 year ago

4 minutes

The WEEKDAY function in Excel returns the day of the week as a number for a given date. This can be useful for determining which day of the week a specific date falls on or for performing calculations based on weekdays.

Syntax

WEEKDAY(serial_number, [return_type])

Parameters

serial_number: The date for which you want to determine the weekday. This can be a cell reference containing a date, or a date entered as a text string, or a result from another date function (like TODAY()).
[return_type] (optional): A number that determines the day of the week numbering system. This parameter allows you to customize how the days of the week are represented. If omitted, the default is 1.
- 1 or omitted: Sunday = 1, Monday = 2, …, Saturday = 7.
- 2: Monday = 1, Tuesday = 2, …, Sunday = 7.
- 3: Monday = 0, Tuesday = 1, …, Sunday = 6.

How It Works

The WEEKDAY function returns an integer (between 1 and 7, depending on the return type), which corresponds to a day of the week for the given date.
By adjusting the return_type, you can get different numbering schemes based on your preference or regional settings.

Examples

Basic Usage with Default Return Type (1): To find the weekday number for the date January 13, 2025, assuming A1 contains the date:
```
=WEEKDAY(A1)
```
This will return 2 because January 13, 2025, falls on a Monday, and the default setting numbers Sunday as 1, Monday as 2, etc.
Specifying the Return Type: If you want to use Monday as 1 and Sunday as 7, you can use return type 2:
```
=WEEKDAY(A1, 2)
```
This will return 1 because January 13, 2025, is a Monday, and with return type 2, Monday is considered the first day of the week.
Using Return Type 3: If you prefer the week to start on Monday with numbers starting from 0, you can use return type 3:
```
=WEEKDAY(A1, 3)
```
This will return 0, because January 13, 2025, is a Monday, and with return type 3, Monday is treated as day 0.
Using with the TODAY Function: If you want to find the weekday for today’s date, you can combine WEEKDAY with the TODAY function:
```
=WEEKDAY(TODAY())
```
This will return the weekday number for the current date, based on the default return type.
Weekday Name Using TEXT: You can combine WEEKDAY with the TEXT function to get the name of the day:
```
=TEXT(A1, "dddd")
```
This will return the full name of the weekday, such as “Monday”, for the date in A1.

Common Use Cases

Identifying Weekends: The WEEKDAY function is often used to check if a given date falls on a weekend. For example, to determine if a date in A1 is a weekend (Saturday or Sunday), you can use:
```
=IF(OR(WEEKDAY(A1) = 1, WEEKDAY(A1) = 7), "Weekend", "Weekday")
```
This will return “Weekend” if the date in A1 is a Saturday or Sunday, and “Weekday” otherwise.
Scheduling: You can use WEEKDAY to determine the day of the week for scheduling purposes, like finding out which days of the week certain events fall on, and creating conditional formatting rules for those days.
Counting Weekdays or Weekends: You can use WEEKDAY in combination with COUNTIF or other functions to count the number of weekdays or weekends in a range of dates.

Important Notes

The serial_number argument must be a valid date recognized by Excel. If it’s not a valid date, WEEKDAY will return a #VALUE! error.
The WEEKDAY function returns an integer that represents the day of the week. By changing the return_type, you can adjust which day corresponds to the number 1.
If you need the day of the week as a text string (e.g., “Monday”), you can use the TEXT function in combination with WEEKDAY or directly use TEXT with a date.

Summary

The WEEKDAY function in Excel helps identify the day of the week for a given date by returning an integer between 1 and 7 (or a custom numbering system, depending on the return_type). It is useful for various date-based calculations, such as determining weekends, scheduling events, or counting specific days of the week in a range of dates.

Post Views: 25