Excel Formula to Calculate Three Business Days Before a Ship Date
Question details
Calculate the date that is three business days before a specified ship date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A ship date is stored in a worksheet cell, and the user needs a preceding date that excludes Saturday and Sunday.
- Observed behavior
- The desired result is a valid date exactly three working days before the ship date, with weekends and optionally listed holidays skipped.
Make sure the ship date cell contains a real Excel date value rather than text. If holidays must be excluded, enter each holiday as a date in a separate cell range.
Use WORKDAY to subtract three business days from the ship date
Use this formula when Saturday and Sunday are the only non-working days that need to be excluded.
The WORKDAY function moves forward or backward by working days. A negative number subtracts business days, and Excel excludes Saturdays and Sundays by default.
Click the cell where you want the calculated preparation date to appear, such as E2.
Type =WORKDAY(D2,-3) and press Enter, replacing D2 with the cell that contains your ship date.
Select the result cell, then use Home > Number Format and choose Short Date or Long Date to display the result as a date.
Include a holiday list in the WORKDAY calculation
Use this option when company holidays or other non-working dates must also be skipped.
Calculate Ship Preparation Dates with WORKDAY in WPS Spreadsheets
WPS Spreadsheets supports the WORKDAY function for calculating a date before or after a ship date while excluding weekends and a custom holiday list. Open your worksheet, enter the formula, and format the result as a date.
- 1. Open the shipping worksheet: In WPS Office, open the Excel workbook in WPS Spreadsheets and locate the ship date column.
- 2. Select the output cell: Click the cell beside the ship date, such as E2, where the business-day result should appear.
- 3. Enter the business-day formula: Type =WORKDAY(D2,-3) and press Enter. To skip listed holidays, use =WORKDAY(D2,-3,$H$2:$H$20).
- 4. Format and copy the formula: Choose Home > Number Format > Date, then drag the fill handle down to calculate dates for additional ship-date rows.
Frequently Asked Questions
What does -3 mean in the WORKDAY formula?
The -3 argument tells WORKDAY to move backward by three working days. Positive values move forward, while negative values move backward.
Does WORKDAY exclude Saturdays and Sundays automatically?
Yes. =WORKDAY(D2,-3) treats Saturday and Sunday as non-working days by default, so no extra weekend setting is required.
How do I exclude holidays from the ship-date calculation?
Put holiday dates in a range such as H2:H20, then use =WORKDAY(D2,-3,$H$2:$H$20). Excel skips any dates in that range.
Why is my WORKDAY result displayed as a number?
The result is likely formatted as General or Number. Select the result cell and choose Home > Number Format > Short Date or another date format.
Can I use WORKDAY when my weekend is not Saturday and Sunday?
Use WORKDAY.INTL instead. It lets you specify a custom weekend pattern, for example =WORKDAY.INTL(D2,-3,1,$H$2:$H$20) for the standard Saturday-Sunday weekend.
