Formula Errors

How to Fix Excel COUNTIF Not Counting Midnight or 12:30 AM Values

Phi Hung VoPhi Hung Vo Oct 29, 2026 869 views

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.

How to Fix Excel COUNTIF Not Counting Midnight or 12:30 AM Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click on the empty cell where you want to display the count result for your midnight values.

2
Enter the formula for midnight

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.

3
Enter the formula for 12:30 AM

To count 12:30 AM values, select another cell, type the formula =COUNTIF($L$2:$L$3002, TIME(0,30,0)), and press Enter.

Use the TIME Function in COUNTIF Criteria
Formula Tip: Using TIME(hour, minute, second) is the most robust way to avoid syntax and regional format errors when comparing times across different operating systems.

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the time data.
  2. 2. Select the result cell: Click on an empty cell where you wish to calculate your time occurrences.
  3. 3. Input the formula: Type the formula =COUNTIF(A2:A100, TIME(0,0,0)) to count midnight values.
  4. 4. Get the result: Press Enter to instantly get your accurate count results without format errors.
100% compatibility with Microsoft Excel formulas and file formats (.xlsx)Lightweight software with fast processing for large datasetsBuilt-in data formatting tools to quickly identify and fix text-to-number issues
QA img-9

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.