[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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

 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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
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 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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