5 Excel Weekday Formulas

Introduction to Excel Weekday Formulas

Excel provides a variety of formulas to manipulate and analyze date and time data, including determining the day of the week. These formulas can be incredibly useful in scheduling, planning, and data analysis tasks. In this article, we will explore five essential Excel weekday formulas that can help you work more efficiently with dates.

1. WEEKDAY Function

The WEEKDAY function in Excel returns the day of the week as a number (1 for Sunday, 2 for Monday, …, 7 for Saturday) for a given date. The syntax of the WEEKDAY function is:

WEEKDAY(date, [return_type])

• date: The date for which you want to find the day of the week.
• [return_type]: Optional. A number that determines the day of the week and the way the week is represented.
  • If [return_type] is 1 (default) or omitted, the function returns 1 (Sunday) through 7 (Saturday).
  • If [return_type] is 2, the function returns 1 (Monday) through 7 (Sunday).
  • If [return_type] is 3, the function returns 0 (Monday) through 6 (Sunday).

📝 Note: Understanding the return types can help you tailor the output to fit your specific needs or regional preferences.

2. TEXT Function for Weekdays

To display the actual day of the week (e.g., “Monday”, “Tuesday”) instead of a number, you can use the TEXT function in combination with the WEEKDAY function or directly with a date. The syntax for the TEXT function when used with a date to display the weekday is:

TEXT(date, "dddd")

• date: The date for which you want to display the weekday.
• “dddd”: A format code that displays the full weekday name.

For example, =TEXT(TODAY(), "dddd") will display the current day of the week.

3. Using IF with WEEKDAY for Conditional Logic

You can combine the WEEKDAY function with the IF function to apply different rules based on the day of the week. For instance, to check if a date falls on a weekend (Saturday or Sunday), you can use:

=IF(OR(WEEKDAY(A1)=1, WEEKDAY(A1)=7), "Weekend", "Weekday")

• A1: The cell containing the date you want to check.
• OR(WEEKDAY(A1)=1, WEEKDAY(A1)=7): Checks if the day of the week is Sunday (1) or Saturday (7).
• “Weekend”: The value returned if the condition is true.
• “Weekday”: The value returned if the condition is false.

4. NETWORKDAYS Function for Workdays

The NETWORKDAYS function calculates the number of workdays (Monday through Friday) between two dates, excluding weekends and any dates you specify as holidays. The syntax is:

NETWORKDAYS(start_date, end_date, [holidays])

• start_date: The start date of the period.
• end_date: The end date of the period.
• [holidays]: An optional range of dates to exclude from the working days.

For example, =NETWORKDAYS(A1, B1, C1:C3) calculates the workdays between the dates in A1 and B1, excluding any dates listed in C1:C3.

5. WORKDAY Function for Scheduling

The WORKDAY function returns a date that is a specified number of workdays (excluding weekends and holidays) before or after a starting date. The syntax is:

WORKDAY(start_date, days, [holidays])

• start_date: The starting date.
• days: The number of workdays before or after the start date. A positive value returns a date in the future; a negative value returns a date in the past.
• [holidays]: An optional range of dates to exclude.

For example, =WORKDAY(A1, 10, C1:C3) returns the date 10 workdays after the date in A1, excluding any dates listed in C1:C3.

Summary of Formulas

Here is a summary of the formulas discussed:

Formula	Description
WEEKDAY(date, [return_type])	Returns the day of the week as a number.
TEXT(date, “dddd”)	Displays the full weekday name.
NETWORKDAYS(start_date, end_date, [holidays])	Calculates the number of workdays between two dates.
WORKDAY(start_date, days, [holidays])	Returns a date a specified number of workdays from a start date.

In conclusion, mastering these Excel weekday formulas can significantly enhance your ability to work with dates and schedules, making you more efficient in your tasks and analyses. Whether you’re scheduling appointments, calculating project timelines, or analyzing data trends, these formulas provide powerful tools to achieve your goals.