To calculate working days between two dates in Excel, use the WORKDAY or WORKDAY.INTL function. 1. Use WORKDAY with a start date and workdays to add/subtract, e.g., =WORKDAY(A1,5). 2. For custom weekends, apply WORKDAY.INTL with a weekend code or string like "0000011". 3. Include holidays by adding a holiday range, e.g., B1:B5. Ensure dates are valid, formatted correctly, and holiday lists are accurate to avoid errors.

If you need to calculate working days between two dates in Excel, the WORKDAY function is a solid tool. It helps you skip weekends and even holidays if needed. Here’s how to use it effectively.

Basic Use of WORKDAY

At its core, WORKDAY calculates a date that is a certain number of workdays ahead or behind a start date. The default setup assumes a Monday–Friday workweek.
To use it, you just need two things: a start date and the number of workdays to add (or subtract). For example:

=WORKDAY(A1, 5)

This means: starting from the date in cell A1, give me the date 5 workdays later. If A1 is a Monday, the result will be Friday.
You can also use negative numbers to go backward. Like this:

• =WORKDAY(A1, -3) gives you the date 3 workdays before A1.

One thing to remember: make sure your cell is formatted as a date, or you’ll see a serial number instead of a readable date.

Custom Weekends with WORKDAY.INTL

If your schedule doesn’t follow the standard Mon–Fri pattern—say, you work Tue–Sat or have a 4-day week—you should use WORKDAY.INTL. This version lets you define which days are weekends.
The syntax looks like this:

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

The key part here is the [weekend] argument. You can either use a preset code (like 11 for Sunday only, 17 for Saturday only) or a custom string of 7 digits (1s and 0s) representing each day of the week.
For instance:

• "0000011" means Saturday and Sunday are weekends.
• "1000000" means Monday is the only weekend day.

So if you want to move 4 workdays forward from A1, but your weekend is Thursday and Friday, you’d write:

=WORKDAY.INTL(A1, 4, "0011000")

Adding Holidays

Both WORKDAY and WORKDAY.INTL can account for holidays. Just provide a range or list of holiday dates as the last argument.
Say your company has holidays listed in cells B1:B5. Your formula might look like:

=WORKDAY(A1, 10, B1:B5)

Or with WORKDAY.INTL:

=WORKDAY.INTL(A1, 10, 1, B1:B5)

Here, 1 sets the weekend as Sat–Sun by default. Make sure the holiday list includes actual dates and no extra text or blanks, or the function might throw an error.

A few tips:

• Dates must be valid Excel dates (not text).
• Holidays should be in chronological order, though it usually still works if they’re not.
• Always double-check that your holiday range is absolute (e.g., $B$1:$B$5) if you plan to copy the formula down a column.

That’s basically all you need to get started with the WORKDAY function. It’s not complicated, but small mistakes—like wrong date formats or missing holidays—can throw off your results.

The above is the detailed content of how to use the workday function in excel. For more information, please follow other related articles on the PHP Chinese website!