Converting seconds to HH:MM:SS

Actually I have a similair question, I have a column with Minutes (94, 108, 61...) and I want a result in hours so 94+90=3Hs 4Mns.
Maybe Yvan can help.:)

Hello

Here I am.

Before giving the wanted responce, I wish to write that it is available in the Application's help.
If you enter this Help and ask for the keyword "second" you will get a lot of entries.
One of them"TIME" is the perfect one but when someone don't know the precise answer, he may look at the entry "Date and Time Functions" and read:

TIME Converts hours, minutes, and seconds into a time format.

Double clicking the name "TIME" he would get the detailed description of this function.

It is not difficult to reach it and it is easy to understand.

Assuming that a cell in column B contains a number of seconds (3718).
In the cell C of the same row, enter the formula
=TIME(0,0,B)
which reads : calc the Time of 0hours, 0minutes, (contents of B) seconds
will return, if the format of the cell is set to hh:mm:ss
01:01:58


Assuming that a cell in column B contains a number of minutes (200).
In the cell C of the same row, enter the formula
=TIME(0,B,0)
which reads : calc the Time of 0hours, (contents of B) minutes, 0second
will return, if the format of the cell is set to hh:mm:ss
03:20:00

Remember: the Help as well as the PDF User's guide are not only for Helpers, _*they are Also for you*_ 😉

Yvan KOENIG (from FRANCE jeudi 3 janvier 2008 11:26:2)

Thanks Yvan,
I wrote in the Numbers feedback about their Time formulas, hopefully they can make it easier for us mortals.
In my table I have a whole column of minutes (B2=30, B3=35, B4=20) and in B5 I want the result in HH:MM (no seconds). All my cells are formated in HH:MM
Following your example I wrote the formula in B5 as follows: =TIME(0,B2:B4,0) and I get a warning sign saying " The range B2:B4 can't be used as a single value"
Can you tell me what am I doing wrong?

Hello

I'm not sure that I understand well your question.

