How to Count Calendar Days Between First and Last Working Days in WPS Spreadsheets
Question details
Calculate the inclusive calendar-day span from the first working day to the last working day in a WPS Spreadsheets range, even when non-working days such as Tuesday create gaps.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a weekly schedule where working days are marked 1 and non-working days are marked 0, and needs to know the calendar distance between the first and last working positions.
- Observed behavior
- Simple consecutive-workday counts return an incorrect span when the schedule includes gaps, such as Monday and Wednesday with Tuesday off.
Open the workbook in WPS Spreadsheets and confirm that your working-day indicators are in a single contiguous range, using 1 for worked days and 0 for non-worked days.
Use LET and XMATCH to return the inclusive first-to-last working-day span
Best for current WPS Spreadsheets versions that support LET and XMATCH; it finds the first and last 1 in one readable formula.
This method calculates the position of the first and last working-day marker in the range, then subtracts and adds 1 to include both endpoints. It counts calendar days, not only the working days.
Click an empty cell where you want the calendar-day span to appear.
Type =LET(work,C2:C8,first,XMATCH(1,work,0),last,XMATCH(1,work,0,-1),last-first+1) and replace C2:C8 with your actual range.
Press Enter. WPS Spreadsheets returns 3 when Monday and Wednesday are marked 1 and Tuesday is marked 0.
Find the first and last working positions with MATCH and INDEX for compatibility
Use this approach if your WPS Spreadsheets version does not support LET or XMATCH, or if you prefer classic functions.
Calculate Workday Calendar Spans Directly in WPS Spreadsheets
WPS Spreadsheets gives you the same Excel-compatible formulas you need to find the first and last working day positions, subtract them, and return the inclusive calendar span—without extra paid tools.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains your daily work indicators.
- 2. Select the result cell: Click the cell where you want the calendar-day total to appear.
- 3. Enter the calculation formula: Type =LET(work,C2:C8,first,XMATCH(1,work,0),last,XMATCH(1,work,0,-1),last-first+1) with your actual range, or use the MATCH and INDEX method.
- 4. Press Enter and review the result: Press Enter to display the inclusive span from the first to the last working day.
- 5. Save the file as .xlsx: Click Menu > Save As and choose Excel Workbook (.xlsx) to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the formula return 3 for Monday and Wednesday?
Because it finds the first 1 at Monday and the last 1 at Wednesday, then calculates last-first+1. The inclusive span includes Monday, Tuesday, and Wednesday, so the result is 3.
Can I count only the working days inside the same span?
No, this formula counts calendar days from the first to the last working day, including gaps. To count only working days or exclude weekends, use COUNTIF, NETWORKDAYS, or WORKDAY depending on your data.
What should I do if my WPS Spreadsheets version does not support XMATCH?
Use classic functions: =MATCH(1,C2:C8,0) for the first position and =MAX(INDEX((C2:C8<>0)*(ROW(C2:C8)-ROW(C2)+1),)) for the last position, then subtract and add 1.
Can this method work across multiple weeks?
Yes. Build one continuous date or indicator range for all weeks and point the formula at that range. It returns the inclusive calendar-day span from the earliest to the latest working marker.
How do I avoid an error when no day is marked as working?
Wrap the formula with IFERROR, for example =IFERROR(LET(work,C2:C8,first,XMATCH(1,work,0),last,XMATCH(1,work,0,-1),last-first+1),0), so the result is 0 instead of an error.
