In order to convert time in hours:minutes:seconds.milliseconds (hh:mm:ss.00) in excel, simply set the cell format to “[ss].00″. This is a custom format.
Make sure that you have enough s’s to cover the number of seconds there will be. e.g if the format is [ss].00 and your data is 3 minutes, then although you have 180 seconds, this will only appear as 80.00. If the cell format is [sss].oo you will get 180.00
Took me an age to figure out without the use of muliplication!
Gadzooks.
Over and out!
Tags: excel, minutes, minutes to seconds, mm:ss.00, seconds
This is exactly what I’m looking for except I need to go the other way. I have a timing system that gives me sss.00 and I need mm:ss.00. Any idea how to do this?
Cheers,
doug
If your system automatically produces numbers in the sss.00 format and excel “knows” that these numbers are seconds and milliseconds, then simply format those cells to [mm]:ss.00.
If however, the cell is simply ###.## – just a number, it becomes a little more complex. I’ve had a play around and I have come up with the following formulae which you need to place in the cells in which you wish to display mmm:ss.00 :
“=CONCATENATE(INT(G8/60),IF(MOD(G8,60)<10,":0",":"),FIXED((MOD(G8,60)),2))"
where g8 is the cell with the no. of seconds in it.
There is unfortunately no simple way of doing it, but this will work on any number in seconds eg. 12432.968. It also works around a known excel bug which likes to produce strange numbers when not asked, ie can return 3.999999999 instead of 4.
Hope this helps and let me know if you have any problems with it and I will see what I can do!
Bethaney