Turning 1400 into 14:00, 380 times.
Posted by Chris on October 04, 2001 3:41 PM
I have a column of data that is supposed to be time. The problem is, when the data was entered it was entered without the : in the middle. So now when I try to format the cells to time hh:mm it just gives me a bunch of zeros. Can someone tell me how to make a macro that turns all of my time data without the : into data with a : in the middle, so I can then format the cells as time?
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
Even simplier...
=A1/2400
Posted by IML on October 05, 2001 8:31 AM
but...
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
Also...
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
Very nice
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
Re: Also...
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
Correction...
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
Re: Correction...
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
Oooow...
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.
