Time Format or Time conversion

  • Thread starter Thread starter Res
  • Start date Start date
R

Res

Good Morning everyone,

I have a csv I imprted to Excel2000 and have a column of times like
1:05 PM. I went in to change the format to be like hh:mm:ss which
should give me 13:05:00. If you look at the column it does look like
that but if I click on a cell it shows 1:05 PM. How do i change this
so I get just the hh:mm:ss? Thanks for any help
 
I presume you have already tried formatting as time? If not, try it. If you
have the value is a text and requires conversion to time. There is a trick
to do this without complex formula. Simply multiply the value by 1. You can
either do this in an adjacent cell (=A1*1) or 'paste special' multiply.

After you do this, you should see a fraction as the result (1.05 PM becomes
0.545138888888889). Format the result as time and you should see what you
need.

This site tells you how paste special multiply :
http://www.j-walk.com/ss/excel/usertips/tip059.htm

Instead of 1.05 used in the example, use 1
 
You could change your windows regional settings to display HH:mm:ss, but that
affects all windows programs--including windows explorer.

But my recommendation is to not look at the formula bar!
 
I presume you have already tried formatting as time? If not, try it. If
you have the value is a text and requires conversion to time. There is
a trick to do this without complex formula. Simply multiply the value
by 1. You can either do this in an adjacent cell (=A1*1) or 'paste
special' multiply.

After you do this, you should see a fraction as the result (1.05 PM
becomes 0.545138888888889). Format the result as time and you should
see what you need.

This site tells you how paste special multiply :
http://www.j-walk.com/ss/excel/usertips/tip059.htm

Instead of 1.05 used in the example, use 1
Click to expand...

I did try the formatting including some custom methods. I did the
multiply and it worked! Nice trick. But the column that has the data is
the one that will be imported. I tried a cut and paste special, values,
but I still ended up with 13:05:00 and in the cell 13:05:00 PM. I think
I can move that colums to another sheet, do the multiply and export as
csv.

Thanks for the help.
 
You could change your windows regional settings to display HH:mm:ss, but that
affects all windows programs--including windows explorer.

But my recommendation is to not look at the formula bar!
Click to expand...

Dave,

I'll try that if the calculations don't work. THX!
 
Back
Top