Tenths of seconds in 'Sheet'

[RallyCoDriver]

This is my first post - but first thanks to all NetBook/Epoc enthusiasts who contribute to this forum as I have learnt a lot from you all!

I use Excel to collate timing data for motorsport events, which are accurate to tenths of a second. In Excel it is possible to create your own formats, eg; [ hh:mm:ss.0 ]. Is it possible to do the same with Sheet as I cannot edit the built-in formats?

Phil

[Jim Cooke]

Phil,

Not that I can think of offhand. The time format is fixed at hh:mm:ss. You could use a serial number that would calculate down to the tenth of a second but I don't know of any way to show the result in time format.

[cshandley]

Sheet stores all times & dates as a floating point number, so it is quite capable of remembering times down to 10ths of a second... The problem is that it won't DISPLAY them.

I suspect that it would be quite easy to display the 10ths of a second in an adjacent cell, with a bit of trickery. Would that be sufficient? Please PM me if it would be, and I'll see what I can come up with.

[cshandley]

Here's the reply I sent in reply to Phil's PM, which may be useful to other people:

Hello Phil,

Here's how I would do it:

Column B formatted as 'hh:mm' (without AM/PM bit).
Column C formatted as 'Fixed, 1 d.p.' and Left Aligned.
Column D formatted as 'hh:mm:ss', although it can be made zero width if you want to hide it.
D1 would contain B1 + C1/86400 , with it copied to every cell in column D.

This means you enter hours & minutes in the B column, while seconds (and tenths of seconds) are entered in the C column. Any reading of the times should be from the D column, which contains the full & accurate time values.

So lets say you sum the D column, so D10 contains SUM(D1:D9) . To display the sum, you would have B10 & C10 formatted the same as the rest of the B & C columns.

B10 would contain TRUNC(D10*3600)/3600
C10 would contain (D10-B10)*86400

I think that gives you a very nicely formatted way for entering & displaying times down to 10ths of a second. If you want, you can have whole dates (not just times) in the B column - provided you still don't display any seconds.


BTW, if you were wondering the value 1.0 represents 24 hours, so 1/24 represents 1 hour, 1/(24*60) represents 1 minute, and 1/(24*60*60) represents one second.

Regards,
Chris Handley