WPS Spreadsheet: Exclude Weekends and Holidays When the Closing Date Is Blank
Question details
The user needs to calculate elapsed working time while excluding weekends and listed holidays, even when the closing date is blank.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains a received date in D2, an optional closing date in AE2, and holiday dates in AJ2:AJ15. The calculation should use the closing date when available and the current date when it is blank.
- Observed behavior
- A calculation based on TODAY minus the received date does not consistently exclude weekends and holidays when the closing date is empty.
Confirm that D2 and AE2 contain valid date or date-time values, and that AJ2:AJ15 contains real spreadsheet dates rather than text. Check that the holiday range does not include unintended blank or invalid entries.
Use NETWORKDAYS.INTL with TODAY for a blank closing date
Use this formula when the closing date in AE2 may be empty and the calculation must exclude Saturday, Sunday, and the holidays listed in AJ2:AJ15.
The IF function selects AE2 when a closing date exists and TODAY() when AE2 is blank. NETWORKDAYS.INTL then excludes the standard weekend and the dates in the holiday range. The MOD terms preserve the time portions of the start and end values.
In WPS Spreadsheet, select the cell where you want to calculate the elapsed working time.
Enter `=NETWORKDAYS.INTL(D2,IF(AE2="",TODAY(),AE2),1,$AJ$2:$AJ$15)-1-MOD(D2,1)+MOD(IF(AE2="",TODAY(),AE2),1)` in the formula bar, then press Enter.
Open the cells in AJ2:AJ15 and confirm each holiday is formatted as a valid date recognized by WPS Spreadsheet.
If other rows use the same layout, drag the fill handle from the completed result cell to copy the formula while keeping the holiday range fixed.
Check date values and holiday entries before recalculating
Use this check when the result is incorrect, especially if holidays appear to be counted as workdays or the formula returns an error.
Calculate Working Days in WPS Spreadsheet with Flexible Closing Dates
WPS Spreadsheet can calculate working days with NETWORKDAYS.INTL, exclude a custom holiday range, and automatically use TODAY() when a closing date is blank. Its familiar spreadsheet interface makes it easy to enter, verify, and fill the formula across a worksheet.
- 1. Open the spreadsheet: Launch WPS Office, open the workbook, and go to the worksheet containing the received, closing, and holiday date columns.
- 2. Prepare the holiday list: Make sure the holiday dates are entered in AJ2:AJ15 and formatted as valid dates.
- 3. Enter the formula: Select the result cell and enter `=NETWORKDAYS.INTL(D2,IF(AE2="",TODAY(),AE2),1,$AJ$2:$AJ$15)-1-MOD(D2,1)+MOD(IF(AE2="",TODAY(),AE2),1)`.
- 4. Review and copy the result: Press Enter to calculate the workday duration, then drag the fill handle down for additional rows.
Frequently Asked Questions
What happens when AE2 is blank?
The IF portion of the formula uses TODAY() when AE2 equals an empty string, so NETWORKDAYS.INTL calculates working time from D2 through the current date.
Which days are excluded by the formula?
The weekend code `1` excludes Saturday and Sunday. Dates listed in the absolute range `$AJ$2:$AJ$15` are also excluded as holidays.
Why must the holiday range contain valid dates?
NETWORKDAYS.INTL can only match holidays that WPS Spreadsheet recognizes as date values. Dates stored as plain text may not be excluded from the calculation.
Why does the formula include MOD(D2,1) and MOD(IF(AE2="",TODAY(),AE2),1)?
These terms preserve the time portions of the received and ending date-time values while NETWORKDAYS.INTL counts the working days between the dates.
