View previous topic :: View next topic 
Author 
Message 
JohnMatt Newbie
Joined: 18 Apr 2010 Posts: 4

Posted: Sun Apr 18, 2010 1:17 pm Post subject: Converting MM:SS to decimal (SOLVED) 


I'm copying from the following webbased 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 


JohnMatt Newbie
Joined: 18 Apr 2010 Posts: 4

Posted: Sun Apr 18, 2010 1:42 pm Post subject: 


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 semicolons. 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 


JohnMatt Newbie
Joined: 18 Apr 2010 Posts: 4

Posted: Sun Apr 18, 2010 1:56 pm Post subject: 


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 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

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 18991230 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 




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
