The Problem

Ever stared at a long list of dates in Excel, needing to know if each falls on a weekend, a Monday, or perhaps a critical Friday deadline? Manually checking each date against a calendar is not only tedious but incredibly prone to error, especially when dealing with hundreds or thousands of entries. Imagine you're a project manager trying to calculate resource availability, a HR professional scheduling shifts, or an analyst identifying sales trends specific to certain days. You need a fast, accurate way to extract the day of the week from your dates.

What is WEEKDAY? The WEEKDAY function is an Excel function that returns the day of the week for a given date, represented as a number from 1 (Sunday) to 7 (Saturday) by default. It is commonly used to categorize dates, schedule tasks, or analyze weekly trends without manual calendar lookups. Without WEEKDAY, you're left guessing or painstakingly inputting data, which is exactly the kind of repetitive task Excel was designed to eliminate.

The Ingredients: Understanding WEEKDAY's Setup

The WEEKDAY function is a straightforward yet incredibly powerful tool in your Excel toolkit. It takes a date and tells you which day of the week it represents, expressed as a number. The beauty lies in its flexibility, allowing you to define what day starts your week.

Here’s the precise syntax you’ll use:

WEEKDAY(serial_number, [return_type])

Let's break down each 'ingredient' required for this recipe:

| Parameter | Description The WEEKDAY function in Excel is a vital tool for anyone working with dates and needing to perform specific actions based on the day of the week. This recipe will guide you through understanding its parameters, using it in practical scenarios, and troubleshooting common issues. We’ll turn your raw date data into actionable insights, helping you manage schedules, analyze trends, and automate processes with confidence.

1. The Problem

You're looking at a spreadsheet filled with project deadlines, customer order dates, or perhaps sales transaction timestamps. Your manager needs a report showing all deliveries scheduled for a Friday, or perhaps you need to identify weekend sales trends. Manually sifting through thousands of dates, checking a calendar for each one, is not only mind-numbingly tedious but highly susceptible to errors. You need a fast, reliable way to determine the day of the week for each date.

What is WEEKDAY? WEEKDAY is an Excel function that returns the day of the week for a given date, represented as an integer. It is commonly used to categorize dates for reporting, automate scheduling based on specific weekdays, or to apply conditional formatting to weekends. This function saves countless hours, allowing you to move beyond manual date checking.

2. The Ingredients: Understanding WEEKDAY's Setup

The WEEKDAY function is your go-to for extracting the day-of-the-week number from any given date in Excel. It’s simple, yet its optional argument provides powerful flexibility in how it presents the day numbers.

Here’s the exact syntax you'll use:

WEEKDAY(serial_number, [return_type])

Let's break down each component, much like laying out your ingredients before cooking:

| Parameter | Description
This WEEKDAY function is an Excel function that returns the day of the week for a given date. It is commonly used to categorize dates, schedule tasks, or analyze weekly trends. The serial_number refers to the date you want to evaluate. This can be a direct date entered in double quotes (e.g., "1/15/2025"), a reference to a cell containing a date, or the result of another date function like TODAY() or DATE(). Excel stores dates as serial numbers, starting with 1 for January 1, 1900.

The [return_type] is an optional numerical value that tells WEEKDAY how to number the days of the week. This is crucial for matching your specific calendar week start. If omitted, Excel defaults to 1, where Sunday is 1, Monday is 2, and so on, up to Saturday as 7.

Here are the most common return_type values:

Understanding return_type is key to getting the results you expect from WEEKDAY.

3. The Recipe: Step-by-Step Instructions

Let’s apply the WEEKDAY function to a real-world scenario. Imagine you're managing a sales team, and you've recorded various sales dates. You want to quickly determine which day of the week each sale occurred to identify patterns, such as higher sales on specific weekdays.

Here's our sample data:

We want to fill in the "Day of Week Number" column using the WEEKDAY function, assuming Monday is the first day of the week (return type 2).

1. Prepare Your Data:

Ensure your dates are in a valid Excel date format. In our example, they are in column A, starting from A2.

2. Select Your Output Cell:

Click on cell B2, where you want the first WEEKDAY result to appear. This cell will show the numerical day of the week for the date in A2.

3. Enter the WEEKDAY Formula:

In cell B2, type the following formula:
=WEEKDAY(A2, 2)

This formula tells Excel to look at the date in A2 and return its day of the week. We're using 2 as the return_type because, in this scenario, we prefer Monday to be represented as 1, Tuesday as 2, and so on, with Sunday as 7. This is a common preference for many business reports.

4. Press Enter and Drag:

After typing the formula, press Enter. You'll see the result 3 in cell B2. This indicates that January 15, 2025, was a Wednesday (since Monday=1, Tuesday=2, Wednesday=3).
Now, click on cell B2 again. Grab the fill handle (the small green square at the bottom-right corner of the cell) and drag it down to cell B7 to apply the formula to all other dates in your list.

Your table will now look like this:

5. Add Day of Week Names (Optional but Recommended):

To make the results more user-friendly, you can combine WEEKDAY with the CHOOSE function or the TEXT function to display the actual day name. For column C ("Day of Week Name"), let's use CHOOSE to translate the numbers into names.

