In the meantime I tried the following:
Next to the column where I make the total of one connection I made a sum of
all the totals until then formatted as [u]:mm

date begin user end total subtotal
4/08/2005 8:59 AnetteS 9:50 0:51
4/08/2005 10:38 AnetteS 11:04 0:26 1:17
4/08/2005 11:23 AnetteS 11:36 0:13 1:30
4/08/2005 11:37 AnetteS 11:55 0:18 1:48
4/08/2005 15:14 AnetteS 15:24 0:10 1:58
4/08/2005 15:26 AnetteS 15:37 0:11 2:09

this is the result I get when counting an overnight connection

26/08/2005 13:40 AnetteS 13:59 0:19 31:01
27/08/2005 18:43 LauwersB 18:51 0:08 31:09
27/08/2005 19:02 LauwersB 19:47 0:45 31:54
27/08/2005 22:58 DepotterL 8:42 9:44 593:38

suddenly there is a big jump on the total 31:54h + 9:44 and the total jumps
to 593:38

the following lines are added correctly

29/08/2005 7:05 DepotterL 7:14 0:09 593:47
29/08/2005 7:16 DepotterL 7:23 0:07 593:54

until the next overnight connection and then I get another big jump in the
complete total.

If I format the total as uu:mm then the adding goes correct, the only thing
is that the counting goes until 24:00h and then again from zero.

My end total is made up as a subtotal, so when I filter on the user I get
the total time for that user. With the users that don't have an overnight
connection the total is correct, with a user with an overnight connection the
total is way to big.

If I format everything as general the numbers are correct, and are added
correctly in the total.

I tried the same spreadsheet on a different machine and get the same
results, even when I make the same from scratch.

If you would try this on your machine, do you get correct results?
These are the formulas I use

total time connection total time until now
=IF(B38<=D38;D38-B38;24-B38+D38) =F37+E38
=IF(B39<=D39;D39-B39;24-B39+D39) =F38+E39
=IF(B40<=D40;D40-B40;24-B40+D40) =F39+E40
=IF(B41<=D41;D41-B41;24-B41+D41) =F40+E41
=IF(B42<=D42;D42-B42;24-B42+D42) =F41+E42
=IF(B43<=D43;D43-B43;24-B43+D43) =F42+E43

I have totally no clue as what is going on here?

"Gary''s Student" wrote:

Just as an experiment, re-format the total as general to see its actual value.
--
Gary''s Student


"Bruno Lauwers" wrote:

Cells are formatted as hh:mm and total is formatted as [u]:mm and the total
is only wrong when there is a sum made by the false-statement. If my
formatting was wrong, it should be always wrong - I think.

"Gary''s Student" wrote:

Your math is good, formatting may need to be fixed. Entry cells seem to be
formatted as hh:mm

From you example, it looks like the total is formatted in mm:ss. Excel may
be trying to represent 9 hours as approximately the 561 minutes you are
seeing.

--
Gary''s Student


"Bruno Lauwers" wrote:

I made a spreadsheet calculating how long a user is on line(VPN)
From until = total time
Because this can go overnight, I make the sum with an IF-statement.
if(from <= until;until-from;24-from+until)
This works completely correct.
Then I make a sum of all these times, and here it goes wrong.
If there is a sum calculated with the false-statement, then the sum of the
total hours goes completely wrong. As long as every time is calculated with
the true-statement, the total result is correct.

This even goes wrong by making a sum of one cell.
example
from until total
22:58 8:42 9:44

total 561:44

this is the result I get when making a sum of the column total, format as
[u]:mm

Does anyone have a solution for this?