Why is Excel not counting time with COUNTIF?

I have a table like the following:

A B C
123 24/05/2024 09:42:54
123 24/05/2024 09:42:55 00:00:01
123 24/05/2024 09:42:56 00:00:01
123 24/05/2024 09:42:57 00:00:01

Where C is like `=b2-b1`. The sheet has over 100,000 rows.

What I want to do is count all the entries in C where the value is `00:00:01`, but when I try `COUNTIF(c1:c200000, "00:00:01")` or `COUNTIF(c1:c200000, TIMEVALUE("00:00:01"))` or `COUNTIF(c1:c200000, TIME(0,0,1))` I get 0.00, when I can easily see the value must be greater than one.

How should I be counting the time difference?

If it's important I'm using Excel for Microsoft 365.

I changed the calculation in column C from `=b2-b1` to `=SECOND(b2-b1)` and changed the COUNTIF to `=COUNTIF(c2:c200000, 1)`, which showed me a value I found more plausible.

• Correct! Your original approaches all suffer from a rounding error: the value in C is actually `0,00001157408` but `TIMEVALUE("00:00:01")` is `0,00001157407`. Good enough for most purposes, but comparing two floating point values with `=` is always tricky. Best to include a rounding function (like `SECOND()`)
– Paul
Commented Aug 13 at 10:19
• @Paul I see! That's good to know. I was hoping C would be some kind of datetime value rather than a number. Today I learned! Commented Aug 13 at 11:19
• Datetime is just a formatting of a number. You can format both, B and C to a number and figure out what that number is. Commented Aug 13 at 19:33
• The way time is represented in excel is that days 1 and time increments smaller than a day is a fraction of a day, e.g., 0.25 is 6 hours. One of the ways I've found to deal with this is to use countif or Countifs like so: `=countifs(C:C, ">="&A1, C:C, "<"&A2)` where column A is a list of time increments (in order) that I want to count by. Commented Aug 15 at 17:09

A trick for more precision would be to convert the difference into seconds,
i.e. `=(A1-INT(A1)*86400`

Dates in spreadsheets are an integer count of days from a base date, with the time of date stored as an additional fraction part.

One day consist of 24x60x60 seconds (= 86400).

So entering `=1/86400` in a cell and then formatting it as `HH:MM:SS` will display the value as `00:00:01`.

Further: Adding up a sum of seconds (any number of them) and then dividing it by 86400, will allow to display it as a "days and HH:mm:SS" time by entering
`=INT(A1) & " days " & TEXT(A1-INT(A1)," HH:MM:SS")`
in a cell.

• Nice. And good grief — of all the dumb ways we programmers have come up with to represent time, a floating point fraction of a day has got to be one of the dumbest. Commented Aug 13 at 18:17

Another method would be to round the results of your calculation in Column C to the nearest second:

``````=MROUND(B2-B1,TIME(0,0,1))
``````

Thinking laterally, if every cell in C is either 0 or 1 second, then instead of counting the 1s, why not just use `SUM(C)`?