Data in ten-thousandths of seconds--Looking for best practice

[hz79hc]

Looking for best approach to doing various analyses of data, some of which is pretty extreme time measurements--at least extreme compared to what I'm used to working with... Some is in what I'll call ss0.ssss format and some is in hhh:mm:ss.ssss format from a non-Excel source of course. I use Excel 365 on Mac but also have on Win10, using "slow Insider" on Mac.

The best the built-in time data type is hhh:mm:ss.sss but want the most reliable approach I can find. Thanks for suggestions!

 

[DRSteele]

Try as I might, I can't seem to make Excel go beyond milliseconds to format a cell.

This workaround will at least display the data in ten-thousandths or whatever you like, even though it is merely text and no longer a number. It is driven by the Base10 exponent.

Notice a strange thing occurs when the value itself contains a decimal, as in the yellow rows; I think it's an Excel calculation engine error.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D
6	value	unit	exponent (Base10)	hh:mm:ss.{fraction}
7	28856219	ten-thousandth	-4	00:48:05.6219
8	6945632	thousandth	-3	01:55:45.632
9	89547	hundredth	-2	00:14:55.47
10	89547.001	hundredth	-2	00:14:55.47.0010000000038
11	60	seconds	0	00:01:00.0
12	60.02	seconds	0	00:01:00.0.0200000000000031
13	658	hundred	2	18:16:40.0
Sheet22

Cell Formulas
Range	Formula
D7	=CONCATENATE(TEXT(INT(A7*10^C7)/86400,"hh:mm:ss"),".",A7-(INT(A7*10^C7)/10^C7))

[/FONT]