How to Calculate Average Attendance by Date, Time, and Weekday in WPS Office Spreadsheet
Question details
The user needs to calculate guest attendance across multiple dates and times, including overnight stays, and then average the resulting attendance counts by weekday and time.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has guest check-in and check-out records and wants attendance counts for specific date-time points, then averages by weekday and time.
- Observed behavior
- A working method is needed to count a guest as present when check-in is earlier than or equal to the target point and check-out is later than or equal to it, and to summarize those counts.
Before writing formulas, confirm that the check-in and check-out columns contain real date-time values, not text, and add a helper column for each target date-time point you want to analyze.
Use COUNTIFS and a PivotTable to average attendance by weekday and time
Use this method when you have check-in and check-out ranges and want to produce reliable attendance counts for each date-time point and then average them by weekday and time.
This method treats each target date and time as the comparison point. A guest is counted only when the target point falls between the check-in and check-out values, so overnight stays are handled correctly when the cells contain full date-time serial numbers.
In an empty column, enter each target date and time, such as 1/1/2025 8:00 AM, and format the cells as date-time.
Next to each target point, enter =TEXT(A2,"dddd") to return the weekday name.
In the next column, enter =COUNTIFS(CheckIn,"<="&A2,CheckOut,">="&A2), replacing CheckIn and CheckOut with your actual cell ranges.
Select the date-time, weekday, and attendance count columns, go to Insert > PivotTable, place Weekday and Time in Rows, and set Attendance Count to Average.
Use AVERAGEIFS on helper columns for weekday and time averages
Use this when you already have attendance counts per date-time and need a formula-based average instead of a PivotTable.
Calculate average attendance by date, time, and weekday in WPS Office Spreadsheet
Use WPS Office Spreadsheet to build COUNTIFS formulas, add weekday helper columns, and create a PivotTable that averages attendance by weekday and time. It opens and saves Microsoft Excel files, so existing attendance workbooks can be edited without conversion.
- 1. Open the attendance workbook: Launch WPS Office Spreadsheet and open the Excel workbook that contains the CheckIn and CheckOut columns.
- 2. Build the COUNTIFS formula: Click the first attendance count cell and enter =COUNTIFS(CheckIn,"<="&A2,CheckOut,">="&A2), using your actual date-time point and ranges.
- 3. Add weekday and PivotTable summaries: Add =TEXT(A2,"dddd") beside the date-time points, select the resulting table, and go to Insert > PivotTable to average attendance by weekday and time.
- 4. Save in Excel format: Go to Menu > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Why does my COUNTIFS attendance formula return 0?
This usually means the check-in or check-out cells are text, not real date-time values. Select the cells and check the Number Format; if necessary re-enter dates and times so they become numeric date-time serial numbers.
How do I handle attendance that crosses midnight?
Use full date-time values in both check-in and check-out cells. The COUNTIFS formula compares the target point with both columns, so a guest checked in before midnight and out after midnight is counted on both dates.
Can I average attendance by weekday without a PivotTable?
Yes. Add helper columns for weekday and time, then use AVERAGEIFS with the weekday and time criteria to average the attendance count column.
Can WPS Office open my existing Excel attendance file?
Yes. WPS Office Spreadsheet opens and saves .xlsx workbooks, supports COUNTIFS and PivotTables, and preserves existing formulas and formatting.
