time operation

[Tom F]

When the following times are added A1 = 0:45:38; B1 = 0:04:00; C1 = 11:19:30 the sum in hours and minutes is 12:09. Does anyone know how these times be added without the seconds arriving at the time 12:08?

Many thanks,
Tom F

 

[Tom F]

Andrew,

Thanks for the formatting tip.

Here are the values for the following variables returned.

G26/2 = 0:45:37.50
D6 = 0:04:00.00
D14 = 11:19:30.00

Now I really cannot make sense of this.

 

[Aladin Akyurek]

On 2002-10-23 04:28, Tom F wrote:
Juan,

You are right.

I should have said, “Excellent suggestion, however when (G26/2)-(SECOND(G26/2)/(24*60*60))+D6-(SECOND(D6)/(24*60*60))+(D19-(SECOND(D19)/(24*60*60))) is evaluated for G26/2=45:38:00; D6=00:04:00; D19=11:19:30 the expression equates to 12:07:59 and not 12:08:00 as expected since the seconds are being subtracted individually from the time in each cell. “

I went through exactly the same calculations you did and with the same conclusions and do not understand it why the individual values are okay without the seconds, but when you sum the three, it yields 12:07:59.

Many thanks,
Tom F
This message was edited by Tom F on 2002-10-23 05:41

What is the value of G26 before division by 2?

 

[Andrew Poulsom]

Well if G26/2 is 0:45:37.50 and SECOND(G26/2)/(24*60*60) is 0:00:38.00 then the difference is 0:44:59.50. So the total is 12:07:59.50, which formatted as h:mm:ss becomes 12:07:59, presumably because of the vagaries of floating point arithmetic.

In case G26 contains an odd number of seconds you need to do some rounding:

=ROUND(G26*86400/2,0)/86400

or use the HOUR and MINUTE functions as Yogi suggested.

 

[Yogi Anand]

On 2002-10-23 05:24, Tom F wrote:
Good idea Yogi,

Do you know a shorter way to code the following statement?

=TIME((HOUR(D6)+HOUR(D19)+HOUR(G26/2)),(MINUTE(D6)+MINUTE(D19)+MINUTE(G26/2)),0)

Hi Tom F:

Sorry, I believe this is as short as it can be, since our range of data in this formulation is non-contiguous.

Regards!

Yogi Anand

 

[Tom F]

Andrew, Yogi, Juan, and Aladin

Thanks so much for the help. Your suggestions were well taken and the problem is solved thanks to you.

A lot has been learned. You guys know some stuff!


“Life is a journey … not a destination. Enjoy the trip!”

 

[IML]

On 2002-10-23 05:24, Tom F wrote:
Good idea Yogi,

Do you know a shorter way to code the following statement?

=TIME((HOUR(D6)+HOUR(D19)+HOUR(G26/2)),(MINUTE(D6)+MINUTE(D19)+MINUTE(G26/2)),0)

a little shorter
=SUM(INT(D6*1440),INT(D19*1440),INT(G26*1440)/2)/1440

this assumes only times exist and not times and dates in the cells
This message was edited by IML on 2002-10-23 18:04

 

[Yogi Anand]

Hi IML:

Good Call!

In comparison to this
=TIME((HOUR(D6)+HOUR(D19)+HOUR(G26/2)),(MINUTE(D6)+MINUTE(D19)+MINUTE(G26/2)),0),

this one
=SUM(INT(D6*1440),INT(D19*1440),INT(G26*1440)/2)/1440

is indeed shorter.

Regards!

Yogi Anand