Show fractions of seconds

[scambigol]

I have a formula which, for simplicity, says: =time(1,23,34.12)
How do I set the cell format so that the hundreths of seconds are shown? If I set mm:ss all I get is 23:34 but I miss out the .12 information
thanks in advance
scambigol

 

[Richard Schollar]

hh:mm:ss.00

 

[scambigol]

that doesn't work - it just displays 1:23:34.00 ignoring the .12
any thoughts?

 

[Richard Schollar]

Can you provide your full formula because it does work for me.

 

[scambigol]

here it is
=TIME(2,3,4.56)
thanks

 

[dcardno]

I get a similar result. If I enter 1:32:15.015 in a cell formated as "hh:mm:ss.000" it will display up to three decimal places in the seconds value (ie, thousandths of a second). If I enter =time(1,32,15.015) in the same cell, it displays "1:32:15.000" - it seems that the time function may return only integer seconds. When I add ten cells that contain the formula =time(1,1,1.15) the result of 10:10:10.000 indicates that the time values are indeed truncated to integer seconds.

Interestingly, while the original value entered displays as "1:32:15.015" - in the formula bar the value is truncated to "1:32:15 AM" - and the decimal seconds have to be re-entered if the cell is edited, and then they are displayed but again lost on the formula bar. When these are added, though, the fractional parts are retained - ten cells with 1:01:01.15 entered sum to 10:10:11.500...

Note - all on XL2003; I am not sure how other versions might handle this issue

 

[Richard Schollar]

Scambigol

Sorry for the confusion - despite you having clearly stated in your first post you were using TIME() function, I didn't appreciate (or test in my given solution) that this would impact my suggested solution. Dean looks to be absolutely right re TIME truncating the seconds values passed to it. I think you may have to look for an alternative solution not involving TIME() if you want to display decimal fractions of seconds.

 

[scambigol]

alright, thanks a lot - guess it was rather unexpected that the TIME() function would create a problem :D
I've tried things like =TIME(1;1;1)/2 and that does work, so I guess that's the way I'll go about it
thanks a lot
scambigol