sum of total hours not correct

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?

It appears to be off by 23 days.

Any chance you have a date in one of those cells (maybe both), instead of just
times. (Select each cell and look at the formula bar.)

ps. In the USA version of excel, we use: [h]:mm



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?

--

Dave Peterson

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?

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?

No dates, if I chance the hours, so there are no overnight calculations (with
the false-statement), then everything is correct.

In Belgium we use u instead of h for the hour("uur" in belgian).

"Dave Peterson" wrote:

It appears to be off by 23 days.

Any chance you have a date in one of those cells (maybe both), instead of just
times. (Select each cell and look at the formula bar.)

ps. In the USA version of excel, we use: [h]:mm



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?

--

Dave Peterson

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?

Ah, an hour is 1/24 of a day.

Since you added 24 to your formula, it added 23 extra days.

if(from = until;until-from;1-from+until)

should work ok.



Bruno Lauwers wrote:

No dates, if I chance the hours, so there are no overnight calculations (with
the false-statement), then everything is correct.

In Belgium we use u instead of h for the hour("uur" in belgian).

"Dave Peterson" wrote:

It appears to be off by 23 days.

Any chance you have a date in one of those cells (maybe both), instead of just
times. (Select each cell and look at the formula bar.)

ps. In the USA version of excel, we use: [h]:mm



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?

--

Dave Peterson


--

Dave Peterson

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?

Bruno:

I took the last part of your last posting and pasted it into Excel. The
first column I formatted Time 13:30. The second column I formatted as Time
37:30:55. I manually placed the value 30:42:00 into the top of the second
column. I put

=A2+B1 into cell B2 and copied down. This is what I got:

0:00 30:42:00
0:19 31:01:00
0:08 31:09:00
0:45 31:54:00
9:44 41:38:00

This does not replicate your 593:38. When I looked at the first column in
full date/time format, the dates were all the same. I suspect that on your
worksheet the 9:44 is really a much larger value caused by spanning the dates.
--
Gary''s Student


"Bruno Lauwers" wrote:

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?

That's it - thank you very much.
I was thinking I was counting with hours, so I used 24 as hours.
I should have used 24:00, to let excel know that I meant hours - it did not
occur to me that I was working with days.


"Dave Peterson" wrote:

Ah, an hour is 1/24 of a day.

Since you added 24 to your formula, it added 23 extra days.

if(from = until;until-from;1-from+until)

should work ok.



Bruno Lauwers wrote:

No dates, if I chance the hours, so there are no overnight calculations (with
the false-statement), then everything is correct.

In Belgium we use u instead of h for the hour("uur" in belgian).

"Dave Peterson" wrote:

It appears to be off by 23 days.

Any chance you have a date in one of those cells (maybe both), instead of just
times. (Select each cell and look at the formula bar.)

ps. In the USA version of excel, we use: [h]:mm



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?

--

Dave Peterson


--

Dave Peterson