Count instances that happen within time range

[Abarency]

Okay I have a workbook with multiple sheets. On each sheet is a list of service calls. The time a service call is received is formated as General ex "02:05:00". I want to count how many times a service call is late between hours of the day ex 00:00:00 - 01:00:00 , 01:00:00 - 02:00:00 and so on. There is a column next to the service call received time that will have "Late" in it if the call was late. I have tried a few different formulas but just can't get it to count them.

This is the most recent formula i have tried (obviously i would have to add the "Late" criteria but even without that it wont count).

=COUNTIFS(CAO!$X$21:$X$500,">="&TIME(2,0,0),CAO!$X$21:$X$500,"<"&TIME(3,0,0))

[Tony Valko]

Are the times true Excel time values?

If the times are in this range CAO!$X$21:$X$500, what result do you get from this formula:

=COUNT(CAO!$X$21:$X$500)

That formula will only count the true Excel time values.

[Abarency]

This shows "0". The data is actually imported from a crystal report that has been exported to excel and then copy-paste special then values and number formats.

I changed the format to "time" and still counted "0".

[Abarency]

I changed the format of one of the cells then entered the cell and pressed enter. This then caused the cell to be counted. Am i going to have to enter each cell for the change to take effect?

[Tony Valko]

Sometimes this will convert TEXT numbers to numeric numbers...

Select the range of cells in question
Goto the Data tab>Text to Columns>Click Finish

[Abarency]

Perfect! Thank you very much!

[Tony Valko]

You're welcome!