change time in excel 08.11.15 to 08:11:15

October 23, 2011 at 07:10:51
Specs: Windows 7
Have a huge excel sheet with thousands of times from an Ironman competiton. All times and split times have been written in the format 08.11.15. I need this converted to the 08:11:15 format so that I can then convert this to seconds and continue with statistics. I have thought of using decimals also, however, then one loses the second, which is not good in this case. Anyone out there who can help. The 08.11.15 format is in the "general" format and have already tried to see if I could get the numbers to change by changing the formatting. Does not work. Thanks.

See More: change time in excel 08.11.15 to 08:11:15

Report •


#1
October 23, 2011 at 18:35:40
If your data is supposed to be 8 hours 11 minutes 15 seconds
Try this:

With your data in Cell A1, in cell B1 enter the formula:

=TIME(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))

MIKE

http://www.skeptic.com/


Report •

#2
October 24, 2011 at 13:55:32
Hi Mike,
Thanks. My wish was to transfer the hh:mm:ss to all seconds. I did have the formular for this and yesterday I figured out to copy paste the columns into word, find and replace all . with : , copy paste again back into excel and voila my formula worked and I got seconds. Thanks for taking the time to answer though.
Hans

Report •

#3
October 24, 2011 at 14:15:16
I figured out to copy paste the columns into word, find and replace all . with : , copy paste again back into excel

You could have saved yourself a Copy/Paste by doing the find and replace all . with : in Excel.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 24, 2011 at 14:44:39
He he, so true, that was pretty silly. Thanks again. Hans

Report •

Ask Question