7

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.

Improve this question

4 Answers 4

Reset to default
14

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.

Improve this answer
4
  • 12
    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!
    – Matt Ellen
    Commented Aug 13 at 11:19
  • 2
    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.
    – Thomas Weller
    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.
    – JimmyJames
    Commented Aug 15 at 17:09
4

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.

Improve this answer
1
  • 3
    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.
    – Luke Sawczak
    Commented Aug 13 at 18:17
2

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))
Improve this answer
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)?

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .