countif command not recoognizing numbers correctly

[flexeh]

Hi I'm a little confused.

I have a sheet that has a lot of times. There is a column that compares the times and advises the number of minutes between anything less than 5 minutes is a clash with different levels with 00:00:00 being a direct clash.

what I'm trying to do is count the number of times different clashes appear and the amount of minutes.

example - direct clash is 00:00:00 then 00:01:00 then 00:02:00 etc. 00:00:00 works correctly but 00:01:00 brings up 0 even though I know there is entries.

the code I'm using is =COUNTIF(E9:E750,"00:00:00") =COUNTIF(E9:E750,"00:01:00") etc

can anyone advise what I'm doing wrong and why excel doesn't determine the difference. I would attach the file but it says I cannot post attachments, can upload it somewhere if anyone wants to see.

Cheers

 

[Snakehips]

flexeh,

Welcome to Mr Excel.

In your count if formulas you are looking to count where E9:E750 has a text string . e.g. "00:01:00"

Although your times will display in your sheet as "00:01:00" that is likely just because of the cell's format and the underlying date will be a number. (Date Serial)
You will only get away with the string comparison with zero and "00:00:00"

If it is a pure time then it will be a decimal fraction of 24 hours.

So maybe try like below..

Excel 2007
	D	E	F	G	H
3	Minutes	0	1	2	3
4	Count	3	1	2	1
5
6
7
8
9		00:00:00
10		00:01:00
11		00:02:00
12		00:03:00
13		00:00:00
14		00:02:00
15		00:00:00
Sheet7

Cell Formulas
Range	Formula
E4	=COUNTIF($E$9:$E$750,E3/24/60)

If by any chance this doesn't work it could be that your 'times' are actually date & time in which case you will need a revised formula.

Hope that helps.

 

[flexeh]

Hi Snake

Thanks for the quick reply and your welcome. No that hasnt worked, it does for 0 again but the others no. It will be difficult changing all the data as its pulled from a different source. Would there be a work around if the formula has to change? a couple of cells changing all the data rather than having to go through the whole lot.

is there anyway to attach a sheet to the post?

 

[Snakehips]

You cannot attach a file via the forum but if necessary you can upload to a share site such as DropBox and post the link.

Before you do that can you just check what format you have in the 'time' cells.
Format a sample cell as Number and see if it changes the cell data to a number.
If so what is that number?

 

[flexeh]

08:00:00 (8am) becomes 0.333333 as a number. will upload now.

update - Link https://www.dropbox.com/s/bphy3pnwgk25rwu/counttime.xlsx?dl=0

 

[Snakehips]

Ok. The problem is that the subtractions that compute the Gap yield a very small rounding error at about the 16th or so decimal place.
So other than when zero, the gap values will never equate to the comparators in B4:B7.

You can get round this as per below by imposing ROUNDING of say 10 digits on all your values.

Drag formulas in C3 and D3 down through B7 and D7
I have used ROWS(B$3:B3)-1 to give the value 0 in row 3 , 1 in row 4 etc so that the formula can drag down.
Also e.g. 1 / 60 / 24 = the time serial fraction for one minute.

In E you do not need SUM(D10-D9) just D10-D9 Then I have applied the rounding.

Excel 2007
	A	B	C	D	E
1		Total Departures		726
2		Total Clashes
3		00:00:00		15
4		00:01:00		41
5		00:02:00		20
6		00:03:00		52
7		00:04:00		75
8	Service	Stand		Scheduled Departure Time	Gap
9	530	A		06:32:00
10	530	A		07:04:00	00:32:00
11	530	A		07:24:00	00:20:00
12	530	A		07:30:00	00:06:00
13	530	A		07:39:00	00:09:00
BIL_MF

Cell Formulas
Range	Formula
B3	=ROUND((ROWS(B$3:B3)-1)/60/24,10)
D3	=COUNTIF($E$9:$E$750,B3)
E10	=ROUND(D10-D9,10)

Hope that sorts it.

 

[flexeh]

You sir are a true gent! first glance it looks like its working. problem with my template is there maybe additions to the list so will always have to change the amount for example 750 today might be 820 next week. At least with your commands and help its easily managed now. just going to do the conditional formatting to change the colours for each minute.

 

[Snakehips]

Pleased to have helped. Thanks for the feedback.

If your sensible maximum rows of data is 1000 the maybe change the formula in E10 to..

=IF(A10 = "","",ROUND(D10-D9,10))

and drag it down to row 1000.

The clash totals will then ignore any rows beyond where you have data in column A.