I am adding Days and Hours/Min in two different columns. All values are positive

ID: 3563772 • Letter: I

Question

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 should read 597:37 and it again returns ####

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

Thanks in advance!

Date/Times are stored as int/decimal values, so using MOD(SUM(),24) is not going to work, and is the 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.