MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.