Formatting cells to display a length of time, not time of day

**fogdarcy** · 02-06-2012, 11:31 AM

Hi,

I am trying to copy the results of a race from a word doc into excel.

In the word doc they appear like this

16.59
17.22
17.31
18.12 etc

I want them to look like this
0:16:59
0:17:22
0:17:31
0:18:12
I'm running into lots of problems and it keeps thinking that im entering a time of day instead of a length of time.

Any Advise would be great, Thanks

**kinseld5** · 02-06-2012, 11:44 AM

fogdarcy,
Best thing to do would be to copy all those time, 16.58, 17.52..... into column A and put a formula in column B to change the values in column A to what you desire. See below code;

Please Login or Register  to view this content.

This will change the times appropriately. Just copy this into B1 and autofill.

Daithi

**Domski** · 02-06-2012, 11:47 AM

Assuming the numbers once you paste them are decimal values rather than time serial values you will need to convert them first using a formula like =A1/1440 and then format the results as a custom format h:mm:ss

Dom

**Domski** · 02-06-2012, 11:49 AM

An alternative to using a formula to convert from a decimal value to time serials would be to pop 1440 in a spare cell, select your decimal values and then paste special...divide.

Dom

**OnErrorGoto0** · 02-06-2012, 11:51 AM

It would help to know what problems you are running into. If you are trying to add the times, format the sum cell using [h]:mm:ss to get total time displayed.

**fogdarcy** · 02-06-2012, 12:16 PM

Thanks guys, that formula worked perfectly.

**Domski** · 02-06-2012, 12:25 PM

Note if you are using Daithi's formula you will return a text string rather than a time value which if you want to do any calculations on will still require converting.

Dom