ISNUMBER acting odd

[MiniFav]

Could some one explain why cell W3 is counted as a number, yet W5 is not?
both are formatted to [h]:mm
I potentially get data ranging from 00:00 to 9999999:59, but ISNUMBER seems to stop working at 9999:59.

Any help with this?

Aircraft AD template Remake V13.xlsx
	W
3	9600:45
4	a
5	10000:00
(REPEAT) - REPEAT

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
text	NO
W2:W1000	Expression	=IF($W2="","",IF(ISNUMBER($W2),FALSE,TRUE))	text	NO

 

[Fluff]

There is nothing wrong with ISNUMBER, when any time value greater than or equal 10000:00:00 is manually entered into a cell it will be treated as text & not a number.

 

[MiniFav]

There is nothing wrong with ISNUMBER, when any time value greater than or equal 10000:00:00 is manually entered into a cell it will be treated as text & not a number.

Is there anyway around this?

I just need to highlight anything which falls outside of 00:00 - 9999999:59.

 

[Fluff]

Is there anyway around this?

Not that I can think of off hand.

 

[Peter_SSs]

Would this suffice?
Note that currently it does not highlight row 8 which is outside your range but I thought that your initial post may have meant you would not get a value like that anyway?
Also, there are other values that would not get picked up by this formula (eg 12DEC:23) due to the nature of ISNUMBER.
To get a more robust check you would possibly need to use vba.

22 06 16.xlsm
	A
1	150:00:00
2	10000:00
3	9999:59:00
4	10000:00
5	a
6	15:12:22
7	9999999:59
8	9999999999:11
9	12:12:23:33
10	12:12xxx
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=NOT(OR(ISNUMBER(A1),AND(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,ISNUMBER(LEFT(A1,FIND(":",A1)-1)+0),ISNUMBER(REPLACE(A1,1,FIND(":",A1),"")+0))))	text	NO

 

[MiniFav]

Would this suffice?
Note that currently it does not highlight row 8 which is outside your range but I thought that your initial post may have meant you would not get a value like that anyway?
Also, there are other values that would not get picked up by this formula (eg 12DEC:23) due to the nature of ISNUMBER.
To get a more robust check you would possibly need to use vba.

22 06 16.xlsm
	A
1	150:00:00
2	10000:00
3	9999:59:00
4	10000:00
5	a
6	15:12:22
7	9999999:59
8	9999999999:11
9	12:12:23:33
10	12:12xxx
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=NOT(OR(ISNUMBER(A1),AND(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,ISNUMBER(LEFT(A1,FIND(":",A1)-1)+0),ISNUMBER(REPLACE(A1,1,FIND(":",A1),"")+0))))	text	NO

That works great, Thank you.

 

[Peter_SSs]

You're welcome. Glad to help.