Converting MM:SS to decimal (SOLVED)

JohnMatt · Newbie Joined: 18 Apr 2010 Posts: 4

I'm copying from the following web-based chart and pasting in Calc:

http://www.nhl.com/ice/playerstats.htm?fetchKey=20102ALLSASAll&sort=player.bioFirstNameLastName&viewName=summary

The TOI/G column is meant to represent time in MM:SS, but Calc interprets it as HH:MM.

Ideally, I'd like to convert the time to a decimal (so, 10:30 would become 10.5), and that would work much better for me. I'm open to other alternatives though. If it has to stay as a time, that's okay, but I'd prefer a decimal value.

-edit-

Cancel that, I need it to be represented as a value. Ultimately I'm copying over to Google Docs and putting all the data into a Gadget that only accepts strings and numbers.[/b]

JohnMatt · Newbie Joined: 18 Apr 2010 Posts: 4

Okay, I think through searching I've figured most of it out. So far I have this:

=HOUR(A1)+(MINUTE(A1)/60)

Which works, except for values above 23 (the evaluation of HOUR(24) is 0, HOUR(25) is 1, etc.

So, I tried the following, but I get an error 508. Can't seem to find the problem. Help?

=IF(HOUR(R30)>23,=24+HOUR(R30)+(MINUTE(R30)/60),=HOUR(R30)+(MINUTE(R30)/60))

-edit-

Okay, so there are multiple problems.

First, all the commas should be semi-colons. Fixed that.
Second, the equal signs shouldn't be there. Fixed that.
The main problem however is that HOUR() won't produce a value over 23 anyway (if it did I wouldn't have the problem in the first place). Using R30> 23 doesn't appear to work either. Suggestions?

JohnMatt · Newbie Joined: 18 Apr 2010 Posts: 4

Aaaand never mind. Realized I need A1>1, because it's comparing in terms of days, not hours.

=IF(R30>1;24+HOUR(R30)+(MINUTE(R30)/60);HOUR(R30)+(MINUTE(R30)/60))

properly evaluates 1:30 as 1.5 and 24:30 as 24.5.

I should've just kept fiddling around for a little longer Smile

Villeroy · Posted: Sun Apr 18, 2010 11:21 pm Post subject:

All dates are integer day numbers, all times are fractions of these day numbers as you can see when you apply a decimal format. The other way round every number can be formatted as a point on a time scale with 1899-12-30 00:00 as point zero.
06:00:00 in decimal format is 0.25 (on quarter of a day from point zero)
The unit of that scale is "Days", so you just need to multiply by 24 and set a decimal format in order to get hours as integer.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org