In cell C2, enter this formula:
=CHOOSE(WEEKDAY(A2,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")

This formula first calculates the WEEKDAY using return_type 2. Then, CHOOSE uses that number to pick from the list of text values ("Mon", "Tue", "Wed", etc.). Drag this formula down to C7.

Your final table with the WEEKDAY function providing both numerical and textual day of week will be:

This setup allows you to quickly identify sales patterns, such as the two Saturday sales and one Sunday sale, helping you better understand your team's performance across the week.

4. Pro Tips: Level Up Your Skills

Mastering the WEEKDAY function goes beyond basic application. Here are some expert tips to enhance your use of this versatile formula:

• Use the return_type argument to match your preferred numbering system for the week (e.g., 1 for Sunday=1, 2 for Monday=1). This is a critical best practice that ensures your results align with your local calendar or reporting standards, preventing confusion and errors down the line. We've seen many users stick to the default without realizing they can customize it.

• Combine with CHOOSE for Day Names: As demonstrated in our recipe, WEEKDAY outputs a number. To get the actual day name (e.g., "Monday" instead of "2"), pair it with the CHOOSE function or the TEXT function. For example, =TEXT(A2,"ddd") or =TEXT(A2,"dddd") can also retrieve the day name directly, often being a more concise alternative to CHOOSE.

• Identify Weekends for Conditional Formatting: WEEKDAY is invaluable for highlighting weekends. For instance, to color-code rows that fall on a Saturday or Sunday, you can use a conditional formatting rule with a formula like =OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7) applied to your range. Experienced Excel users prefer this for visual clarity in schedules.

• Integrate into Scheduling Formulas: If you're calculating workdays or project timelines, WEEKDAY can be a powerful component of more complex formulas. For instance, to exclude weekends when calculating a future date, you might use it in conjunction with WORKDAY or NETWORKDAYS to ensure only business days are counted.

5. Troubleshooting: Common Errors & Fixes

Even the best chefs encounter minor mishaps. When working with the WEEKDAY function, a couple of common errors can pop up. Understanding them is your first step to a quick fix.

1. #VALUE! Error

• What it looks like: You see #VALUE! in the cell where your WEEKDAY formula is.
• Why it happens: This error occurs if the serial_number argument (the date you're trying to evaluate) is not a valid date format that Excel can recognize. A common mistake we've seen is entering text that looks like a date but isn't parsed correctly by Excel, or referencing an empty cell or a cell containing non-date text. For example, "January 32, 2025" or text like "N/A" will cause this.
• How to fix it:
  1. Check the cell: Ensure the cell referenced in your serial_number argument (e.g., A2 in WEEKDAY(A2,2)) contains a genuine Excel date. You can test this by formatting the cell as a "General" number; a valid date will turn into a number (like 45678), while invalid text will remain text.
  2. Use DATEVALUE: If your date is text that should be a date (e.g., "2025-01-15" entered as text), you can wrap it in DATEVALUE, like =WEEKDAY(DATEVALUE(A2),2). Be cautious, as this only works if the text is perfectly formatted.
  3. Correct the source data: The best solution is often to correct the source data itself so Excel recognizes it as a date.

2. #NUM! Error

• What it looks like: Your WEEKDAY formula displays #NUM!
• Why it happens: This error specifically occurs if the return_type argument you've provided is outside the allowed range of values. According to Microsoft documentation, valid return_type values are typically integers from 1 to 17. Using 0, 18, or any non-integer value (other than omitting it) for return_type will trigger this error.
• How to fix it:
  1. Verify return_type: Double-check the second argument in your WEEKDAY function. Make sure it's one of the recognized return_type numbers (e.g., 1, 2, 3, 11, 12, etc.). The most commonly used are 1, 2, and 3.
  2. Remove if unnecessary: If you intend to use the default (Sunday=1), you can simply omit the return_type argument entirely, e.g., =WEEKDAY(A2).

By keeping an eye out for these errors and knowing their solutions, you'll be able to quickly diagnose and fix any issues that arise, ensuring your WEEKDAY calculations are always accurate.

6. Quick Reference

Here's a concise summary of the WEEKDAY function to keep in your mental recipe box:

• Syntax: WEEKDAY(serial_number, [return_type])
• Purpose: Returns a number from 1 to 7 (or 0 to 6, depending on return_type) representing the day of the week for a given date.
• Most Common Use Case: Quickly identifying the day of the week for scheduling, data filtering, or conditional formatting.
• Key Gotcha to Avoid: Forgetting to specify the return_type if your week doesn't start on Sunday, or using an invalid return_type which results in a #NUM! error. Always consider your preferred week start!
• Related Functions to Explore:
  • WORKDAY: Calculates a date before or after a specified number of workdays.
  • NETWORKDAYS: Returns the number of whole workdays between two dates.
  • TEXT: Converts a value to text in a specific number format (e.g., =TEXT(A2,"dddd") to get full day name).
  • CHOOSE: Selects a value from a list of values based on an index number.
  • DATE: Returns the serial number of a particular date.