How to Count Active Employees Between Start and Termination Dates
Question details
The user needs to calculate the number of active employees for a given statistical month based on their start and termination dates, taking into account currently active employees who do not have a termination date.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating active employee headcount for HR or administrative reporting using specific monthly date ranges.
- Observed behavior
- Requires an array or logic formula to evaluate if the statistical month falls on or after the start date and before the termination date, while intelligently treating empty termination date cells as still active.
Ensure your employee start and termination dates are formatted as proper date values (not plain text), and verify that your target statistical month reference is a valid date (typically the last day of the target month).
Use SUMPRODUCT and MIN Formula to Count Active Employees
This method uses the SUMPRODUCT function combined with the MIN function to handle both standard date ranges and blank termination dates (active employees) in a single formula.
The SUMPRODUCT function evaluates multiple criteria arrays simultaneously. To handle employees who haven't left the company, the formula substitutes any blank or ongoing termination date with a far-future date (like December 31, 9999). This guarantees that active employees are correctly factored into the monthly headcount.
Identify the cell ranges for your employee start dates (e.g., A2:A100) and termination dates (e.g., B2:B100), as well as the cell containing your statistical target date (e.g., E1).
Select an empty cell where you want the total count to appear and input the formula: =SUMPRODUCT((Start_Date<=Stat_Month)*(MIN(DATE(9999,12,31),Termination_Date)>Stat_Month))
Update 'Start_Date', 'Termination_Date', and 'Stat_Month' with your actual cell references. For example: =SUMPRODUCT((A2:A100<=E1)*(MIN(DATE(9999,12,31),B2:B100)>E1))
Press the Enter key to execute the formula. The cell will now display the total number of active employees for that specific month.
Use Combine COUNTIFS for a Non-Array Approach
If you prefer avoiding SUMPRODUCT or array mechanics, you can add two COUNTIFS formulas together to account for both terminated and actively employed staff.
Easily Calculate Employee Headcount in WPS Spreadsheet
WPS Spreadsheet fully supports complex array formulas like SUMPRODUCT and COUNTIFS, making HR calculations, date evaluations, and headcount reporting highly efficient.
- 1. Open your HR roster: Launch WPS Spreadsheet and open your employee dataset containing the start and termination dates.
- 2. Input the calculation formula: Click on the cell where you want the headcount displayed and paste the provided SUMPRODUCT or COUNTIFS formula.
- 3. Save and share: Save your document in .xlsx format to ensure full compatibility with team members using Microsoft Excel.
Frequently Asked Questions
Why does my SUMPRODUCT formula return a #VALUE! error?
This error typically occurs if your 'Start_Date' and 'Termination_Date' ranges are not exactly the same size. Ensure both ranges cover the exact same number of rows (e.g., A2:A100 and B2:B100).
How exactly should I define 'Stat_Month' in the formula?
'Stat_Month' should refer to a cell containing the specific date you are checking against. For monthly statistics, this is typically the last day of the month you are analyzing, entered as a standard date (e.g., 10/31/2023).
Can I use these formulas if my dates are stored as text?
No, spreadsheet formulas require dates to be stored as actual date serial numbers to perform mathematical comparisons. You can use the 'Text to Columns' feature or the DATEVALUE function to convert text dates into proper date formats before calculating.
