How to Fix Excel COUNTIF Not Counting Midnight or 12:30 AM Values
Question details
The user is unable to get accurate counts using the COUNTIF function for specific time values, specifically midnight (12:00 AM) and 12:30 AM.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Trying to count the occurrences of specific time values (midnight and 12:30 AM) within a dataset using the COUNTIF function.
- Observed behavior
- The COUNTIF formula returns 0 or an incorrect count because Excel fails to match the criteria with the cell contents due to hidden date values, text formatting, or decimal precision differences.
Select your time data range and ensure the cells are formatted as Time or Custom Time rather than Text by checking the Number Format dropdown in the Home tab.
Use the TIME Function in COUNTIF Criteria
Wrapping the target time in the TIME function ensures Excel accurately compares the decimal time value rather than evaluating text strings.
Because Excel stores time as a fractional portion of a 24-hour day, comparing text strings like "12:00 AM" often fails. The TIME function standardizes this by generating the exact decimal value Excel expects.
Click on the empty cell where you want to display the count result for your midnight values.
Type the formula =COUNTIF($L$2:$L$3002, TIME(0,0,0)) and press Enter. Be sure to replace $L$2:$L$3002 with your actual data range.
To count 12:30 AM values, select another cell, type the formula =COUNTIF($L$2:$L$3002, TIME(0,30,0)), and press Enter.

Remove Hidden Date Components Using the INT Function
If cells contain both date and time but only display the time, COUNTIF will fail because the underlying value includes the integer date. Extracting just the time resolves this.
Convert Text-Formatted Times to Real Time Values
Sometimes exported data stores times as text, which COUNTIF cannot match against numeric time criteria. Converting the text to true numbers fixes the discrepancy.
Easily Analyze Time Data with WPS Spreadsheet
WPS Spreadsheet offers full compatibility with Excel formulas, including COUNTIF and TIME functions, making it a perfect tool for handling complex data and time calculations effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the time data.
- 2. Select the result cell: Click on an empty cell where you wish to calculate your time occurrences.
- 3. Input the formula: Type the formula =COUNTIF(A2:A100, TIME(0,0,0)) to count midnight values.
- 4. Get the result: Press Enter to instantly get your accurate count results without format errors.

Frequently Asked Questions
Why does Excel store time as decimal numbers?
Excel represents dates as sequential serial numbers starting from January 1, 1900, and times as fractional portions of a 24-hour day. For example, 12:00 PM is 0.5, and 12:00 AM (midnight) is 0.0 or 1.0 depending on whether a date component is attached.
Can I use text strings like "12:00 AM" directly in COUNTIF?
While using strings like =COUNTIF(A1:A10, "12:00 AM") might work occasionally, it is highly prone to errors due to your computer's regional settings and hidden cell formatting. Using the =TIME(0,0,0) function is far more reliable.
How do I know if my time cells have hidden date values?
Select a cell containing a time and look at the Formula Bar at the top. Even if the cell on the sheet simply displays "12:30 AM", the Formula Bar will show the full date and time (e.g., "1/15/2023 12:30:00 AM") if a hidden date component is present.
What should I do if my COUNTIF result is still 0 after using the TIME function?
If the result is still 0, the issue is likely that your times are stored as text rather than numbers. You can fix this by using the 'Text to Columns' feature in the Data tab to convert the text strings back into true numeric time values.




