WPS Spreadsheet Formula to Calculate an End Date Excluding Weekends and Holidays
Question details
The user needs a formula that calculates a future end date and time from a starting date, working-hour duration, weekends, and a holiday list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A project or task starts at a date and time, has a duration in working hours, uses an eight-hour workday, and must skip weekends and listed holidays.
- Observed behavior
- The required end date must be calculated automatically while excluding weekends and the holidays in a specified cell range.
Place the starting date and time in A1, enter the duration in working hours in B1, and list excluded holiday dates in H1:H10. Confirm that the start date and holiday cells are recognized as valid dates rather than text.
Use WORKDAY to calculate the end date from working hours
Use this formula when the schedule is based on an eight-hour workday and the holiday dates are stored in H1:H10.
This approach converts the working-hour duration into full eight-hour workdays and remaining hours. WORKDAY skips Saturdays, Sundays, and every date in the holiday range.
For exact start and finish times, adjust the calculation to match the workday start time, end time, and any breaks in the schedule.
In WPS Spreadsheet, enter the starting date or date and time in A1 and the total working-hour duration in B1. For example, B1 can contain 16 for sixteen working hours.
Enter each holiday as a valid date in cells H1:H10. Leave unused cells blank and expand the range if more holidays are required.
Select the result cell and enter =WORKDAY(A1,INT((B1+7)/8),H1:H10)+MOD(B1,8)/24, then press Enter. The formula adds full working days, converts remaining hours into a fraction of a day, and excludes weekends and the listed holidays.
Select the result cell, open the Home tab, choose the number format controls, and apply a Date, Time, or custom date-and-time format so the calculated end value displays correctly.
Adapt the formula for a precise daily schedule
Use a schedule-specific formula when work begins and ends at fixed times or includes breaks that cannot be represented by a simple eight-hour duration.
Calculate Working-Hour End Dates in WPS Spreadsheet
WPS Spreadsheet provides WORKDAY and related date functions for calculating end dates from working hours while excluding weekends and holiday ranges. It also supports Excel-compatible formulas, making it easy to open, edit, and share existing workbooks.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open an existing workbook or create a blank worksheet.
- 2. Prepare the input cells: Enter the start date and time in A1, the working-hour duration in B1, and the holiday dates in H1:H10.
- 3. Insert the formula: Select an empty result cell and enter =WORKDAY(A1,INT((B1+7)/8),H1:H10)+MOD(B1,8)/24, then press Enter.
- 4. Format and verify the result: Use the Home tab's number-format controls to display the result as a date and time, then test it with weekend and holiday dates.
Frequently Asked Questions
Which cells should contain the start date, duration, and holidays?
Enter the starting date or date and time in A1, the duration in working hours in B1, and the holiday dates in H1:H10. Change H1:H10 if your holiday list uses another range.
How does the formula exclude weekends and holidays?
The WORKDAY function skips Saturdays and Sundays automatically and also skips valid dates included in the H1:H10 holiday range.
Why does the result show a number instead of a date?
Spreadsheet dates are stored as serial numbers. Select the result cell, open the Home tab, and apply a Date, Time, or custom date-and-time number format.
Can this formula account for a lunch break or fixed work hours?
The basic formula assumes an eight-hour workday. For exact schedules, revise it to use the net hours per day and account for the daily start time, finish time, and breaks.
Can I use the formula with an Excel workbook in WPS Spreadsheet?
Yes. WPS Spreadsheet supports Excel-compatible WORKDAY formulas and can open and save common Microsoft Excel .xlsx and .xls files.
