Spread sheet help please.

[RoadKingLarry]

I'm looking for a little help on an OpenOffice spreadsheet.
I'm self taught on Excel and I keep stuff pretty simple most of the time. I'm using OpenOffice and I'm not able to duplicate what I can do with Excel at work.
And, I'm not really able to make excel do exactly what I want. I only have Open Office at home where this will be played with.

This is just an self imposed exercise I'm playing with, no real need but I kind of like playing with this stuff and I'm thinking I just don't know enough to make it work.

Here is the problem:

Working with elapsed times -  hh:mm:ss.
Excel wants to make it a date/time format and OpenOffice wants to use hh:mm.ss (tenths of seconds)

The formula I want to apply is for corrected time for handicapped sailboat racing - CT=(ET*100)/HC where CT and ET are in hh:mm:ss format and HC is the handicap ie: 89.1.
My issue is getting OpenOffice to play with time in the hh:mm:ss format
I want to be able to show CT and also be able to show +/- differences in times between different laps and also between different boats.
Not really looking for anything other than a nudge in the right direction here.

[Doggy Daddy]

Are you able to enter [h]:mm:ss as a custom number format?  Yes, with the brackets around the hours.

[Brad Johnson]

What about using individual cells for h, m, and s? Display the full time in a seperate cell by drawing data from the discrete cells. Since you're populating the cell via formula rather than direct entry you could insert custom characters between the value fields in your formula. This would also give you the option of adding a field for tenths, should you need it.

You'd have to do some if/then functions to account for field excess (i.e. if minutes are in ecxess of 60 then add 1 to the hour field and display only the excess minutes in the minute field). It would be extra work but the discrete data fields give expansion flexibility and some future-proofing to the dataset. Or you could just calculate it manually and then enter the data.

Brad

[MechAg94]

Wish I could help.  Our company is trying to move away from MS products to Google products.  Google Sheets is nearly useless to me.  The live sharing is the only feature that it seems to be good at. 

[RoadKingLarry]

OK, a couple of things to try. Thanks.

[birdman]

My best quick though would be make the Hn:mm:SS cell be a text format, then make a hidden colum that uses the various text2num or text2date type tools on de-concatenated subsets of the text to make it an excel date number, then apply your formulas to that.