# time operation

#### Tom F

##### Active Member
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Juan Pablo González

##### MrExcel MVP
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
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
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
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
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
If you format G26, D6 and D19 as h:mm:ss.00 what do you get?

#### Tom F

##### Active Member
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
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.

#### Andrew Poulsom

##### MrExcel MVP
Under Custom there is a format mm:ss.0. Just change it to h:mm:ss.00 in the Type box.

Replies
3
Views
237
Replies
0
Views
227
Replies
2
Views
299
Replies
2
Views
135
Replies
4
Views
204

1,181,534
Messages
5,930,462
Members
436,741
Latest member
buckeyerich

### 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.

### Which adblocker are you using?

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

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