Subtract Business Days and Skip Weekends in Excel with WORKDAY
Question details
Calculate a date a specified number of working days before an arrival date while excluding weekends.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to subtract five business days from an arrival-date value in a worksheet.
- Observed behavior
- The resulting date should skip Saturdays and Sundays, with an option to exclude listed public holidays.
Make sure the Arrival date column contains real Excel dates rather than text. If holidays must be excluded, enter each holiday date in a separate contiguous cell range.
Use WORKDAY to subtract business days from an arrival date
Use this formula when weekends are Saturday and Sunday and you need a date a fixed number of workdays before an arrival date.
WORKDAY adds or subtracts working days from a start date. A negative number subtracts days, and the function automatically skips Saturday and Sunday.
Click the cell where you want the calculated business-day date to appear.
Type =WORKDAY([@[Arrival date]],-5) and press Enter. The -5 argument subtracts five working days from the Arrival date in the current table row.
Drag the fill handle down, or double-click it, to apply the formula to other rows in the Excel table or range.
Exclude public holidays with a WORKDAY holiday range
Use this version when company closures or public holidays should not count as business days.
Calculate Earlier Business Dates with WORKDAY in WPS Spreadsheet
WPS Spreadsheet supports the WORKDAY function for calculating dates before or after an arrival date while skipping weekends and optional holiday dates. Enter the formula once, then fill it down to calculate schedules for the rest of your table.
- 1. Open the workbook in WPS Spreadsheet: Open your Excel workbook in WPS Office and select the worksheet containing the Arrival date column.
- 2. Select an output cell: Click the cell beside the first arrival date where the earlier business date should be displayed.
- 3. Enter the WORKDAY formula: Type =WORKDAY([@[Arrival date]],-5) for a table, or use a regular date-cell reference such as =WORKDAY(A2,-5).
- 4. Include holidays when required: Add your holiday cells as the third argument, for example =WORKDAY(A2,-5,$H$2:$H$20).
- 5. Copy the formula to remaining rows: Use the fill handle to apply the formula down the column and calculate each business-day date.
Frequently Asked Questions
What does -5 mean in the WORKDAY formula?
In =WORKDAY(start_date,-5), the negative 5 tells Excel to move five working days backward from the start date. Positive numbers move forward instead.
How do I exclude holidays when subtracting workdays in Excel?
Add a holiday range as the third WORKDAY argument. For example, =WORKDAY(A2,-5,$H$2:$H$20) subtracts five workdays and ignores dates listed in H2:H20.
Does WORKDAY skip both Saturday and Sunday?
Yes. WORKDAY treats Saturday and Sunday as nonworking days by default. Use WORKDAY.INTL if your organization has a different weekend pattern.
Why does WORKDAY return a number instead of a date?
The cell is likely formatted as General or Number. Select the result cell, then use Home > Number Format > Short Date or Long Date to display it as a date.
Can I subtract business days from a date stored in an Excel table?
Yes. With an Arrival date table column, use a structured reference such as =WORKDAY([@[Arrival date]],-5) in a calculated column.
