Count Employees Working During a Time Interval in Excel
Question details
Count employees whose shifts overlap a specified time interval, using start and end times stored as spreadsheet time values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Employee shift start times are in column C, end times are in column D, and the interval to check is entered in cells F2 and F3.
- Observed behavior
- The desired result is the number of employee shifts that meet the selected overlap criteria, with valid time values and clearly defined boundary rules.
Identify the shift start and end columns, and enter the interval start in F2 and interval end in F3. For time-only calculations, confirm that all shifts and the interval are on the same day; use full dates and times for overnight shifts.
Count Employee Shifts That Overlap a Time Interval
Use COUNTIFS to count employees whose shifts overlap any part of the interval, including shifts that end exactly at the interval start.
This calculation counts overlapping shift records, not the maximum number of employees working simultaneously within the interval. It represents an employee count only when each employee has one shift record in the selected range.
Enter shift start times in C3:C12 and shift end times in D3:D12. In F2, enter the interval start, such as 7:00 PM; in F3, enter the interval end, such as 8:00 PM.
Select an empty result cell and enter =COUNTIFS($C$3:$C$12,"<"&F3,$D$3:$D$12,">="&F2). This counts shifts that start before F3 and end at or after F2. Adjust both shift ranges to cover the same rows in your worksheet.
If an employee whose shift ends exactly at F2 should not count, use =COUNTIFS($C$3:$C$12,"<"&F3,$D$3:$D$12,">"&F2). This version counts only shifts with a positive-duration overlap.
In an empty cell, enter =ISNUMBER(C3), then repeat for D3, F2, and F3. Each valid numeric time should return TRUE. For time-only text entries, use =TIMEVALUE(C3) in a helper column and fill down, then copy the converted results and use Paste Special > Values to replace the original text. Use straight double quotation marks in the COUNTIFS formula.

Count Employees Working at One Exact Time
Use this formula when you need the headcount at a specific moment rather than all shifts overlapping an interval.
Calculate Shift Coverage with WPS Spreadsheet
Use COUNTIFS in WPS Spreadsheet to count shifts that overlap a selected interval or employees working at one exact time. You can open an existing Excel workbook and perform the calculation in its worksheet.
- 1. Open the schedule: Launch WPS Spreadsheet and choose Open to load your Excel schedule, or create a blank workbook and enter shift starts in C3:C12 and ends in D3:D12.
- 2. Enter the interval: Select F2 and enter the interval start, then select F3 and enter the interval end. Use actual time values, or full date-time values for schedules crossing midnight.
- 3. Count overlapping shifts: Select an empty result cell and enter =COUNTIFS($C$3:$C$12,"<"&F3,$D$3:$D$12,">"&F2) to count shifts with a positive-duration overlap.
- 4. Check and save the workbook: Test a few shift rows against the selected interval, then choose Save As and select .xlsx to keep the workbook in Microsoft Excel format.

Frequently Asked Questions
Why does COUNTIFS return zero when the times look correct?
Test the source cells and criteria cells with ISNUMBER, such as =ISNUMBER(C3). A time displayed as text will return FALSE and may not compare correctly. Convert time-only text using TIMEVALUE, and check that the formula uses straight double quotes and matching range sizes.
Should someone finishing at 7:00 PM count at 7:00 PM?
That depends on your scheduling rule. For an exact-time headcount, use end times greater than the target time to exclude employees finishing then: =COUNTIFS($C$3:$C$12,"<="&F2,$D$3:$D$12,">"&F2). Change ">" to ">=" if the ending moment should be included.
Does the interval formula show the maximum number working at the same time?
No. It counts shifts that overlap any part of the interval, even if those employees are not all present together. To find peak staffing, calculate headcounts at shift start times within the interval and at the interval start, then take the maximum of those counts.
How do I count shifts that run past midnight?
Enter full dates and times in columns C and D, using the next day's date for the shift end. Enter full date-time values in F2 and F3 as well. The same COUNTIFS overlap formula then compares the schedule chronologically.
Will an employee with two shift rows be counted twice?
Yes, if both rows meet the criteria. COUNTIFS counts qualifying rows, not unique employees. For a true employee count, use one qualifying record per employee or build a distinct employee list from the matching shifts.




