Problem with duration formula in Google Sheets

1
votes

I'm having a bit of a problem with this formula:

=COUNTIFS(H13:H17, ">="&A30, H13:H17, "<"&A31)

I'm trying to count the number of Duration occurrences, from 0 to 30 min in 1 min intervals. But here's the problem I run into. If I use it on the cell where I just put the time by myself like 0:01:15 (HH:MM:SS), then it works just fine.

But the problem is that I don't want to count durations manually. And in another line, I have two timestamps and just use a simple formula to get the difference ( duration). And that's where the problem starts. If I use the same formula on the cell where the duration is calculated automatically, I get different results.

Any recommendations on how to fix it?

2
google-sheets
can you add a screen shot of a sample of the data including column headers and cell letters? – tomb
I posted a sample sheet as the answer below. But here it is again. [link]docs.google.com/spreadsheets/d/… – Nacho Libre

2 Answers

2
votes

You are experiencing rounding inconsistencies in the representation of time (why I do not recommend the Duration format). However, if I understand your requirement (I am not sure I do) then in C2 and copied down to suit:

=ROUND(B2-A2,15)

and in E2 and copied down to suit:

=round((row()-2)/(60*24),15)

may provide you the automation you seek.

-1
votes

format all times as TIME and all durations as DURATION by clicking on 123 button