Custom Time Format doesn't work for me

C

chuck

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?
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
Top