Numbers is right: B2:B4 is not a number, it's a range of cells.
Your formula is trying to use this range of cells as a number representing the minutes: it's meaningless. The number of minutes may be given two ways
B2 B3B4
This way the formula would be =TIME(0,B2 B3B4,0)
an alternate way (more efficient) is
SUM(B2:B4) (which means sum of the contents of every cell from B2 thru B4
This way the formula would be =TIME(0,SUM(B2:B4),0)

If you looked at the Help you would have read:

TIME

The TIME function converts hours, minutes, and seconds into a time format.
TIME(hours, minutes, seconds)
hours: The number of hours (using a 24-hour clock).
minutes: The number of minutes.
seconds: The number of seconds.
Notes
You can specify hour, minute, and second values greater than 23, 59, and 59, respectively. If the hours, minutes, and seconds add up to more than 24 hours, Numbers subtracts 24 hours repeatedly until the sum is less than 24 hours.

If you don't wish to see the seconds appearing in the cell, apply the format Time > hh:mm

I agree with most users about the need for enhancements about the time treatment, mainly the addition of a "Duration" format not limited to the range 0 thru 23:59:59.
BUT for the questions asked in this thread no enhancements are required.
The available tolls fit perfectly the needs … but of course, users must read the given explanations 😉

I'm always surprised to see guys paying to learn to use a bicycle and many other tools but reluctant to learn how to use softwares they are employing daily.
I never accepted the well known acronym: RTFM (Read The Fu…g Manual).
In fact I never read a manual, but I consult them when I need an info.
I always thought that forums are here to respond to questions whose responce is not in the manuals (and they are sufficiantly numerous).

Of course, it is often more time consuming to explain this advice than reproducing some lines from the manual but I think that no one may seriously use a tool begging the info for every task.
We are assumed to be thinking beeings. Aren't we ?

Of course the given formulas assumes that the time value is fitting the Numbers's requirements (from 0 thru 23:59:59)

Yvan KOENIG (from FRANCE jeudi 3 janvier 2008 13:38:55)

So I take it that you are trying to add the minutes together, then display the result like HH:MM? If so, the problem you are having is not with inadequacies of Numbers' time handling (which are considerable), but with your understanding of formulas. You must sum the minutes first:

B5=TIME(0,SUM(B2:B4),0)

This cell may then be formatted like Date: "None", Time: "14:10". Note, though, this technique only works when the total is less than 24 hours. Yvan will likely provide or point you to a solution that handles larger sums, but it will be a bit more complicated.

Hello

Your wishes are orders 😉

=IF(SUM(B2:E2)<(24 60),TIME(0,SUM(B2:E2),0),(INT(SUM(B2:E2)/(24*60))24)+HOUR(TIME(0,SUM(B2:E2),0))&":"&RIGHT("0"&MINUTE(TIME(0,SUM(B2:E2),0)),2))

as everybody knows that 24*60=1440 we may shorten to:

=IF(SUM(B2:E2)<1440,TIME(0,SUM(B2:E2),0),(INT(SUM(B2:E2)/1440)*24)+HOUR(TIME(0,S UM(B2:E2),0))&":"&RIGHT("0"&MINUTE(TIME(0,SUM(B2:E2),0)),2))

which reads:
if sum of durations is less than 24*60 minutes then returns TIME(0,sum of durations,0)
else
returns
(numberOfDays*24+numberOfHoursInThe24hoursRange) concatenated to ":" & numberOfMinutes

Yvan KOENIG (from FRANCE jeudi 3 janvier 2008 14:34:58)

Yvan, do not assume I am not reading the help guide. I did, before posting my first thread. I read the section about TIME. But as I said in another thread, what seems logical and simple and "But of course" to you, isn't to me. And that's why I post on this forum. Reading is one thing, understanding is another. I can read every help topic on the formulas, but frankly, I probably understand 5% of it.

In any case, you help and patience, as I said before, is greatly appreciated. Thank you very much.
The formula in the above post is what I was looking for.

I have not read the manual either but here is an example (I've placed the equations below the cells):



Regards,

Hello

You don't like that I write "look in the manual" but where would you find the info?
In your case, it's not the helpers task to explain you that it is YOUR duty to go to the Cells Inspector to select the time format hh:mm:ss
At this time, YOU are using the format hh:mm AM/PM.
We can't do the change for you.

I wish to add that at this time, duration is an unknown concept for Numbers.
It only know TIME values in the range 0:00:00 thru 23:59:59 but _that is ALSO explained in the Help and in the PDF User's guide._

Yvan KOENIG (from FRANCE vendredi 4 janvier 2008 12:28:23)

Though not the case here, please note that when converting to a time value using TIME() by "loading" the seconds like this

=TIME(0,0,A2)

when A2 is the result of some calculation (the difference in two times, for instance) it is important to first ROUND() the result as

=TIME(0,0,ROUND(A2,0))

in order to get second-perfect results. It seems that TIME() must truncate it's arguments (a bug, in my eyes) to integers. While that behavior is out of the user's control, rounding mitigates the problem.

Hello

You are perfectly right and in some cases, I discovered that the ROUND() is required when the used numbers are the results of calculations done in the formula because when us mortals know that a division gives a finished result (0.23 for instance), it may be 0.230000000001 from the computer's point of vue.

This is why you had perhaps encounter this ROUND() in some formulas I posted here in the two last months.

I also met an oddity dealing with string coercion.

It's not described in the help 😉 but we may use the concenate operator to gather two numbers.

Alas, in some cases, when the number results of a calculation done in the formula, the result is odd and it is necessary to coerce the value as string using the FIXED() function.

Just for see here is a sample given in this forum:

A 10
B 10.11
=IF(LEFT(ROUND(B-A,12),1)="0",RIGHT(ROUND(B-A,12),LEN(ROUND(B-A,12))-1),ROUND(B- A,12))

appears as correctly spelled.
Alas, on a French system, it displays .11 when the correct value is ,11 (here we use a decimal comma)
To get rid of that, we must code:
=IF(LEFT(ROUND(B-A,12),1)="0",RIGHT(FIXED(ROUND(B-A,12)),LEN(ROUND(B-A,12))-1),R OUND(B-A,12))
The FIXED() forces the application to convert the internal decimal period in the external required decimal comma.

Yvan KOENIG (from FRANCE vendredi 4 janvier 2008 15:57:54)

KOENIG Yvan wrote:
You are perfectly right and in some cases, I discovered that the ROUND() is required when the used numbers are the results of calculations done in the formula because when us mortals know that a division gives a finished result (0.23 for instance), it may be 0.230000000001 from the computer's point of vue.

Yes... and apparently, in the case of TIME(), with it distain for floating point numbers, a function call like

TIME(0,0,59.99999999999)

is executed like

TIME(0,0,59)

rather than

TIME(0,0,60)

In my opinion, this is a bug in TIME(). TIME() should be made honor floating point values for all parameters so, for instance,

TIME(0.5,0,0)

would equal 0:30:30

and

TIME(0,0,12.34)

would equal 0:00:12.34

Hello

It seems that an equivalent of ROUND(theVal,12) is missing somewhere.

I already reported that to Apple but of course, messages about that on the official track would be usefull.

Yvan KOENIG (from FRANCE vendredi 4 janvier 2008 19:42:29)