Convert a plain number in a cell to minutes and seconds (00:00)

Question

Level 1

8 points

Convert a plain number in a cell to minutes and seconds (00:00)

Hello, I've search everywhere for what I am doing wrong but I can't seem to find anything that helps, so I'm going to ask here.

I have exported some data from iTunes (Music App) and opened it in a Numbers Sheet. In this sheet, column B contains the length of each songs I have exported. The lengths are given by plain numbers and the data format of each cell in column B is set to "Number". For instance, the song in A2 is 2:58 minutes, so B2 displays it as the plain number "178", which obviously is 2:58 in seconds.

So, to make the sheet understand this column contains durations, I changed the data format of column B to "Duration". The issue, is that Numbers assumes 178 represents days, since when I change the data format of column B to "Duration", my B2 cell displays 178 as "25w 3d". And when I try to set the "Custom Units" tab differently by selecting the "min" and "sec"tabs, it just converts "25w 3d" to an enormous number of minutes.

I saw a post about using the TIME function to display it the way I want to, by doing =TIME(0,B2,0). And technically it works, it does display my initial "178" as "2:58", the only problem is that now the sheet considers the data format of this value to be in a "Date & Time" format, and manifestly, just switching the data format to "Duration" doesn't do anything, it doesn't even seem to be registering the change since when I click back on the cell the data format is set back to "Date & Time". This would be fine, but I need to do multiplications with column B, that is why I really need it to be set to a duration format.

This is my first post here and hopefully my English is clear enough, I just thought I'd post here rather than in my country's forum for better visibility. Thanks in advance for any help I'll get! And if I missed the post where this was discussed please point me its way.

Posted on Dec 20, 2020 2:47 PM

Reply

Answer 1

Top-ranking reply

pierreblhp Author

Level 1

8 points

Dec 21, 2020 2:51 AM in response to Barry

Thank you for your answer, perfectly clear.

I've tried your method, and it works perfectly.

But while I was trying it out, I noticed the DURATION formula you used was very similar to the TIME one, so I tried to use it the same way as in my original post, and it worked too. So I'm putting it out there if someone has the same issue in the future.

Add a column after column B. In C2, enter the DURATION formula and replace each category in the formula by 0, except for the the "seconds" one, where you input B2 in its place. It'll look like this:

Then edit the format of column C anyway you want to.

Reply

Answer 2

Yellowbox

Level 7

21,939 points

Dec 20, 2020 10:27 PM in response to pierreblhp

Hello Pierre,

With some extra columns, you can convert a Date & Time to a Duration.

Fill the formulas down the columns.

Formula in C2 =FIND(":",B2)

Formula in D2 =LEFT(B2,C2−1)

Formula in E2 =RIGHT(B2,LEN(B2)−C2)

Formula in F2 =DURATION(,,,D2,E2)

Column F is formatted like this:

You can hide columns C, D and E.

Regards,

Ian.

Reply

Answer 3

pierreblhp Author

Level 1

8 points

Dec 21, 2020 2:32 AM in response to Yellowbox

Perfect, thanks!!! I would never have figured this out!

Reply

Answer 4

Barry

Level 9

60,023 points

Dec 20, 2020 11:19 PM in response to pierreblhp

Hi Pierre,

Here's another conversion that may prove useful.

Format column B as Text.

Copy the values from your source, then click Once) on B2 to select that cell.

Go to the Edit menu and choose Paste and Match Style.

Enter the formula shown below the table in C2, then fill down as many rows as you have entries. Take care to include the three commas* before "LEFT" in the Duration function. These specify that the first 'number' represents minutes.

As written, the formula will return an error flag if there is no data in that row of column B. To prevent that, wrap the formula in this IF statement:

IF(LEN(B2)<4,"",formula)

Revised formula for B2 is shown below this table.

Copy/paste version: IF(LEN(B2)<4,"",DURATION(,,,LEFT(B2,FIND(":",B2)−1),RIGHT(B2,2)))

NOTE: Assuming your region uses the comma ( , ) as the decimal separator, you will need to replace the commas in the formula with semi colons ( ; ).

Regards,

Barry

PS: I hope my English is as clear as yours was!!

B.

Reply

Convert a plain number in a cell to minutes and seconds (00:00)

Similar questions