Concatenate with Round in Numbers - bug

Question

Concatenate with Round in Numbers - bug

I am trying to display a time from a number of seconds.

Let's say 140 seconds. I want to display 2:20.5 (2 minutes, 20.5 seconds)

Let's say my number is in A1

ROUNDDOWN(A1÷60,0)

Result: 2

-

CONCATENATE(ROUNDDOWN(A1÷60,0),":",MOD(A1,60))

Result: 2.0:20.5

This is obviously not the expected behavior.

-

If I change the formatting of the source cell to two decimal places:

Result: 2.00:20.50

If I add "FIXED":

CONCATENATE(FIXED(ROUNDDOWN(A1÷60,0)),":",MOD(A1,60))

Result: 2.00:20.50

It seems to be something with concatenate where it ignores rounding and follows the formatting of a source cell.

Excel does this correctly.

Posted on Dec 8, 2023 4:24 PM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

22,249 points

Dec 8, 2023 4:50 PM in response to holesinthinair

holesinthinair wrote:

I am trying to display a time from a number of seconds.

Let's say 140 seconds. I want to display 2:20.5 (2 minutes, 20.5 seconds)

I do not understand. 140 seconds equals 2 minutes, 20 seconds, not 2 minutes, 20.5 seconds.

What is the actual value in your cell A1? It seems to be some value that displays as 140, but is in fact approximate.

With A1 selected, look at the bottom left of your Numbers window. What do you see?

Try this formula, and reply with your result:

INT(B2÷60)&":"&MOD(B2,60)

Regards,

Ian.

Reply

Answer 2

Yellowbox

Level 7

22,249 points

Dec 8, 2023 6:29 PM in response to Yellowbox

Hi again holesinthinair,

Try the DURATION function

More information here: DURATION – Apple Support (AU)

Regards,

Ian.

Reply

Answer 3

Yellowbox

Level 7

22,249 points

Dec 8, 2023 5:58 PM in response to holesinthinair

Hi again holesinthinair,

OK, testing 140.5 seconds.

Bottom left of my Numbers window with cell B2 selected shows the value as Actual 140.5

What do you see in your Numbers document?

Now the formula and result in C2:

Just wondering, did you import your document from Excel to Numbers? 🤔

Regards,

Ian.

Reply

Answer 4

Yellowbox

Level 7

22,249 points

Dec 8, 2023 7:27 PM in response to holesinthinair

Thanks for your reply!

Further hint, one way to format as Duration:

Happy Numbering!

Ian.

Reply

Answer 5

Dec 8, 2023 4:54 PM in response to Yellowbox

Hi, sorry, I am not able to go back and edit but I meant 140.5 seconds. That is the actual value.

INT function formula you provided gives the same result: 2.0:20.5

Reply

Answer 6

Dec 8, 2023 7:18 PM in response to Yellowbox

Duration function: that works perfectly! Thank you! And it makes handling of leading zeroes better (e.g. if the time were 128 seconds)

BTW no, not imported from Excel.

Reply

Concatenate with Round in Numbers - bug

Similar questions