Time Calculation Help

[Kyosti]

I have very large time values that I am trying to add in order to use the value in another calculation. It is a very simple formula that I know works:

=sum(a1+b1)

In the case of the following it works perfectly:

9743:53:41 + 18409:10:00 = 28153:03:41

However on the following it does not:

10801:01:57+18409:10:00= #Value!

The value 10801:01:57 does not seem to be looked at as a time value as it is automatically changed into a text type of format. When I change the format to the proper time format, the result is the same.

Does anyone have any clue as to what is causing this?

 

[GlennUK]

Try this array formula ( method pinched from Barry Houdini actually ), entered using Ctrl-Shift-Enter instead of Enter:

=SUM(IF(ISNUMBER(A1:B1+0),A1:B1,RIGHT(A1:B1,7)+LEFT(A1:B1,LEN(A1:B1)-7)*10/24))

 

[Kyosti]

Thanks for the reply. While the formula looked very promising, it still does not give the expected value. In addition it will not let me format into a time format.

Any other suggestions???

 

[Kyosti]

I take that back, even though I was using ctrl-shift enter, it was not giving me the correct values at first.

Now it is


Thanks!!!

 

[GlennUK]

My pleasure!