Hi, I am working on a spead sheet that in D colum has 76.50 this represents
76 seconds and .50 100's of a second
I am tring to come up wit a formula that will convert this to 1:06.50 in the
next colum E.
Colum D has appox. 68 cells all with various times in them like 143.89 again
this ia seconds and iooth of a second which should convert to 2:23.89
2min:23sec.and .89 one hunderths of a sec. Im stumped :)

Thanks PaulS56

Assume your data starts in row 2, so put this in E2:

=D2/60/60/24

and then apply a custom format to the cell of:

[m]:ss.00

Then copy the formula down as required.

Hope this helps.

Pete

pauls56;297293 Wrote:
Hi, I am working on a spead sheet that in D colum has 76.50 this
represents
76 seconds and .50 100's of a second
I am tring to come up wit a formula that will convert this to 1:06.50
in the
next colum E.
Colum D has appox. 68 cells all with various times in them like 143.89
again
this ia seconds and iooth of a second which should convert to 2:23.89
2min:23sec.and .89 one hunderths of a sec. Im stumped :)

Thanks PaulS56


--
Pete_UK
------------------------------------------------------------------------
Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=83096

One way:
=TEXT(INT(D1)*(1/(24*3600)),"p:mm")&"."&TEXT(MOD(D1,1)*100,"@")
Note! It's a string, not a number!

Regards,
Stefi

€žpauls56€� ezt Ã*rta:

Hi, I am working on a spead sheet that in D colum has 76.50 this represents
76 seconds and .50 100's of a second
I am tring to come up wit a formula that will convert this to 1:06.50 in the
next colum E.
Colum D has appox. 68 cells all with various times in them like 143.89 again
this ia seconds and iooth of a second which should convert to 2:23.89
2min:23sec.and .89 one hunderths of a sec. Im stumped :)

Thanks PaulS56

thanks Stefi I copied the string and pasted it in the next cell to the right
which would be C60 the 76.50 is in D60. It came up and said Value. IM sure Im
messing it up.
Oh by the way Thank you for helping PaulS56

"Stefi" wrote:

One way:
=TEXT(INT(D1)*(1/(24*3600)),"p:mm")&"."&TEXT(MOD(D1,1)*100,"@")
Note! It's a string, not a number!

Regards,
Stefi

€žpauls56€� ezt Ã*rta:

Hi, I am working on a spead sheet that in D colum has 76.50 this represents
76 seconds and .50 100's of a second
I am tring to come up wit a formula that will convert this to 1:06.50 in the
next colum E.
Colum D has appox. 68 cells all with various times in them like 143.89 again
this ia seconds and iooth of a second which should convert to 2:23.89
2min:23sec.and .89 one hunderths of a sec. Im stumped :)

Thanks PaulS56

Thanks Pete It worked Like a dream you guys are GREAT (you'all) saved me
weeks of converting those cells on paper then, typing them into the cells
"Pete_UK" wrote:


Assume your data starts in row 2, so put this in E2:

=D2/60/60/24

and then apply a custom format to the cell of:

[m]:ss.00

Then copy the formula down as required.

Hope this helps.

Pete

pauls56;297293 Wrote:
Hi, I am working on a spead sheet that in D colum has 76.50 this
represents
76 seconds and .50 100's of a second
I am tring to come up wit a formula that will convert this to 1:06.50
in the
next colum E.
Colum D has appox. 68 cells all with various times in them like 143.89
again
this ia seconds and iooth of a second which should convert to 2:23.89
2min:23sec.and .89 one hunderths of a sec. Im stumped :)

Thanks PaulS56


--
Pete_UK
------------------------------------------------------------------------
Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=83096