MINUTES:SECONDS to decimal time, (MINUTES.SECONDS)

Question

Level 1

0 points

MINUTES:SECONDS to decimal time, (MINUTES.SECONDS)

I want to be able to convert "colon time" (28:45) minutes:seconds to "decimal time" (28.75).
I have resorted to a work-around: put minutes in one column, seconds in a second column, convert the minutes to seconds, add the two seconds columns, divide by 60 entered in another column. It works, but for what I am doing it takes several columns of formulas.

I only have 08 at home. I am at an APPLE STORE where I can do this easily using iWork 09.

Is there an easier solution for this using 08?

Thank you,
new2SS
(New To Spread Sheets)

iBook 800, Mac OS X (10.4.11)

Posted on Jun 17, 2009 11:22 AM

Reply

Answer 1

Jun 17, 2009 2:32 PM in response to new2SS

n,

Your MM:SS data will need to be formatted as text since the Time/Date format is looking for HH:MM, or HH:MM:SS. Given that, and assuming that your data is in column B, a formula to do the conversion would be: =LEFT(B, LEN(B)-3)+RIGHT(B, 2)/60

Regards,

Jerry

Reply

Answer 2

new2SS Author

Level 1

0 points

Jun 18, 2009 10:42 AM in response to Jerrold Green1

I thank you! I will look this over and give it a "try" tonight.

ONE MORE QUESTION:

Does EXCEL "accept" a SS (converted from NUMBERS) with this format.
In Other Words, If I make a NUMBERS SS, with HH:MM using your formula, will it be seen as such by EXCEL?
Thanks again,
new2SS

Reply

Answer 3

KOENIG Yvan

Level 8

41,819 points

Jun 18, 2009 12:15 PM in response to new2SS

If you use a legal time format like HH:MM:SS,if cell B2 contains 00:28:45

an efficient soluce would be to insert

=TIMEVALUE(B) 2460
in cell C2

Yvan KOENIG (from FRANCE jeudi 18 juin 2009 21:15:06)

Reply

Answer 4

Jun 18, 2009 1:20 PM in response to new2SS

The formula I suggested will work on either platform.

Jerry

Reply

Answer 5

new2SS Author

Level 1

0 points

Jun 19, 2009 9:13 AM in response to Jerrold Green1

Jerrold Green1, Jerry:
Most excellent posts by you, thank you. Your formula was quite clear and it worked. I formatted an entry column as TEXT, and the formula column (to the right) as NUMBER (to two decimal-places, which is what I need).

I found the formulas: LEFT, LEN, and RIGHT under TEXT, as you said it would be. I have yet to figure out the(math—logic) of the formula!

I could not find this formula in APPLE's NUMBERS08 User Guide. Nor could I find a direction for converting MM:SS to decimal minute(s) in the 09 User Guide.

I had printed the 08 guide right after I had bought the software and had downloaded the 09 guide to study as a preview to buying it.
I do have an "issue" with the software of NUMBERS 08.

Using 08, when formatting a column as Date and Time, one has three options: Date and Time, Time only, or Date only.
But when I formated the column for Time only, a date might appear when I clicked on a cell in that column.
To me, it is like being asked if I want cheese on my Big Mac, I say NO, and I receive the Big Mac with cheese! Why ask, sif you don't listen?
So, if anyone knows what the reason for this (the NUMBERS 08—not cheese on my BIg Mac!), please tell.
Thanks.
new2SS

Reply

Answer 6

Jun 19, 2009 9:45 AM in response to new2SS

In Numbers, Date and Time are a combined value; a time on a particular day. What you see in the format options are offers to display just the date part, just the time part, both, etc. You can't order date without the time, or in your case the burger without the cheese..

Regarding the math in my expression, the Numbers compiler determines that when text amounts to a numeric value it may be used in a math expression. In this case I peel off the minutes and seconds from the text expression and add the minutes and the quotient of seconds over 60.

Jerry

Reply