Convert Text Time to Excel Time

[william.mcseveney]

Hi

I have imported a file to excel that contains data that looks like
time e.g 10:30 however the format of the data is General. How can I
get this data to show as an actual time in excel as I need to perform
further calculations on the time once I get it converted. I just need
it to show as hh:mm

Many thanks

W

 

[Guest]

Hi,

Try this:

Type the number 1 in a blank cell then copy the cell, next select your data
and do a Paste Special/Multiply then format as time.

Hope this helps!
Jean-Guy

 

[David McRitchie]

That would be an incorrect answer, because time
is a fraction of a day. Please read about time in
http://www.mvps.org/dmcritchie/excel/datetime.htm

 

[Guest]

Hi David

It seemed to work for me. I formatted a cell as text then typed in "10:30"
when I did a Paste Special/Multiply I got .4375 which when formatted as time
gave me 10:30.

If I was wrong I appoligize.

Regards!
Jean-Guy

 

[David McRitchie]

Hi Jean-Guy, and William (cc: to William)
Very sorry about that, don't know what I was
thinking of.

Not only is multiplying by 1 with Edit, Special Values
correct but that is the normal way to fix text times
(formatted as General) without macros, a technique
I learned probably from John Walkenbach.

In fact I have macros that use the same method (in datetime.htm).
The TrimALL macro (in join.htm) will also convert
as it is actually causing a reentry to the format that
would be current when reentry takes place -- in other
words for this example as long as it is not specifically
formatted as Text.

I was thinking formatted as Text even though the
posting specifically indicated General. And had
that been the case, the only extra step needed would
be to format the column as "General" first then
do the special multiply.

Hi David

It seemed to work for me. I formatted a cell as text then typed in "10:30"
when I did a Paste Special/Multiply I got .4375 which when formatted as time
gave me 10:30.

If I was wrong I appoligize.

Regards!
Jean-Guy

:
[Oops -- it was the correct answer]

That would be an incorrect answer, because time
is a fraction of a day. Please read about time in
http://www.mvps.org/dmcritchie/excel/datetime.htm

 

[David McRitchie]

Actually one minor suggestion
Instead of copying a cell with number 1
in it, selecting the range to convert,
and using Edit, Paste Special Multiply

use
Copy an empty cell
then select the range to convert,
Edit, Paste Special Add
the difference is that empty cells do not
become 0 but remain empty.