How to Count the Number of Days an Employee Worked in a Month in Excel
Question details
The user needs an Excel formula to count the unique number of workdays for a specific employee within a given month.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating employee attendance or payroll where multiple daily entries exist for the same employee.
- Observed behavior
- Standard counting formulas artificially inflate the workday count by including multiple records on the same day, requiring a method to extract unique dates first.
Ensure your dataset is organized with clear, separate columns for employee names and dates. If your date column contains timestamps, you may need to format or truncate them to extract just the date portion to ensure accurate unique counting.
Use FILTER, UNIQUE, and COUNT Functions
Combine these dynamic array functions to extract unique dates for a specific employee and count them accurately.
This method is ideal for modern spreadsheet software supporting dynamic array formulas. The FILTER function isolates records for the target employee, UNIQUE removes any duplicate dates, and COUNT tallies the final number of unique days.
Click on an empty cell where you want the final workday count to appear.
Input the formula: =COUNT(UNIQUE(FILTER(Report_Date_Range, Surveyor_Range="Employee Name")))
Replace 'Report_Date_Range' with the actual column range containing your dates (for example, B2:B100).
Replace 'Surveyor_Range' with the column containing employee names (e.g., A2:A100) and change 'Employee Name' to the actual employee's name or a cell reference. Press Enter to calculate the result.
Calculate Employee Workdays Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and UNIQUE, allowing you to handle complex employee attendance and payroll calculations with ease.
- 1. Open your file: Launch WPS Spreadsheet and open your employee attendance tracking file.
- 2. Select a cell: Click on the blank cell where you want to display the unique workday count.
- 3. Enter the formula: Input the =COUNT(UNIQUE(FILTER(...))) formula using your specific cell ranges.
- 4. Calculate the result: Press Enter to instantly calculate the unique days worked for the employee.
Frequently Asked Questions
What if my version of Excel doesn't support FILTER and UNIQUE?
If you are using an older version of Excel, you can use a combination of SUMPRODUCT and COUNTIFS to count unique values with conditions, or use a Pivot Table to group dates by employee and count the unique days.
How can I exclude weekends and holidays from this count?
To exclude weekends and holidays, you should use the NETWORKDAYS function. If you have duplicate dates, you may need to first extract the unique dates using the method above, and then evaluate those dates using NETWORKDAYS.
Why does the formula return a #CALC! error?
The #CALC! error usually occurs if the FILTER function doesn't find any matching records for the specified employee name. You can use the built-in [if_empty] argument in the FILTER function to return a blank or 0 instead.
Can I reference a cell instead of typing the employee's name in the formula?
Yes, you can replace the hardcoded name with a cell reference like D2. This makes it easier to drag the formula down a column to calculate workdays for a list of multiple employees.
