[Solved] Time is added wrong (when sum of times > 24h)

[langba]

using oo 3.2.1 OOo_3.2.1_Win_x86_install-wJRE_en-US.exe

I can add up hours and minutes with no problem until I get to 24 hours, then the result is wrong, even negative.

I track my clock in/out times. Daily, the hours always are correct. When I sum all 7 days, I get this result:

8:29 + 9:53 + 9:51 + 0 + 0 + 6:01 + 0 = -13:46 (all positive times, sum of negative 13 hours...oh no, I owe my company!?)

It is easy for me to see that the time is exactly 2.0 behind. But in my formula, how will I know to add 2. If I work less days, I will only add 1 for example, more then maybe 3 =)

I drag this formula down many rows. Some rows calculate correctly, others don't. I am confused.

I have been running this same calc spreadsheet since oo 2.x so maybe it is corrupt?

[floris v]

Not for me on 3.2.0, at least when I enter each time in one cell, then use SUM().

[langba]

I have confirmed spreadsheet on another computer, using 3.2.0, calculated sheet correctly. Back to computer with 3.2.1, cannot sum the times correctly when > 24.

[MrProgrammer]

I suggest that you use Format, Cells, Numbers, Format Code, [HH]:MM because the brackets around HH tell the spreadsheet that hours can be larger than 24. If that does not work, I suggest that you post a small sample so we can see what your data and formatting looks like, after removing any proprietary or confidential information.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[langba]

I have [HH]:MM as the format. It is wrong. How do I post a small sample? Do you want a screenshot or my time tracking spreadsheet?

[thomasjk]

Best to attach your file if smaller tan 128kb. Otherwise a sample file. See http://user.services.openoffice.org/en/ ... =74&t=8289.

[langba]

Here is a sample week. (I hope a second set of eyes will see my error right away!)

[acknak]

I have [HH]:MM as the format.

Close. You have "H:MM" as the format, which displays a clock time, which cannot be negative. Change the cell format, as Mr Programmer suggested, and I think you'll see the problem.

[langba]

I'm really sorry, but it says the format is [HH]:MM. I will delete cell and try again. Post back in a minute.

[langba]

Here is the result. Still -13:46.

[FJCC]

The error is not in cell L9 where the sum is calculated, but in cells D11:J11. Copy these cells and do a Paste Special in an empty area keeping the Date &Time but not the Formulas or Format. You will see that the values in columns D, E and F are negative and the value in column I is greater than one. Cells D11:J11 are formatted as H:MM and this hides the error. You will have to rework your IF() formula to properly handle Clock Out times beyond midnight. I think this works

Code: Select all

=IF(D10<D9;D10-D9+1;D10-D9)

as long as you work less than 24 hours and I'm sure there are other ways to do it.

[langba]

Yupp! That was it. I didn't think to reformat the other (seemingly) positive times as another format. Thank you!

[acknak]

Oops--my bad. I should've mentioned which cells to look at.

[gandalf006]

Hi all,

I have a simmilar problem with LibreOffice, I'm trying to sum the hours that I've spend but the SUM of time is incorrect and I can't seem to fix it. Any help will be really appreciated.
I'm adding my file as attachment.

[Villeroy]

Hi all,

I have a simmilar problem with LibreOffice, I'm trying to sum the hours that I've spend but the SUM of time is incorrect and I can't seem to fix it. Any help will be really appreciated.
I'm adding my file as attachment.

Apply number format [HH]:MM

[Zizi64]

And use the Cell styles instead of the direct (manual) formatting.

And always work in the native, International Standard ODF fileformats.