Random calculation error with adding and subtracting times

The calculations look correct to me, so it is either misunderstanding exactly what Excel is doing, or something is incorrectly programmed, and I'm not sure which. Here's what I see happening:

My first observation is that your time formats are all "time of day" formats ("h:mm"), but you talk about them as if they are "elapsed time" kind of formats ("[h]:mm"). The first thing I did was change the format to an elapsed time format, which shows that what you claim is -4 minutes in AG10 is really -24 hours and 4 minutes -- the time of day format conceals the "day" information that is in the cell value. Of course, -24:04+1:14 (-1.00313+0.0514 -- the actual cell values behind the time formatting) is correctly calculated as -22:50 (-0.95174). I think that, if you will format your cells as elapsed time or even general (where the numbers you see will mean decimal days), you will see that the calculations are correct. Then you will be in a better position to determine if the error is in the formatting or in the actual calculation or if numbers have been entered incorrectly.

Why do you say the answer is crazy?

It is adding AG10 to AD11
AG 10 is displayed as a time format -00:04. The underlying number is -1.003
AD11 displays time format 00:14, underlying number .05138.
The sum of the two is negative -.95162 which displays as a time format -22:5

You don't complain that AG9 plus AD10 shows a sum of - 00:04 minutes, why therefore do you complain the about AG10 Plus AD11 showing a total of -.95162, which equates to -22:50 i.e. .95162 * 24 = -22.83888 hours or -22:50.


What answer do you expect? 1:10 or 1:18. The difference is due to whether the AG + AD calculation is positive or negative. In the earlier rows it was negative. In the AG11 row it is positive. If you want to trap and correct it you will need to include an IF test. Maybe something like

Formula:

Please Login or Register  to view this content.