How to Fix COUNTIFS Missing Excel Time Values with Hidden Seconds
Question details
The COUNTIFS function fails to count certain time values because the source data contains seconds that are visually hidden by the cell's number format.

- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Counting data records falling within specific start and end times using the COUNTIFS formula.
- Observed behavior
- The COUNTIFS formula misses certain records because hidden fractional seconds in the time data prevent exact or boundary matches from calculating correctly.
Verify that the time values in your data range are stored as genuine spreadsheet time serial numbers, not as text strings. You can confirm this by temporarily changing the cell format to 'General' to see if the time converts into a decimal number.
Expand Time Criteria to Include Hidden Seconds
Adjust your COUNTIFS formula boundaries using the TIME function to account for potential hidden seconds without altering the source data.
Spreadsheet software stores time as a fractional value of a 24-hour day. If a cell format is set to display only hours and minutes (e.g., 12:30), underlying seconds (e.g., 12:30:45) are visually hidden but still calculated by formulas. To capture these values, adjust your condition boundaries slightly to include any hidden seconds.
Locate the cells containing your target start time and end time (for example, Between!F2 and Between!G2).
Click on the cell where you want the total count result to be displayed.
Type the formula adjusting the bounds by subtracting 1 second from the start time and adding 59 seconds to the end time: =COUNTIFS(Database!$D:$D,">="&Between!F2-TIME(0,0,1),Database!$D:$D,"<="&Between!G2+TIME(0,0,59))
Press Enter. The formula will now correctly count all data points, including those with unformatted seconds.

Reveal Seconds and Round Source Data
Expose hidden seconds through cell formatting and round the source times to the nearest minute so exact COUNTIFS matching works flawlessly.
Calculate Time Formulas Flawlessly in WPS Spreadsheet
WPS Spreadsheet offers high precision and full compatibility with complex time functions, ensuring your COUNTIFS formulas calculate accurately without missing hidden values.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your time tracking data.
- 2. Inspect time formatting: Select your time data and use the intuitive Format Cells menu (Ctrl+1) to inspect and reveal any hidden seconds.
- 3. Apply complex formulas: Apply your COUNTIFS and TIME adjustments exactly as you would in Excel.
- 4. Analyze seamlessly: Enjoy fast, highly accurate calculations across all your complex data references.

Frequently Asked Questions
Why does spreadsheet software hide seconds in time values?
Spreadsheets often default to standard time formats like "hh:mm" (hours and minutes) to keep data visually clean and easier to read. However, the underlying data still stores the full time serial number, including seconds, which is what formulas use for calculations.
How can I check if my times are stored as text instead of numbers?
Select the target cells and apply the "General" number format from the Home tab. If the time turns into a decimal number (like 0.5 for 12:00 PM), it is a real time value. If it remains formatted as a time visually, it is stored as text.
Can I use the TRUNC or FLOOR function to remove hidden seconds?
Yes. You can use the FLOOR function in a helper column, such as =FLOOR(A1, "0:01"), to round the time down to the nearest minute, effectively stripping away the seconds so strict minute-level COUNTIFS matching succeeds.




