Calculating minutes and seconds (length of time, not time of day)
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hi all,
I am confused as to how to multiply a length of time and get a return of the new longer length of time.
For example, if I have a time duration of 05.30 (5 minutes and thirty seconds, not 5:30 time of day) and I need to multiply it by 2.0, I should get a return time of 11.00 minutes. Instead, I get a return of 10.60.
If I use a colon between the numbers and seconds, the formula breaks and I don't get a return of a product at all.
Any help would be appreciated.
Matt
15 replies
Sign in or join Beachball Help to reply.
- 0
mabou ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
WWJD,
Thanks for the reply. So.... uhhh... how did you convert the 5:30 into decimal values?
Sorry for the bother, I am not well versed in data manipulation. When I decided to use Numbers to do the calculating for me, I thought it would be basic multiplication of a mm:ss with a result that would also be in mm:ss. - 0
mabou ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Maybe this will help you.. to help me...
πfor a large video project I'm working on, I need to slow down a whole bunch of music from 140 bpm to 130 bpm. To do so, I figured out that I need to multiply the original song length x 1.077 in order to get the new song length.
Below is an example of the problem I am seeing...
Song length=3.53 Multiplier=1.077 New Song Length=3.80I need the new song length to return properly.
Thanks again for all your help.
MattMessage was edited by: mabou
- 0
R C-R ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
My suggestion, based on what I have found effective for similar needs, is to avoid Numbers' date-time data type completely -- there is no need for it.
An example of one way to do this:
1. Use column A as a header column for the name of the song.
2. Use column B for the minutes part of the length of the song.
3. Use column C for the seconds part of the length of the song.
4. Use a different row for each song.So for example, if row 4 is used for a song that is 3 minutes & 53 seconds long, in B4 you would enter 3 & in C4 53. (By using the
Tab key to step from the B to C column you will probably find that this is faster & less prone to entry error than having to enter the colon in a single cell.)5. If you need or want to display the length in the conventional mm:ss format, set column D to
=B & ":" & C -- this will produce a text string Numbers won't try to format as a date-time one.6. Convert the song length to total seconds in the E column with the simple formula
=B*60 + C -- use this for all length calculations. Since these E values are simple numbers, the meaning & formatting is straightforward. For example, the 3 minutes & 53 seconds long song has a value of 233 (seconds) in cell E4.7. To multiply song length by some factor, use column F for the multiplier. Set column G to
=E*F -- this also will yield a number, here the total seconds of the new length.8. Convert the new length back to the mm:ss format for convenience in column H by setting it to
=INT(G/60) & ":" & ROUND(MOD(G,60), 0) -- this once again yields a text string Numbers won't try to format as a date-time. If you wish, you can change the last zero in the formula to a one to show the seconds part to tenth second accuracy or use one of the other rounding functions (like ROUNDUP or TRUNC) if this better suits your needs.Other length calculations can be handled the same way: use the total seconds for calculations & a formula like in #8 to display them as needed in whatever format you want. This is particularly handy for totaling track or clip times -- no special techniques have to be used for totals that run over 24 hours.
You can also adapt this approach to time formats Numbers knows nothing about, like video time codes that represent fractional seconds in frames or CD ones that represent them in samples. (In general, reduce the duration to a total of the smallest time unit of interest, do all your calculations with that, & then convert it to an appropriate text string for display.)
In all cases, you can hide the columns you do not need to see displayed (like E & G in the above example) for a better looking document.
Message was edited to correct formatting errors
- 0
R C-R ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Oops! I noticed too late to add to my last post that my display formulas (steps 5 & 8 in the example) are too simple for general use -- they don't display leading zeros in seconds parts of durations under 10 seconds. IOW, a 2 minute, 1 second duration displays as "2:1" instead of "2:01."
To display the cell values in step 5 in a more conventional format, you could use something like the following for column D:
=B & ":" & RIGHT("00" & C,2)
For step 8, you could use something similar for column H:
=INT(G/60) & ":" & RIGHT("00" & ROUND(MOD(G,60),0),2)
The ROUND("00" & {whatever}, 2) part would need to be changed if fractional seconds are to be displayed.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hello
Assuming that the time value is entered in cell B2
in any cell of row 2 enter:=(MOD(B-DATE(1904,1,1),1)
it will return the time value in decimal day.
=(MOD(B-DATE(1904,1,1),1)*24 is the same in decimal hour
=(MOD(B-DATE(1904,1,1),1)
2460 is the same in decimal minutes=ROUND(MOD(B-DATE(1904,1,1),1)
24*6060,0) is the same in secondsThe late one is useful when wr need to rebuild the standard display:
=TIME(0,0,nbSeconds)
I feal useless to use two or three columns when a single one is sufficient but we are living in a free world, aren't we π
Yvan KOENIG (from FRANCE mercredi 18 juin 2008 08:45:09)
PS no need to add Mr. when naming me, I would just grind if someone write Miss Yvan π
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
While I was in the shower, I thought to a simpler formula.
Assuming that the time value is entered in cell B2
in any cell of row 2 enter:=(MOD(B-TODAY()),1)
it will return the time value in decimal day.
=(MOD(B-DATE(1904,1,1),1)*24 is the same in decimal hour
=(MOD(B-TODAY(),1)
2460 is the same in decimal minutes=ROUND(MOD(B-TODAY(),1)
24*6060,0) is the same in secondsThe late one is useful when wr need to rebuild the standard display:
=TIME(0,0,nbSeconds)
I feal useless to use two or three columns when a single one is sufficient but we are living in a free world, aren't we
Yvan KOENIG (from FRANCE mercredi 18 juin 2008 09:45:00)
- 0
mabou ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Wow, this was the first time I had posted to the Numbers forum, and I must say that this was a great experience.
Thank you SO much for all of your informative responses, there are apparently a few ways to get the job done, and I will start exploring your answers to see which works best for my level of knowledge and as well as for the task at at hand.
I want to sincerely thank you all for your time. I wish all forums were visited by people as knowledgeable and considerate as this one.
Take care, and hopefully someday I can return the favor.
Matt - 0
R C-R ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
KOENIG Yvan wrote:
I feal useless to use two or three columns when a single one is sufficient but we are living in a free world, aren't we πThere are advantages to using multiple columns that the OP should consider:
1. Ease of entry: by using separate, adjacent cells for each unit of time, there is no need to enter unit separators or leading zeros.
2. Accuracy of entry: what you see is what you get, so there is no possibility that a hidden date will affect calculations or Numbers will interpret a mm:ss entry as a hh:mm one.
3. Accuracy of calculations: Because the smallest time value Numbers recognizes is one second, higher accuracy isn't possible with single-cell, time-like entries or in combination with TIMEVALUE, DATE, NOW, etc. This can be important when "stretching" audio files to synchronize with video clips. (A lot of notes can occur in one second.)
4. Adaptability/convenience: when working with media files, a variety of time formats besides mm:ss may be encountered. (Video typically uses frames as the smallest time unit, for example.) It is quite useful for the spreadsheet to accept, display, & convert among these formats. This is easy to do when each unit is entered in a separate cell ... & because of #3 above, pointless if single-cell entries ignore less-than-second values.Like the OP, I work with audio files for which less than a second values may be important. Like Yvan, I once thought single-cell entries should suffice, but after trying the multiple cell approach, I discovered its merits far outweighed the initial strangeness of entering multiple values, at least for my work.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
R C-R wrote:
Like the OP, I work with audio files for which less than a second values may be important. Like Yvan, I once thought single-cell entries should suffice, but after trying the multiple cell approach, I discovered its merits far outweighed the initial strangeness of entering multiple values, at least for my work.The OP asked about minutes and seconds.
He wrote later that he was multiplying numerical values by 1.077 if I remember well.
It was never asked about the display of part of seconds in 1/60 units.
So, _for the given problem,_ I don't see the advantages of multiple columns.
I know that I am pig-headed πYvan KOENIG (from FRANCE mercredi 18 juin 2008 15:55:30)
- 0
R C-R ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
If you check the times of each post, you will notice that I did not contribute anything to this subject until mabou mentioned that the specific use was multiplying song lengths by a factor to get new song lengths for a video project.
Since this is closely related to how I make my living, I am perhaps more familiar with the problems of using Numbers for this than the average user. This is why I mentioned some considerations that may not have occurred to those less familiar with this kind of work. I have, as we say in the States, "been there & done that."
But that certainly doesn't mean that my suggestion is the only or even the best way to do this. I would welcome any suggestion for an easier, more elegant, or simply different way to handle durations in Numbers when less than one second increments cannot be ignored. I am especially interested in high-accuracy methods that are easily adaptable to arbitrary time formats like frame or sample counts.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
If, like the OP, you start from values using minutes and seconds, converting them in decimal format then doing your computations on this decimal format, the results will be correct.
At least if you don't repetitively convert decimal values to time value then these time values to decimal values.decimalVal1 -> timeVal1 -> decimalVal2
decimalVal2 would be different than decimalVal1
So if you need to make linked calculations you must work this waydecimalVal1 -> timeVal1 -> reUse decimalVal1
I apologize but for me it's more difficult to describe it than it is to do it π
When you do the first conversion from time in min:sec to numeric values, convert the values in number of second and apply ROUND(nbSeconds,0) to get rid of the oddities of calculations upon floating numbers.
This is the kind of tip I learnt when I studied to become an Engineer (years before I decided to become a potter).
Yvan KOENIG (from FRANCE mercredi 18 juin 2008 21:44:44)
- 0
WWJD ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
The values must be converted to decimal due to the math request (Time Value x 1.077). The extra columns were for illustration purposes only and can be combined into one column per Yvan's notes.
Regards,

- 0
R C-R ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
KOENIG Yvan wrote:
When you do the first conversion from time in min:sec to numeric values, convert the values in number of second and apply ROUND(nbSeconds,0) to get rid of the oddities of calculations upon floating numbers.In the first place, if you try to enter times in min:sec format into Numbers cells, it will interpret them as hour:min expressions & add a date to them to make them date-times, so you must start by converting this to something that actually does represent minutes & seconds. I hope it is obvious that you must do this before using any function like TODAY, NOW, or TIMEVALUE on the cell. Perhaps the simplest one-cell solution is to always remember to add a leading "00:" to the min:sec entry, but it is silly to have to do something like this -- the computer is supposed to make things easier, not add to your work load. You could also devise a parsing scheme to detect & extract the minute & second strings, but you will soon realize that this results in an elaborate formula if you want to be able to enter the occasional duration that is greater than an hour.
Basically you are fighting the fact that Numbers will interpret anything entered that looks like a time as a date-time, whether it really is or not. You can win the fight, but it isn't easy & will prove much more computationally expensive than a more direct method that avoids date-time data completely.
Regardless of how you handle this, let's say you have extracted a number representing the total number of seconds of what you entered -- that is, 60 times the minutes part plus the seconds part. Unless your extraction method is seriously flawed, it will be a whole number of seconds & rounding is not necessary.
Now let's say you want to multiply this by some factor, say 1.077. Let's also say this is to change the length of a musical composition so its tempo (beats per minute, or BPM) matches some action in a video clip. You generally do not want to round the result to the nearest second because synchronization errors of much less than a second are noticeable, especially where music is involved. In this use, & in many similar ones, fractions of seconds do matter.
Do you begin to see why I suggested the OP use multiple cells for his duration entries? It isn't about avoiding cumulative errors in repeatedly switching between numeric & time values. It is about avoiding the problems inherent in using date-time values to begin with.
- 0
R C-R ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
WWJD wrote:
The values must be converted to decimal due to the math request (Time Value x 1.077).More fundamentally, they must be converted because Numbers has no data type appropriate for lengths of time (durations), just for moments in time (time of day plus calendar day).
Once you accept that they are entirely different things & that the only point of commonality is that each has a component that can be expressed in units of time, it become clear why working with durations in Numbers as if they were moments in time is so complicated.
You can often avoid the complications of this fiction by treating durations as numbers from the onset. Although this may require using separate cells for entering each of the units of time of interest, which admittedly seems like something that should be unnecessary, the efficiencies gained may surprise you, both in data entry & in spreadsheet design.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
When I write that I want to convert values min:sec to decimal values, its a quick formula.
Of course, I know that I must put 00:mm:ss.When you have to enter BPM, as far as I know, they are numbers, not time values.
Are you trying to explain that your program requires that time values are entered as hh:mm:ss:xx
where xx is in the range 0 thru 99 or in the range 0 thru 59.
If it is that, the problem may be easily solved.
Tell me which is the used range for xx and I will be able to write a script allowing you to translate a decimal number of seconds in the wanted format so that you will be able to paste it in the used tool.Yvan KOENIG (from FRANCE jeudi 19 juin 2008 14:55:07)