How to Average Employee Hours Over the Last 365 Days in WPS Office Spreadsheets
Question details
The user needs to calculate an employee's average contract or gross hours over the last 365 days from a table that contains multiple employment records with different hours and effective dates.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with an employee hours table that includes Employee ID, Gross Hours, and Effective Date columns, and wants a formula that returns the correct average for a selected employee and date window.
- Observed behavior
- A simple AVERAGE over the entire hours column would include all employees and all dates, so the result only becomes correct when both the employee ID and the date range are filtered together.
Verify that the Effective Date column contains real date values by selecting the column and checking the Number Format; if dates are left-aligned or imported as text, convert them to dates before writing the formula. Also decide where to place the target date for a rolling 365-day calculation.
Use FILTER and AVERAGE to Calculate a Rolling 365-Day Average in WPS Office Spreadsheets
Use this method when you need an exact average based on the 365 days before a target date, even if the records span multiple calendar years.
This method uses the FILTER function to restrict the hours column to one employee and to the selected date window before AVERAGE calculates the result. Because WPS Office Spreadsheets supports dynamic array formulas such as FILTER, the formula updates when source data changes.
In WPS Office Spreadsheets, open the workbook that contains Table1 and click the cell where the average hours should appear.
Type =AVERAGE(FILTER(Table1[Gross Hours],(Table1[Employee Id]=G4)*(Table1[Effective Date]>=TargetDate-365)*(Table1[Effective Date]<=TargetDate))) and press Enter.
Replace Table1, Gross Hours, Employee Id, G4, and TargetDate with the actual table name, column names, employee ID cell, and target date cell in your workbook.
If employment periods use separate start and end dates, add conditions such as (Table1[Start Date]<=TargetDate)*(Table1[End Date]>=TargetDate-365) inside the FILTER function.
Calculate a Fixed Calendar-Year Average with YEAR Criteria
Use this method when the reporting period is a specific year, such as 2022, rather than a rolling 365-day window.
Calculate Rolling Employee Hour Averages with WPS Office Spreadsheets
WPS Office Spreadsheets handles Excel-compatible FILTER and AVERAGE formulas, so you can create a dynamic rolling 365-day average without paying for Microsoft Office. Open an existing .xlsx workbook, enter the formula, and keep using the same layout, table names, and cell references.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the .xlsx workbook containing the employee hours table.
- 2. Select the result cell: Click an empty cell where the average hours should appear.
- 3. Enter the rolling-date formula: Type =AVERAGE(FILTER(Table1[Gross Hours],(Table1[Employee Id]=G4)*(Table1[Effective Date]>=TargetDate-365)*(Table1[Effective Date]<=TargetDate))) and replace the table, column, and cell references with your workbook values.
- 4. Save the workbook: Click the Save icon or press Ctrl+S to save the calculated results in Excel-compatible .xlsx format.
Frequently Asked Questions
Why does my AVERAGE formula ignore my date filter?
In WPS Office Spreadsheets, date filters only work when the Effective Date column contains real date serial numbers. Select the column, open Format Cells, and set a Date format. If the values were imported as text, use Text to Columns or DATEVALUE to convert them before applying FILTER.
Can I average hours for a specific calendar year instead of a rolling 365 days?
Yes. Use =AVERAGE(FILTER(Table1[Gross Hours],(Table1[Employee Id]=G4)*(YEAR(Table1[Effective Date])=2022))). Change 2022 to the year you need and adjust the table and column names.
How do I handle employees with multiple employment periods and separate end dates?
Add both start and end date conditions to the FILTER expression. For a rolling window, include conditions such as (Table1[Start Date]<=TargetDate) and (Table1[End Date]>=TargetDate-365) so only overlapping employment periods are averaged.
Does WPS Office support the FILTER function like Microsoft Excel?
Yes. WPS Office Spreadsheets supports dynamic array functions including FILTER and AVERAGE. Make sure your WPS Office version is current so the functions are available and recalculation works as expected.
How can I change the 365-day window without rewriting the formula every time?
Put your target date in a dedicated cell, such as H2, and reference that cell in the formula. When you change the target date, the rolling window automatically moves based on the new date.
