How to Add Days Excluding Weekends with an Excel Formula?

Sharing is caring!

Have you ever needed to calculate a future date in Microsoft Excel, but wanted to exclude weekends from the calculation? For example, if you have a project deadline 10 business days from now, you can’t just add 10 days to today’s date, because that would include weekends. Luckily, there are Excel formulas you can use to add days excluding weekends.

In this article, we’ll show you step-by-step how to use the WORKDAY function in Excel to add a specified number of business days to a start date. We’ll also cover how to account for holidays, how to calculate dates both forward and backward, and how to handle different work week schedules using WORKDAY.INTL. By the end, you’ll be able to easily calculate future and past dates excluding weekends in Excel.

Why Exclude Weekends in Date Calculations?

In many business and personal scenarios, you need to calculate dates based on the number of working days, rather than calendar days. This is because projects, deadlines, and events often revolve around business days, with work typically stopping over weekends.

For example:

• A manager needs to know the due date for a report that takes 5 business days to prepare
• A teacher wants to schedule a test review 3 school days before the exam date
• A lawyer needs to file a brief 10 business days after receiving case documents

In each situation, weekends should be excluded from the day count to get an accurate result. Adding calendar days would give the wrong date, as it would count weekends. This is where Excel’s WORKDAY functions come in handy.

Using the WORKDAY Function to Add Days Excluding Weekends

The easiest way to add days to a date in Excel while skipping weekends is to use the WORKDAY function. This function takes a start date, a number of days to add, and optionally a list of holidays to exclude, and returns a date that number of workdays in the future.

The syntax for the WORKDAY function is:

=WORKDAY(start_date, days, [holidays])

• start_date is the initial date to start counting from
• days is the number of workdays to add to the start date (can be negative to go backwards)
• [holidays] is an optional argument specifying a range of dates to exclude from the workday count (in addition to weekends)

For example, if you want to calculate the date 10 business days from today, assuming cell A1 contains today’s date, you can use:

=WORKDAY(A1,10)

This will return the date 10 working days (2 weeks) from today’s date in A1, excluding weekends.

Dealing with Holidays

In addition to weekends, there are often other days when work is not done, such as national holidays, company days off, or personal time off. The WORKDAY function allows you to account for these non-working days using the optional holidays argument.

To specify holidays in your WORKDAY formula:

1. Create a list or range in your workbook containing the holiday dates
2. Reference that range as the third argument in your WORKDAY formula

For instance, suppose you have a named range called Holidays containing a list of dates. To calculate a date 10 workdays from today, excluding weekends and holidays, you can use:

=WORKDAY(A1,10,Holidays)

This will add 10 working days to the date in cell A1, skipping over weekends as well as any dates included in the Holidays range.

It’s important to note that the WORKDAY function treats the dates in the holidays argument as additional non-working days, on top of the standard weekend days. So if a holiday falls on a weekend, it won’t be counted twice.

Calculating Workdays Backwards

The days argument in the WORKDAY function can be a positive or negative number. When you use a positive number, it calculates a future date. But you can also use a negative number to calculate a date in the past, counting backward by the specified number of workdays.

For example, to find the date 7 business days before the date in cell A1, you can use:

=WORKDAY(A1,-7)

This will return the date that is 7 working days prior to the date in A1, excluding weekends (and holidays if specified).

Calculating backwards with WORKDAY is useful in scenarios like:

• Finding the date an invoice should have been submitted based on the due date and payment terms
• Determining the start date for a project based on the deadline and estimated workdays required
• Calculating the hire date of an employee based on their work anniversary and tenure

Being able to easily look back and forward in time based on workdays helps in planning, scheduling, and analyzing all kinds of business and personal events.

Handling Different Work Weeks with WORKDAY.INTL

The standard WORKDAY function assumes a Monday-Friday work week, with Saturday and Sunday as the weekend days. However, not all work schedules follow this pattern. Some organizations or roles may have different days off, such as Friday-Saturday weekends, or Sunday-only weekends.

To accommodate these variations, Excel provides the WORKDAY.INTL function. This works similarly to WORKDAY, but allows you to specify which days of the week should be treated as weekends.

The syntax for WORKDAY.INTL is:

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

The additional weekend argument is a number or string indicating which days are considered weekends, according to this table:

So, if your work week runs from Sunday to Thursday, with Friday and Saturday as the weekend days, you can calculate 10 workdays from the date in A1 using:

=WORKDAY.INTL(A1,10,7)

The 7 specifies that Friday and Saturday should be treated as the weekend days.

The [holidays] argument works the same in WORKDAY.INTL as it does in the standard WORKDAY function, allowing you to exclude specified dates in addition to the weekend days.

By using WORKDAY.INTL, you can perform workday calculations that match your specific work schedule, whether it’s a traditional Monday-Friday week or a more unique arrangement.

Putting It All Together: A Step-by-Step Example

Let’s walk through a complete example of using the WORKDAY function to calculate a project completion date based on a start date and required workdays.

Suppose you’re planning a software development sprint. The sprint is set to begin on June 1, 2023, and the team estimates it will take 23 working days to complete. Your team follows a standard Monday-Friday schedule, and you want to account for the July 4th holiday.

Here’s how you can use the WORKDAY function in Excel to find the sprint completion date:

1. In cell A1, enter the sprint start date: 6/1/2023
2. In cell B1, enter the estimated number of workdays: 23
3. In a separate range (e.g., D1:D1), enter the July 4th holiday: 7/4/2023. Name this range “Holidays”.
4. In cell C1, enter the WORKDAY formula:

• =WORKDAY(A1, B1, Holidays)

1. Press Enter. Cell C1 will display the sprint completion date: 7/5/2023

The formula calculates the date 23 workdays after June 1, 2023, skipping weekends and the July 4th holiday.

If the project estimates change, you can update the number in B1, and the completion date will recalculate automatically. Similarly, if more holidays are added to the Holidays range, they will be factored into the result.

This example demonstrates how the WORKDAY function can streamline project planning and scheduling by automatically accounting for weekends and holidays in your date calculations.

Final Thoughts

Excel’s WORKDAY and WORKDAY.INTL functions are powerful tools for calculating dates based on workdays. Whether you’re scheduling future events, setting deadlines, or analyzing past data, these functions make it easy to add or subtract days while excluding weekends and holidays.

The WORKDAY function is ideal for standard Monday-Friday work weeks, while WORKDAY.INTL provides flexibility for non-traditional schedules. Both allow you to specify holiday dates to exclude from the calculations.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.