Convert devimal to number format

R

Richard

I am sent a spreadsheet that lists performances in a time
trial. The times are entered in Excel as decimal ie. 23
mins 20 secs is entered as 23.20. I need analyse the
times but in order to do so I have to convert the decimal
to time format. At the moment I am having to do this by
overwriting the time as 00:23:20. Is there an easier way?
Regards
Richard
 
A

AlfD

Hi!


=(60*VALUE(LEFT(A1,LEN(A1)-3))+VALUE(RIGHT(A1,2)))/24/60/60

will turn 23.20 (which is presumably text) in A1 into 23:20 behaving a
minutes and seconds. The cell with the result should have custom numbe
format mm:ss.

If you want to know why, come back;)

Al
 
R

Ron Rosenfeld

I am sent a spreadsheet that lists performances in a time
trial. The times are entered in Excel as decimal ie. 23
mins 20 secs is entered as 23.20. I need analyse the
times but in order to do so I have to convert the decimal
to time format. At the moment I am having to do this by
overwriting the time as 00:23:20. Is there an easier way?
Regards
Richard
Click to expand...

If you have the Analysis Tool Pack installed, you can use the formula:

=DOLLARDE(A1,60)/1440 and format the cell as [h]:mm:ss.000 or similar

If you don't have the ATP installed, then:

=(INT(A1)+MOD(A1,1)*100/60)/1440


--ron
 
G

Guest

-----Original Message-----
Hi!


=(60*VALUE(LEFT(A1,LEN(A1)-3))+VALUE(RIGHT (A1,2)))/24/60/60

will turn 23.20 (which is presumably text) in A1 into 23:20 behaving as
minutes and seconds. The cell with the result should have custom number
format mm:ss.

If you want to know why, come back;)

Alf
Click to expand...

The problem I have is when the time ends with a 0 or 00.
Then the RIGHT function does not recognise the actual
last two figures. For example 26.10 becomes 02:00 using
your formula. This is in fact why I have raised this
issue as I have created a similar formula to yourself.
Thanks
RIchard
 
R

Richard

-----Original Message-----
I am sent a spreadsheet that lists performances in a time
trial. The times are entered in Excel as decimal ie. 23
mins 20 secs is entered as 23.20. I need analyse the
times but in order to do so I have to convert the decimal
to time format. At the moment I am having to do this by
overwriting the time as 00:23:20. Is there an easier way?
Regards
Richard
Click to expand...

If you have the Analysis Tool Pack installed, you can use the formula:

=DOLLARDE(A1,60)/1440 and format the cell as [h]:mm:ss.000 or similar

If you don't have the ATP installed, then:

=(INT(A1)+MOD(A1,1)*100/60)/1440


--ron
Click to expand...

Yes the second one has worked. Thanks very much
Richard
 
Top