Posted by IML on October 04, 2001 3:49 PM

You can turn into time with a formula if you like. Say

=TIME(IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),0),RIGHT(A1,2),0)

and format with [h]:mm or whatever you choose.

Posted by Chris on October 04, 2001 3:55 PM

Works Great. Thanks a bunch!

Posted by Mark W. on October 05, 2001 7:22 AM

Posted by IML on October 05, 2001 8:31 AM

your not accounting for hours versus minutes in that. For example, I assumed 1350 should be 13:50, not 13:30.

Posted by Mark W. on October 05, 2001 9:04 AM

Your right! But, this should take care of that...

=FLOOR(A1,100)/2400+MOD(A1,100)/1440

which uses 2 functions and 3 operators vs.

6 functions and 2 operators.

Posted by Mark W. on October 05, 2001 9:33 AM

Your LEFT(...LEN()) construct alleviates the need

for IF(...LEN()) as shown below...

=TIME(LEFT(A1,LEN(A1)-2)+0,RIGHT(A1,2),0)

which lowers your function/operator count to

4 functions and 2 operators.

Posted by IML on October 05, 2001 9:36 AM

If we were playing poker, I think 2 functions and 3 operators would still beat my three functions and 1 operator. Even if it were a tie, you'd win on number of characters.

=TIME(FLOOR(A1,100)/100,RIGHT(A1,2),0)

It wasn't until I read about 50 or so of your posts saying excel stores time as fractions of a day that I even came close to getting a grasp on it. Thanks...

Posted by IML on October 05, 2001 9:41 AM

I still get a value error with a two digit number. I wasn't sure if that was needed or not

Posted by Mark W. on October 05, 2001 9:49 AM

I thought I could coerce a null string into a numeric

value with +0... I was mistaken, but this works...

=TIME((0&LEFT(A1,LEN(A1)-2))+0,RIGHT(A1,2),0)

with 4 functions and 3 operators. So you end up

trading 2 functions for 1 additional operator.

Posted by IML on October 05, 2001 9:57 AM

I still like stealing from you and using the formula that was burried in my very nice response below:

=TIME(FLOOR(A1,100)/100,RIGHT(A1,2),0)

Posted by Mark W. on October 05, 2001 10:02 AM

I'd say =TIME(FLOOR(A1,100)/100,RIGHT(A1,2),0) is

getting to the point of refinement that it doesn't

make much difference.

I don't know enough about the TIME function to

say what it does internally... It may just perform

the following...

=Arg1/24+Arg2/1440+Arg3/86400

In which case it's really 5 operators disguised

as 1 function.