Negative values in Excel hh:mm sums I am adding Days and Hours/Min in two differ

ID: 3564703 • Letter: N

Question

Negative values in Excel hh:mm sums

I am adding Days and Hours/Min in two different columns.

All values are positive and they all come from direct entries, Not through indirect calculations in other cells.

Column A is dedicated to Days;

Column B is dedicated to Hrs/Min;

The SUM are calculated as follows:

Days: SUM(A1:An)+TRUNC(SUM(B1:Bn))

Hrs/Min: MOD(SUM(B1:Bn);24)-TRUNC(SUM(B1:Bn))

These formulas are repeated in several similar additions.

In most cases they work. In few others they return negative values for no apparent reason.

I have checked for cell format discrepancies are they are all identical.

All formulas have also been checked and they are also correct.

The problem starts AFTER several values have been added

At this point,,, the actual sum in a certain "problematic" column should read 627:05 while it returns ####

When I change from 1900 date system to 1904 date system it returns -572:55 which is also incorrect.

In a different problematic column the actual sum shoould read 597:37 and it agaain returns ####

Again, when I change from 1900 date system to 1904 daate system it returns -557:23 which is also incorrect.

Any ideas would be greatly appreciatedd!!

Date/Times are stored as int/deccimal values, sso using MOD(SUM(),24) is not going to worrk, and is thee source of your error.

To sum the remaining hours and minutes that are not complete days, just use

=SUM(B1:Bn)-TRUNC(SUM(B1:Bn))

formatted as time. And since all your times are positive::::

=SUM(B1:Bn)-INT(SUM(B1:Bn)))

will return the same thing. I just like INT better than TRUNC........

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.