COUNTIFS not recognising time

Do you mean?

=COUNTIFS('Data Dump'!E:E,">&00:50:00",'Data Dump'!B:B,B2)

Select Entire E Column in Data Dump Sheet
Select Text to Columns under Data
Select Delimited in Convert Text to Column Wizard
Then select Finish (Don't go with next button)

with Fotis 1991 formula

Chelsea Long, No Times Over Breaks showing as 11 but as per data it is 9 only

that's worked perfectly

Are you sure?


The problem is that the times in the breaks column (E) are not actually times, they are text values that look like times. You can check this out by changing the format to General ... the displayed value doesn't change; it should display a decimal value. You can change them to time values using a Helper column and adding zero (0).

See the attached file.

The formula should be:

Formula:

=COUNTIFS('Data Dump'!E:E,">" & TIMEVALUE("00:50:00"),'Data Dump'!B:B,B2)

after converting the times.


Regards, TMS

....also there's nothing wrong with the original formula

=COUNTIFS('Data Dump'!E:E,">00:50:00",'Data Dump'!B:B,B2)

You should get correct results with that as long as you convert the data in column E to real time values using methods suggested by TMS or nflsales

And, just to prove DLL's point, the formula:

Formula:

=COUNTIFS('Data Dump'!L:L,">00:50:00",'Data Dump'!B:B,B2)

has been adjusted to refer to column L which has the converted times ... and it does work perfectly.

I have to say, I think I prefer the TIMEVALUE function approach as, for me, it is clearer what is intended. No big deal though. Not sure which, if either, is more efficient but, in relatively small volumes I would guess it doesn't make a lot of difference.

Regards, TMS

Originally Posted by TMS

I have to say, I think I prefer the TIMEVALUE function approach as, for me, it is clearer what is intended.

I agree, Trevor. It's even more important with dates where ">5/3/2005" could refer to 5th March or 3rd of May depending on your region, but ">"&DATE(2005,3,5) is unambiguous and universal.

I was referencing the original formula here, not because that version is preferable but to re-inforce the point that the fault here is caused by the data, not the formula.

btw, Fotis' suggested formula is equivalent to this:

=COUNTIFS('Data Dump'!E:E,"<>",'Data Dump'!B:B,B2)

i.e. it's counting any non-blank value in column E (where the name also matches in B), regardless of the actual time value (assuming times in original text format)

Even that(i think) it's obvious i have to say that i had a quick look and of course i didn't noticed the format of the time column. That's why i had this suggestion and i didn't looked forward as OP reply was that everything was ok. My bad!!

@DLL: thanks for the feedback and also explaining how the results from Fotis's formula are calculated. I guessed it was a string comparison but couldn't quite put my finger on how it worked.

Regards, TMS

The formula

Formula:

=E2+0

copied down will convert the values to "proper times".

If you want, I can provide a short VBA subroutine that will convert them all for you.

Regards, TMS