How to Check a Three-Working-Day Deadline in Excel Using Formulas
Question details
The user needs to calculate whether a specific deadline was met within a three-working-day timeframe, explicitly excluding weekends and custom public holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project milestones or task completions against a strict Service Level Agreement (SLA) of three working days.
- Observed behavior
- Needs an accurate logical formula that outputs 'Yes' or 'No' to indicate if the elapsed time successfully meets the deadline criteria.
Prepare a list of your local public holiday dates in a separate column within your workbook, as you will need to reference these dates to exclude them from the working day calculation.
Use the NETWORKDAYS.INTL Function with a Named Range
The NETWORKDAYS.INTL function calculates the number of working days between two dates, allowing you to exclude specific weekends and custom holidays.
Because the NETWORKDAYS.INTL function is inclusive (it counts both the start date and the end date), subtracting 1 from the result accurately measures the elapsed working days.
Type your public holidays into a blank column. Select these date cells, click the Name Box located to the left of the formula bar, type 'Holidays', and press Enter.
Select the cell where you want the result to appear and enter: =IF(NETWORKDAYS.INTL(Start_Date_Cell, End_Date_Cell, 1, Holidays)-1<=3, "Yes", "No"). Replace Start_Date_Cell and End_Date_Cell with your actual references (e.g., M66 and O66).
Press Enter to evaluate the formula. Click the cell containing your new result and drag the fill handle (the small square at the bottom-right corner) down to apply the formula to the rest of your data.
Apply an Array Formula Using the LET Function
If you are working with dynamic arrays, you can use the LET function combined with NETWORKDAYS.INTL to evaluate entire columns at once and spill the results automatically.
Calculate Deadlines Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions like NETWORKDAYS.INTL, allowing you to manage deadlines and SLAs effortlessly. It offers seamless compatibility with Microsoft Excel formulas and provides an intuitive interface for data analysis.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your existing file containing the start and end dates.
- 2. Define the Named Range: Select your holiday dates, navigate to the Formula tab, and use the Name Manager to define the range as 'Holidays'.
- 3. Apply the Formula: Use the exact same NETWORKDAYS.INTL formula to accurately calculate your three-day deadlines.
Frequently Asked Questions
Why does NETWORKDAYS.INTL count one day too many for deadlines?
The NETWORKDAYS.INTL function is inherently inclusive, meaning it counts both the starting date and the ending date as full working days. To calculate strictly elapsed time (such as a 3-day SLA after the start date), you need to subtract 1 from the function's result.
How do I change the weekend days in NETWORKDAYS.INTL?
The third argument in the NETWORKDAYS.INTL function defines the weekend parameters. Using '1' sets Saturday and Sunday as the weekend. You can change this number (e.g., '7' for Friday and Saturday) to match your regional workweek requirements.
Can I use NETWORKDAYS instead of NETWORKDAYS.INTL?
Yes, you can use the standard NETWORKDAYS function if your weekend is strictly Saturday and Sunday. However, NETWORKDAYS.INTL is highly recommended because it offers greater flexibility to customize which specific days of the week are considered non-working days.
