Problem trying to see if time falls between 2 times

[sharky12345]

I'm using this formula to count the number of instances when a time value falls between 00:00 and 06:00;

=COUNTIF(Deployments!$B$2:$B$998,"<="& 6/24) - COUNTIF(Deployments!$B$2:$B$998,"<="& 0/24)

The range B2:B998 is formatted as Custom hh:mm but the formula result is giving me zero, which is not correct.

Can anyone spot where I am going wrong please?

 

[wideboydixon]

Perhaps your times also have a date component. Click on one of the time cells, press [Ctrl]+1 to format the cell and select "General". What value shows there?

WBD

 

[sharky12345]

Hi, and thanks - result is as per the time value, no change.

 

[wideboydixon]

Quick test on some random data show that the formula should work (although you don't need the second COUNTIF):

Book1
	B	C
2	00:00	7
3	01:00
4	02:00
5	03:00
6	04:00
7	05:00
8	06:00
9	07:00
10	08:00
11	09:00
Deployments

Cell Formulas
Range	Formula
C2	=COUNTIF(Deployments!$B$2:$B$998,"<="&TIME(6,0,0))

WBD

 

[sharky12345]

Thanks, it's now working - I copied the time data and pasted it back and for some reason it's solved the problem so must have been something to do with the way the time data was originally entered. Thanks!!