# adding times results in different outcomes

#### Johanneke757

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

##### Well-known Member
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
AKAL
2Totaal dag
35:22
47:20
54:23
60:00
74:16
81:10
93:10
1025:41week 1
117:15
125:59
137:14
146:15
155:47
160:00
170:00
1832:30week 2
Sheet2
Cell Formulas
RangeFormula
AK10,AK18AK10=SUM(AK3:AK9)

#### Johanneke757

##### New Member
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

##### Well-known Member
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.

Last edited:

#### Johanneke757

##### New Member
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.

Replies
4
Views
99
Replies
2
Views
192
Replies
6
Views
80
Replies
1
Views
67
Replies
31
Views
512

1,128,114
Messages
5,628,783
Members
416,338
Latest member
Spartan5305

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back