How to find weekdays in Excel?

Find weekdays in Excel and unlock the potential of dynamic scheduling, precise project planning, and insightful data analysis. This feature becomes your ally in managing time-sensitive tasks, ensuring your timelines, reports, and forecasts reflect the realistic workflow of weekdays. Embrace the simplicity and accuracy of this function to navigate through your spreadsheets with confidence, making every weekday count in your strategic planning and decision-making processes. Let ‘Find weekdays in Excel’ be the cornerstone of your time management and analytical precision, transforming your approach to data-driven success.

This Tutorial Covers:

1. Excel day-of-week function: WEEKDAY
2. Basic Excel WEEKDAY formula
3. Excel date conversion to weekday name
4. How to find and filter workdays and weekends using WEEKDAY formula
5. Excel tips for highlighting workdays and weekends
6. Counting workdays and weekends in Excel
7. If a workday then, if a weekend day, then (Saturday or Sunday)
8. WEEKDAY formula not functioning

1. Excel day-of-week function: WEEKDAY:

The weekday can be obtained from a given date using the Excel WEEKDAY function.

The outcome is an integer that, by default, ranges from 1 (Sunday) to 7 (Saturday). You can set the formula to begin counting on any other day of the week if your business logic calls for a different enumeration.

All Excel 365 through 2000 versions include the WEEKDAY feature.

The WEEKDAY function has the following syntax:

WEEKDAY(serial_number, [return_type])

Where,

serial_number (required) – the date that you want to convert to a weekday number. It can be entered as a date-representing serial number, a text string in an Excel-compliant format, a reference to the column where the date is stored, or by using the DATE function.

return_type (optional) – determines the weekday to be used as the beginning day. defaults to the Sunday-Saturday week if omitted.

There are the following return_type values that are all supported include:

Note that since Excel 2010 introduced the return_type values 11 through 17, they cannot be utilized with older versions.

2. Basic Excel WEEKDAY formula:

Let’s start by demonstrating how to extract the day number from a date using the Excel WEEKDAY formula in its most basic version.

For instance, the formula is as follows to determine the weekday from date in F1 using the standard Sunday-Saturday week:

=WEEKDAY(F1)

You can directly input a serial number that represents a date in the formula if you have one (for example, one produced by the DATEVALUE function):

=WEEKDAY(45027)

Additionally, you can enter the date directly in the formula as a text string contained in quotation quotes. Use the date format that Excel understands by making sure to do so:

=WEEKDAY(“4/11/2023”)

Alternately, use the DATE function to accurately provide the source date:

=WEEKDAY(DATE(2023, 4,11))

Enter the appropriate number in the second parameter to use a different day mapping than the standard Sun-Sat one. For instance, the formula is as follows to begin counting days on Monday:

=WEEKDAY(F1, 2)

All of the calculations in the graphic below return the day of the week that corresponds to April 11, 2023, which is internally recorded in Excel as the number 45027. The output of the formulas varies depending on the value entered as the second argument.

The results of the WEEKDAY function can initially appear to have little application in real life. But let’s take a different tack and talk about some formulas that address practical problems.

3. Excel date conversion to weekday name:

Excel’s WEEKDAY function is intended to return the day of the week as a number. Use the TEXT method to convert the weekday number to the day’s name.

Use the format code “dddd” to obtain the full day’s names:

=TEXT(WEEKDAY(date), “dddd”)

The format code “ddd” will return day names that have been shortened:

=TEXT(WEEKDAY(date), “ddd”)

For instance, the formula is as follows to change the date in A2 to the name of a weekday:

=TEXT(WEEKDAY(A2), “dddd”)

Or

=TEXT(WEEKDAY(A2), “ddd”)

Tip: Using a custom date format is another simple way to change a date to the name of a weekday. For instance, the date will be displayed as “Friday, January 3, 2025” in the coding format “dddd, mmmm d, yyyy,” as opposed to “Friday” in “dddd”.

4. How to find and filter workdays and weekends using WEEKDAY formula?

You might wish to know which days are working days and which are weekends while dealing with a big list of dates.

Create an IF statement in Excel using the nested WEEKDAY function to distinguish between weekends and weekdays.

The steps to search weekdays in Excel are described below:

Step 1: Enter the below formula in cell A2.

=IF(WEEKDAY(A2, 2)<6, “Workday”, “Weekend”)

This formula is duplicated down into as many cells as necessary beginning in cell A2.

You set return_type to 2 in the WEEKDAY formula, which represents the week from Monday to Sunday, where Monday is the first day. Therefore, the algorithm yields “Workday” if the weekday number (Monday through Friday) is less than 6, and “Weekend” otherwise.

