OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Converting MM:SS to decimal (SOLVED)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
JohnMatt
Newbie
Newbie


Joined: 18 Apr 2010
Posts: 4

PostPosted: Sun Apr 18, 2010 1:17 pm    Post subject: Converting MM:SS to decimal (SOLVED) Reply with quote

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]


Last edited by JohnMatt on Sun Apr 18, 2010 1:57 pm; edited 1 time in total
Back to top
View user's profile Send private message
JohnMatt
Newbie
Newbie


Joined: 18 Apr 2010
Posts: 4

PostPosted: Sun Apr 18, 2010 1:42 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
JohnMatt
Newbie
Newbie


Joined: 18 Apr 2010
Posts: 4

PostPosted: Sun Apr 18, 2010 1:56 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Apr 18, 2010 11:21 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group