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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi Tom F:

use ...

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

this should correctly give 12:08

Regards!

Yogi Anand
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top