Apply an Excel filter to your dataset (Data tab > Filter) and choose “Weekend” or “Workday” to filter out weekends or workdays.

The weekdays have been removed from the screenshot below, leaving only the weekends visible:

You can simply adapt the WEEKDAY formula to your needs by supplying a different return_type if some regional offices of your company operate on a different schedule with rest days other than Saturday and Sunday.

Set return_type to 12 to acquire the “Tuesday (1) to Monday (7)” week type, for instance, to consider Saturday and Monday as weekends:

=IF(WEEKDAY(A2, 12)<6, “Workday”, “Weekend”)

5. Excel tips for highlighting workdays and weekends:

You can automatically have weekends and workdays highlighted in various colors so you can quickly identify them in your worksheet. Use Excel conditional formatting along with the weekday/weekend formula from the previous example for this. We don’t need the IF wrapper because the condition already implies that we only require the basic WEEKDAY function.

To highlight workdays (Monday through Friday), use the formula below:

=WEEKDAY($A2, 2)<6

Use the formula below to highlight weekends (Saturday and Sunday):

=WEEKDAY($A2, 2)>5

where the upper-left cell of the chosen range is designated as A2.

The steps to configure the conditional formatting rule are as follows:

Step 1: Choose the date range. (A2:A30 in our case).

Step 2: To access the Conditional Formatting options, go to the “Home” tab and locate the “Styles” group. From there, click on the “Conditional Formatting” button and select “New Rule” to create a new rule for formatting.

Step 3: “Use a formula to determine which cells to format” should be chosen in the “New Formatting Rule” dialog box.

Step 4: Enter the aforementioned formula for weekdays in the “Format values where this formula is true” box. Select the preferred format by clicking the “Format” button.

 

Step 5: “Format Cells” dialog box opened as a result. Go to “Fill” tab and choose your desired color. Click OK.

Step 6: Repeat the same process for weekend formula To save the changes and exit the dialog boxes, click OK twice. Doesn’t the end result appear to be quite nice?

6. Counting workdays and weekends in Excel:

You can use the WEEKDAY function along with SUM to determine the number of weekdays or weekends in a collection of dates. For instance:

The formula in E1 is to count weekdays as:

=SUM(–(WEEKDAY(A2:A30, 2)<6))

The formula in E2 has the following form when counting weekends:

=SUM(–(WEEKDAY(A2:A30, 2)>5))

This functions as a regular formula in Excel 365 and Excel 2021, both of which support arrays natively, as seen in the screenshot above. To convert a formula into an array in Excel 2019 and prior, use Ctrl + Shift + Enter.

How these formulas operate:

For each date in the range A2:A30, the WEEKDAY function with return_type set to 2 returns a day number ranging from 1 (Mon) to 7 (Sun). The logical expression determines whether the returned numbers are fewer than 6 or more than 5 (for weekends). (for weekdays). An array of values for TRUE and FALSE is produced as a result of this operation.

The logical values are forced to 1s and 0s by the double negation (–). The SUM function then adds them together. You achieve the intended outcome if 1 (TRUE) denotes the days to be counted and 0 (FALSE), the days to be omitted.

7. If a workday then, if a weekend day, then (Saturday or Sunday):

Let’s talk about a more concrete example that demonstrates how to discern the day of the week, and if it’s Saturday or Sunday, then act; accordingly, if it’s a weekday, then act differently.

IF(WEEKDAY(cell, 2)>5, if_weekend_then, if_weekday_then)

Imagine that you need to use different payment rates for workdays and weekends in order to account for employees who performed extra work on their days off. The IF statement below can be used to do this:

• Nest the WEEKDAY function, which determines if a given day is a workday or a weekend, in the logical_test argument.
• Multiply the amount of working hours by the weekend rate in the value_if_true argument. (G3).
• Multiply the number of working hours by the workday rate in the value_if_false argument. (G2).

The entire formula in D2 is expressed as follows:

=IF(WEEKDAY(B2, 2)>5, C2*$G$3, C2*$G$2)

Make sure to lock the rate cell addresses with the $ sign (such as $G$2) for the formula to replicate appropriately to the cells below.

8. WEEKDAY formula not functioning:

The WEEKDAY function in Excel can be used to manipulate days of the week, but it may return errors in certain situations. The two common errors that may occur are:

• #VALUE! error: This error occurs when either the serial number or return type argument is non-numeric or when the serial number argument is outside of the supported date range (1900 to 9999).
• #NUM! error: This error occurs when the return type argument is outside of the permitted range of 1-3 or 11-17.

To avoid these errors, make sure to provide the correct data types for the serial number and return type arguments. Additionally, ensure that the serial number argument falls within the supported date range and that the return type argument falls within the permitted range of 1-3 or 11-17.