time operation

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
One possible solution is the following array formula

=SUM(A1:C1-SECOND(A1:C1)/(24*60*60))

you need to enter this formula with Control Shift Enter, not just Enter like regular formulas.
 

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
Juan,

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 A1=45:38:00; B1=00:04:00; C1=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.

Interestingly enough, each individual time minus the seconds yields a time with zero seconds. It is only when you sum the three do you get a time containing seconds.

Do you have any ideas why Excel would equate this to a time containing seconds as opposed to a time void of seconds as it seems logically it would?


Many thanks,
Tom F
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Well Tom, first, your formula uses some cells that I don't know what houses (G26, D6, D19), then, in there, you're dividing G26/2, which, I also don't understand.

As for the other question, why is it adding up to 12:07:59, I don't know, maybe you have some "hidden" decimals somewhere in there ? because this is what I see:
Book1
ABCD
100:45:3800:0411:19:30
2
312:09:08StandardSum
412:08:00WithoutSecondsusingSUM
512:08:00WithoutSecondsusingstandardmath
Sheet1
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi Tom F:

use ...

{=TIME(SUM(HOUR(A1:C1)),SUM(MINUTE(A1:C1)),0)}

this should correctly give 12:08

Regards!

Yogi Anand
 

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

If you format G26, D6 and D19 as h:mm:ss.00 what do you get?
 

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
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)
 

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
Andrew,

Good thought. Where do you find the format h:mm:ss.00? I looked in the FORMAT CELLS under both time and custom and could not find it.
 

Forum statistics

Threads
1,144,769
Messages
5,726,183
Members
422,661
Latest member
foxleinlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top