Problem with chaining formulas in a single cell

Question

Level 1

0 points

Problem with chaining formulas in a single cell

I'm trying to get a number formatted as minutes:seconds, so zero would be "00:00", 73 would be "01:13", etc. I haven't found a PAD function, but I tried using NUMTOBASE as a hack. Here's what I tried:

Assume a fractional number of minutes is in cell A1. For discussion, let's say it's 36.5. In cell A2, calculate the #minutes:

=FLOOR(A1,1)

and seconds in cell A3:

=MOD(60*A1,60)

Now, use NUMTOBASE to pad with zeroes (in cell A4 and A5):

=NUMTOBASE(A2,10,2)

=NUMTOBASE(A3,10,2)

Finally, concatenate into a string in cell A6:

=CONCATENATE(A4,":",A5)

So far so good. I have "36:30" in cell A6, exactly what I want. However, I want to do all this at once - say A7 contains:

=CONCATENATE(NUMTOBASE(FLOOR(A1,1),10,2),":",NUMTOBASE(MOD(60*A1,60),10,2))

Here's what appears in A7:

0000000036:0000000030

Where'd all those extra zeroes come from??

Can anyone tell me what I'm doing wrong? Or (better yet) suggest a cleaner way to do this?

Numbers '09-OTHER, OS X Mountain Lion (10.8.5)

Posted on Dec 7, 2013 2:05 PM

Reply

Answer 1

Dec 7, 2013 2:47 PM in response to rightnwrong

try something like this:

enter seconds in column A, then put the formula in B:

B4=DURATION(0,0,0,A4÷60)

All I am doing is using the duration function where I provide the decimal number of minutes (second divided by 60). The duration function takes arguments like this:

duration(<weeks>, <days>, <hours>, <minutes>, <seconds>, <milliseconds>)

I set weeks, days, and hours to zero

Reply

Answer 2

rightnwrong Author

Level 1

0 points

Dec 7, 2013 3:04 PM in response to Wayne Contello

Thanks Wayne... that helps.

It's not quite perfect, but it will do for my purposes. I ended up putting this in my formula:

=DURATION(,,,FLOOR(ABS(60*A1),1)/60)

because some of my numbers have fractional seconds, and I don't want to see the milliseconds field. Even with that, occasionally it gives me things like "8m 12s 0ms" instead of "8m 12s", so I guess there's a rounding error there. Also, if the value is exactly zero, I get "1w", which isn't ideal.

But again, it's good enough for what I'm doing. Thanks again!

Reply

Answer 3

Dec 7, 2013 3:07 PM in response to rightnwrong

Rightnwrong,

generally the better the problem statement the better the solution. If you want an integer number of seconds then modify the formula to be:

B4=DURATION(0,0,0,int(A4)÷60)

Reply