Time is my problem!

[Andymann]

I am busy doing a time in motion study for one of our sites. I need to convert a decimal value of time, say 6.72 minutes, to the value in hours, minutes and seconds (00:06:43.12). I can't seem to get this right, and because I have worksheets linked, I don't want to type in the separately calculated value. I would appreciate any suggestions!!
Thanks

Andymann

 

[Derek]

Andymann
You could try this
If your decimal minutes is in A1
Put this formula in B1
=A1/60/24
Use this custom format on B1
[h]:mm:ss
Hope this helps
Derek
This message was edited by Derek on 2002-02-25 23:46

 

[Tom Urtis]

Just to add to Derek's reply, if you enter the formula =A1/1440 in B1 or wherever, and custom format B1 as hh:mm:ss.00, then this will return 00:06:43.20, close to your result but still a little off though, because my .20 decimal remainder is your .12. Where did you get the .12? I must be missing something obvious at this midnight hour.

Tom Urtis

 

[Derek]

You could also put this formula in B1
But to add up the times you would still have to format your cells [h]:mm:ss
=(INT(INT(A1)/60)&":"&MOD(INT(A1),60)&":"&ROUND(MOD(A1,1)*60,0))+0

regards
Derek

 

[Andymann]

Thanks to Derek and Tom for helping out on that one... Much appreciated - the spreadsheet is working properly, and the Boss is a happier person!