adding times results in different outcomes

Johanneke757

New Member
Joined
Feb 12, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

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,)

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,402
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 12, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.
1613324721930.png
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 12, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top