Use Excel’s WORKDAY function to calculate a date a set number of working days before or after another date, while skipping weekends and any holidays you list. In a worksheet, enter a start date and a number of days, then use =WORKDAY(start_date, days, [holidays]). For example, five working days after Wednesday, July 8, 2026, is Wednesday, July 15, 2026, assuming Saturday and Sunday are weekends and there are no holidays in between.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or a recent Excel version. The function is also available in Excel for Mac and Excel for the web.
- A valid start date and the number of working days to add or subtract.
- Optionally, a range of holiday dates to exclude. Use actual Excel dates, not date-looking text, and make sure the holiday list covers the period you are calculating.
By default, WORKDAY treats Saturday and Sunday as nonworking days. If your workweek uses different weekend days, use WORKDAY.INTL instead. The function does not know your organization’s holidays unless you provide them.
Step-by-Step
- Set up a small input table. In a blank worksheet, put a heading such as Start date in A1 and Days in B1. Enter the starting date in A2 and the number of working days in B2. For a reusable sheet, enter the start date and day count as separate values rather than typing both into a formula. That makes it easier to change the schedule and check the inputs. For a date that should not depend on how your computer interprets a typed date, you can enter it with Excel’s DATE function.
- Choose what the day count means. A positive number moves forward in the calendar; a negative number moves backward. WORKDAY counts eligible working days, not calendar days, and it does not count the starting date as day one. For example, when starting on a Wednesday and adding one working day, the result is Thursday, unless Thursday is in the holiday list. Enter a whole number for a clear, predictable schedule.
- Select the result cell. Click an empty cell, such as C2, where you want the calculated date to appear. A separate result column keeps the original start date intact and lets you copy the calculation down for other rows. If your inputs are in different cells, use those cell references in the formula rather than copying the example references unchanged.
- Enter the basic WORKDAY formula. With the start date in A2 and the number of working days in B2, enter
=WORKDAY(A2,B2)in C2 and press Enter. For example, if A2 contains July 8, 2026, and B2 contains 5, the result is July 15, 2026: Thursday July 9 and Friday July 10 are days one and two, Monday July 13 and Tuesday July 14 are days three and four, and Wednesday July 15 is day five. Saturday and Sunday are skipped. - Include holidays when they affect the schedule. Put each holiday date in its own cell, for example in E2:E10, and use that range as the third argument. Include relevant observed holidays if your workplace closes on those dates; Excel does not infer them from a country or calendar. With the start date in A5 set to July 7, 2026, the day count in B5 set to 3, and July 9, 2026, listed as a holiday in D2, enter
=WORKDAY(A5,B5,D2:D2)in C5. The result is July 13, 2026: July 8 is day one, July 9 is excluded, July 10 is day two, and Monday July 13 is day three. You can use a longer holiday range, such as D2:D20, as long as it contains the dates you want excluded. - Calculate a date in the past if needed. Use a negative day count to move backward from the start date. With a valid start date in A2 and -5 in B2, the formula
=WORKDAY(A2,B2)returns the date five working days before A2, skipping Saturday and Sunday. Add the holiday range as the third argument when those earlier dates also need to exclude holidays. Check the result against the calendar if the calculation crosses a year boundary. - Format the answer as a date. If the result appears as a number, select the result cell and choose Home > Number Format, then select a date format. You can also open Format Cells with Ctrl+1 on Windows; on Mac, use Command+1. The underlying result is an Excel date serial number, so changing the display format changes how it looks, not the calculated date.
- Copy the calculation down for a list. Select C2 and drag the fill handle, the small square at the bottom-right corner of the selection, down the column. Excel will adjust relative references for each row, so C3 will refer to A3 and B3. If the holiday range must remain fixed as you fill down, change it to an absolute reference before copying. With start dates in A2:A20, day counts in B2:B20, and holidays in E2:E20, enter
=WORKDAY(A2,B2,$E$2:$E$20)in C2 and fill down. The dollar signs keep the holiday range fixed while the row-specific inputs change. - Check the output against your work calendar. Confirm that the start date, day count, weekend pattern, and holiday list match the rule you are trying to apply. Try a short example by counting forward manually across a calendar. This is particularly useful for deadlines near holidays, year-end dates, or a weekend that differs from Excel’s standard Saturday–Sunday weekend.
The optional third argument is a list of dates to ignore, not a weekend setting. If you need to define weekends such as Friday and Saturday, use WORKDAY.INTL. With a start date in A2, a day count in B2, and holidays in E2:E20, the formula =WORKDAY.INTL(A2,B2,7,$E$2:$E$20) uses weekend code 7 for Friday and Saturday. Check the weekend-code list in Excel’s function help if your work pattern is different; WORKDAY.INTL also accepts a seven-character weekend pattern.
Common Mistakes
- Counting calendar days instead of working days. Adding five directly to a date includes weekends. WORKDAY is intended for a count of workdays; a simple date addition answers a different question.
- Forgetting the holiday argument. WORKDAY skips Saturday and Sunday by default, but it will count a public or company holiday as a workday unless that date appears in the holidays range.
- Entering dates as text. A value that looks like a date may be stored as text, especially if it was imported or pasted from another system. Text dates can cause a #VALUE! error or behave unexpectedly. Convert them to real Excel dates before relying on the calculation.
- Using the wrong cell references. A formula copied down may shift a holiday range along with the row inputs. Lock a fixed holiday range with dollar signs, such as $E$2:$E$20, before filling the formula down.
- Expecting a custom workweek from WORKDAY. The basic function always uses Saturday and Sunday as weekends. Use WORKDAY.INTL for a different weekend schedule.
- Confusing the start date with day one. WORKDAY starts counting after the starting date. If the start date itself is a working day, adding one returns the next working day, not the same date.
Tips
- Keep holiday dates in one clearly labeled column and use the same list across formulas. This makes it easier to update a schedule when a closure is added or changed.
- Use real date values in the holiday list. A reliable way to create a particular date in a formula is to use DATE with year, month, and day arguments; this avoids ambiguity between regional date styles.
- Give the input and result columns descriptive headings, and format them consistently. A sheet labeled Start date, Workdays to add, Holidays, and Due date is easier to review than a column of unexplained numbers.
- For repeated calculations, use an Excel Table so formulas and formatting can extend as you add rows. Still check that the holiday reference stays fixed or points to the intended holiday list.
- Use a negative day count for a backward deadline calculation, such as finding the date a specified number of business days before a delivery date. Include holidays that fall in that backward-looking period.
- On Mac and in Excel for the web, the WORKDAY formula itself works the same way. Ribbon layouts and keyboard shortcuts can differ from Windows, but you can enter the formula directly into a cell or the formula bar.
Troubleshooting
The formula returns #VALUE!. Check that the start date is a valid Excel date and that the day count is numeric. Inspect pasted dates in particular: changing a cell’s number format to Date does not convert text into a date. Re-enter the value as a date or convert the source data, then recalculate.
The result is a five-digit number. The calculation may be correct; Excel stores dates as serial numbers. Select the result and apply a date format through Home > Number Format. If a date format still shows an unexpected date, check the workbook’s source values and date system rather than treating the serial number as text.
The result is off by one or lands on an unexpected day. Remember that the starting date is not counted as the first workday. Count forward or backward on a calendar, crossing weekends, and verify whether the start date or any intervening dates are in the holiday list. Also check that the day count is the number of working days you intend, not the number of calendar days.
A holiday is not being skipped. Confirm that the holiday date is included in the third argument’s range and stored as a date value. Check for a missing observed day, a range that stops too early, or a copied formula whose holiday range shifted. If the holiday is entered as text, replace or convert it to a real date.
The formula is rejected when you type it. Check the spelling of WORKDAY, the parentheses, and the commas between arguments. Some regional Excel settings use semicolons as formula separators; if Excel flags the commas, use the separator required by your installation. The examples here use standard comma-separated Excel syntax.
Your weekend pattern is wrong. The basic WORKDAY function cannot change its default Saturday–Sunday weekend. Replace it with WORKDAY.INTL and specify the appropriate weekend code or weekend pattern, then check the result against your organization’s calendar.
Frequently Asked Questions
Does WORKDAY include the starting date in its count?
No. It counts working days after the start date when moving forward, or before it when moving backward. For a start date that is itself a working day, a count of one returns the next working day.
Can WORKDAY calculate dates backward?
Yes. Enter a negative number for the days argument. The function moves backward by that many working days and still skips the default weekend and any holidays supplied in the optional third argument.
Can I use WORKDAY without a holiday list?
Yes. Leave out the optional third argument when you only need to skip Saturday and Sunday. Add a holiday range when public, company, or other nonworking dates should also be excluded.
How do I use a weekend other than Saturday and Sunday?
Use WORKDAY.INTL, which allows you to specify a weekend pattern or code. The ordinary WORKDAY function has no argument for changing which days count as weekends.
Why does the result show a number instead of a date?
Excel stores dates as serial numbers. Apply a date number format to the result cell; this changes the display without changing the calculation.
Does WORKDAY count half-days or working hours?
No. WORKDAY works with whole working days, not hours or partial shifts. For schedules based on hours, shifts, or varying daily hours, a different calculation method is needed.
Related
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Number of Days Between Two DatesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Excel Date Minus Days: Formulas and ExamplesTo subtract calendar days from a date in Excel, subtract a number from the date cell: if the…
- Excel Find and Replace Exact Match: What to KnowTo find or replace a cell only when its entire contents match, press Ctrl+H in Excel for Microsoft…
- DATEDIF Spreadsheet Examples: Years, Months, and DaysIn Excel for Microsoft 365, use DATEDIF(start_date,end_date,unit) to calculate the elapsed time between two dates in completed years,…
- Excel Function to Check If a Cell Contains TextTo check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text…
- Excel Accounting Formulas Cheat Sheet for Daily BooksAn Excel accounting formulas cheat sheet starts with =SUM(range) for totals, a simple addition formula for running balances,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.