Numbers: Cell time calculation not adding the correct time

If A2 contains a time value such 5:45
and B2 contains a number which= an amount of minutes ie 40
If you want to add the minutes in B2 to A1 and return a time value (ie 6:25) then the formula in C2 is

=TIME(0,(HOUR(A2)*60 MINUTE(A2)B2),0)

You'll note I've reversed the locations of B1 and C1 as per your example. You'll have to flip them round again if you want Time, Result, Minutes

Hope this helps

M.

To add a bit: If you want A3 to pick up the value in C2 you can do one of two things:

in cell A3 you could obviously enter the formula:

=C2

However if you want to keep everything blank until you enter numbers in the B column then you could put the following in A3:

=IF(ISBLANK(B3),"",C2)

Which will only return the value C2 if you enter something into B3 (your new minutes value)

and then in the C column you could enter the equivalent condition:

=IF(ISBLANK(A3),"",(TIME(0,(HOUR(A3)*60 MINUTE(A3)B3),0)))

eveything is now empty until you enter in an amount of minutes.

Pull both these coloumn down the list and you should be going at least someway towards being happy.

Apologies if over complicating

M.

Skip68 wrote:
Cell A1 has a time for example 5:45 PM

Cell C1 has an amount of minutes for example 40

Cell B1 should be adding 40 minutes to 5:45 PM for result of 6:25 PM

The lazy guy which I am wrote this short formula:

=TIME(HOUR(A),MINUTE(A)+C,0)

Yvan KOENIG (from FRANCE vendredi 4 juillet 2008 11:38:08)

=TIME(HOUR(A),MINUTE(A)+C,0)

• Every time I may use them, I uses the "short references", those with no row num embedded.

• The TIME function requires three parameters,
a number of hours,
a number of minutes
a number of seconds

but the formula parser is fair enough to understand that, when the given number of minutes is greater than 59, it means some hours + some minutes.
And of course, mutatis mutandis, it's the same for the number of seconds.

This is why, when I want to display with the current format hh:mm:ss a decimal time value as those resulting of calculations, I don't spent time to isolate the number of hours, the number of minutes, the number of seconds. I just calculate the total number of seconds.
if cell B2 contains 0.1234490740741
I type =TIME(0,0,B 24*6060) in cell C2
Then, applying the yy:mm:ss format I get 02:57:46
inserting =TIMEVALUE(C) in D2, I get 0.1234490740741

I know that this behavior is not described in the Help.
I don't feel that it's a sin.
It's for passing this kind of details that the forum exists.
I feel perfectly odd questions whose response is available in the Help but it's always a pleasure to help users to discover features which are only available "between the lines".

Yvan KOENIG (from FRANCE vendredi 4 juillet 2008 14:48:21)