How do I display time with fractional seconds?

[roscoe]

I need to display time in a cell in 24hr format to the nearest 10th of a second, i.e. 23:21:07.3. The data came to me as number of milliseconds in the day. I have no problem calculating the hrs, minutes, and seconds, and displaying in 24hr is easy...but how do I get the ".3" part to display? My input to the Time() function includes it so I assume it's there.

 

[delaneyjm]

The format you want is hh:mm:ss.000

 

[roscoe]

I just realized that instead of my crazy formula, I could just convert the milliseconds to seconds (/1000), then convert to fraction of a day (/24/60/60) and format as a time and voila. Problem is that it rounds the seconds up so at 5.5 seconds it's displaying "6". Even more imperative to display the ".5" part of the time.

Can it be done?

Thanks!

 

[roscoe]

The format you want is hh:mm:ss.000

Nope, instead of showing "7.3" it's showing "7.000". I tried ### instead of 000 and that was an illegal format so...

 

[mikerickson]

The "pass through millisecond format" is a good way to do this.

But for general information, the custom format .000 will return a time in seconds with decimal seconds, such that half of 1 hour, 3 seconds will be shown as 1801.500.

 

[roscoe]

Nevermind...it out that the purpose I needed it can accept in in text format, so that was trivial to create

 

[gap1234]

I need to display time in a cell in 24hr format to the nearest 10th of a second, i.e. 23:21:07.3. The data came to me as number of milliseconds in the day. I have no problem calculating the hrs, minutes, and seconds, and displaying in 24hr is easy...but how do I get the ".3" part to display? My input to the Time() function includes it so I assume it's there.

Assuming that you want to display a duration in cell J2 with fractional seconds and you have the duration in terms of seconds since the beginning. You use

=TIME(TRUNC(J2/3600,0),TRUNC((J2-TRUNC(J2/3600,0)*3600)/60,0),(J2-TRUNC(J2/60,0)*60))+(J2-TRUNC(J2,0))/(24*3600)

The +(J2-TRUNC(J2,0))/(24*3600) adds the fractions of a second.

In general the time function seems to ignore any fraction of seconds. So you have to add it back in

time(h,m,s)+fraction of second per day.