Time format

[Meb]

I received an excel spreadsheet where data for time was entered in two
columns with a decimal point instead of a colon (e.g the time of twelve
thirty four was entered as 12.34 instead of 12:34). Is there any way I can
insert a formula to convert the data to the correct format so that I can
calculate the time elapsed correctly? Thanks

 

[Bob Phillips]

=--(SUBSTITUTE(E1,".",":"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Niek Otten]

Do a Find and Replace (CTRL-H) replacing all . with :

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I received an excel spreadsheet where data for time was entered in two
| columns with a decimal point instead of a colon (e.g the time of twelve
| thirty four was entered as 12.34 instead of 12:34). Is there any way I can
| insert a formula to convert the data to the correct format so that I can
| calculate the time elapsed correctly? Thanks

 

[Meb]

Thank you for the very prompt and perfect solution. You just saved me 3 hours
work!

 

[Meb]

I followed your advice but got a number of consistent errors with the zeros.
If the original time was stated as 11.00 the revised time was stated to be
00:00. If the original time was stated to be 14.10 the revised time was
stated to be 14:01 not 14:10. However, if the original was stated to 12.18
the revised was correctly stated to be 12:18.
Is this a problem with the Excel programming?

 

[David Biddulph]

Your problem is that the number stored in the cell isn't necessarily what
you think it is from what is displayed.
11.00 will be stored as 11
14.10 will be stored as 14.1

You'll see better what's going on if you format as General.

To use your Find and Replace you may first need to change to a consistent
pattern by using =TEXT(A2,"00.00").
You might as well then do the replace in the same formula, so
=SUBSTITUTE(TEXT(A2,"00.00"),".",":") would give you text, or
=--SUBSTITUTE(TEXT(A2,"00.00"),".",":") would give you a time if you format
the cell appropriately.