converting seconds into minutes

[pauls56]

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

 

[Stefi]

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:

 

[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

 

[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

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

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]

You're welcome, Paul - thanks for feeding back.

Pete

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

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.

- Show quoted text -