adding times results in different outcomes

[Johanneke757]

I'm adding times that I worked on different projects. When I add the times per day the result is OK. When I add the hours worked per day the first week total gives the correct result, but the other weeks give a result of zero hours. I do not see a difference in how I have added the daily totals first week: =SOM(AK3:AK9) = 25:41; second week =SOM(AK11:AK17); =0:0; see image. The format of the cells is equal (I did a copy paste for the week and the week totals) Any suggestions what causes this problem?

Totaal dag​
05:22​
07:20​
04:23​
00:00​
04:16​
01:10​
03:10​
25:41​	week 1
07:15​
05:59​
07:14​
06:15​
05:47​
00:00​
00:00​
0:00​	week 2

 

[Toadstool]

Hi Johanneke757,

All I can think is there's some unprintable characters in your pasted numbers so it treats them as text. Take off any alignment on the column. Text should align left and numeric right.

Looks OK to me:

Johanneke757.xlsx
	AK	AL
2	Totaal dag
3	5:22
4	7:20
5	4:23
6	0:00
7	4:16
8	1:10
9	3:10
10	25:41	week 1
11	7:15
12	5:59
13	7:14
14	6:15
15	5:47
16	0:00
17	0:00
18	32:30	week 2
Sheet2

Cell Formulas
Range	Formula
AK10,AK18	AK10	=SUM(AK3:AK9)

 

[Johanneke757]

Thanks for reacting on my question. But all these cells are calculated values - sum (a:z) with the cell characteristics : mm; If you hoover over the cells above you can see that the values are shown as parts of a day (or24 hours); so there is not text included. Stupid thing is that here the value for week 2 is shown with the correct value, whereas in my spreadsheet it is shown as zero. And I just made a copy of the cells from the spreadsheet. See the same spreadsheet in the picture below. There the total for week2 is shown as 0, with the same kind of data and cell characteristics for the total of week1 as for week2 (I did a copy paste). I still do not understand what causes the problem.

 

[joeu2004]

all these cells are calculated values - sum (a:z) with the cell characteristics : mm

As someone once wrote in another forum: a picture is worth 1000 words; but an Excel file is worth 1000 pictures.

English (any natural language) is an imprecise tool for describing things.

For example, you write "all these cells are calculated values". But you do not say that AK10 (?; the cell that displays "07:15") contains a formula that performs the calculation. You might mean that you copy-and-pasted the value.

For another example, you write "with the cell characteristics : mm" (sic). Obviously that is not the cell format, since it displays "07:15".

If you hoover over the cells above you can see that the values are shown as parts of a day (or24 hours); so there is not text included

We cannot hover over the cells, since you posted an image, not even an XL2BB capture.

There does not need to be any text in the cell. But the type of the cell value might be text, even if the cell has a numeric format like mm:ss .

Looks can be deceiving. And again, the cell format does not matter.

Use formulas of the form =ISTEXT(AK10) to determine if the type of the cell value is indeed text.

I still do not understand what causes the problem

And we probably will never understand the problem if we go back and forth posting English descriptions and incomplete images. (Thanks for including the column names. But without the row numbers, we cannot easily talk about individual cells in the image.)

And in this case, I do not believe this forum's XL2BB feature will provide what we need to see. In particular, I do not believe we can distinguish type text from type numeric.

Upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com.

In any case, test the download URL first, being careful to log out of all websites that share the same login, in order to ensure that anyone else can access the file anonymously.

 

[Johanneke757]

The problem has been solved; it was caused by another cell in the spreadsheet, where a cell with the sum was included in the addition (circular reference); however this fault was not shown in that cell but much later on in the spreadsheet. And it was not referred to as a circular reference, but as being different compared to adjoining cells.