Custom Time Format doesn't work for me
 

I have a time value residing in a database with a format of hhmm:ss.sss (e.g.
1240:30.010) which I have imported into an excel spreadsheet. I want to
convert this to a time value so I can do arithmetic oprations on it but I
can't convert it properly.

I entered a custom format of [BLUE]hhmm:ss.000;@ and applied it to an empty
cell.
when I enter 1240:30.010 I get a blue 2040:30.010 which represents the 1240
minutes in hour minute format. what am I doing wrong?



--
czechguy

On Sat, 12 Feb 2005 16:57:02 -0800, "chuck"
wrote:

I have a time value residing in a database with a format of hhmm:ss.sss (e.g.
1240:30.010) which I have imported into an excel spreadsheet. I want to
convert this to a time value so I can do arithmetic oprations on it but I
can't convert it properly.

I entered a custom format of [BLUE]hhmm:ss.000;@ and applied it to an empty
cell.
when I enter 1240:30.010 I get a blue 2040:30.010 which represents the 1240
minutes in hour minute format. what am I doing wrong?

Your incorrect assumption is that formatting in Excel affects how data is
input. It does not. It only affects how data is displayed.

One possible solution: If you could input your data as text, you could then
use a formula to convert it. For example, with your data in A1,

=VALUE(LEFT(A1,2)&":"&RIGHT(A1,9))

would convert it to the proper time.


--ron