Arbeitszeiten addieren / summieren

[Mau]

Liebe ExpertInnen,

für meine Arbeitszeiten habe ich mir in Calc eine Art Stoppuhr gebastelt, die wie folgt funktioniert:

In Sp. C der Tabelle 1 (ich habe sie "Stoppuhr" genannt) wird ein x eingetragen; daraufhin erscheint in Spp. A und B das aktuelle Datum und die aktuelle Uhrzeit.
Wenn ich eine Pause mache, setze ich ein x in Sp. J; daraufhin wird in Sp. I die aktuelle Uhrzeit eingetragen.
Wenn ich die Pause beende, setze ich ein x in Sp. L, woraufhin in Sp. K die aktuelle Uhrzeit für das Pausenende eingetragen wird. Daraufhin wird in Sp. M die Pausendauer berechnet. Das geht nach rechts weiter, so dass insgesamt drei Pausen verarbeitet werden können.
Sp. H addiert die Pausenzeiten (Spp. M+R+W).
Wenn die Arbeitssitzung beendet ist, setze ich in Sp. E ein letztes x, so dass in Sp. D die aktuelle Uhrzeit erscheint.
Daraufhin errechnet Sp. F aus Spp. B und D die Gesamtzeit, und Sp. G zeigt die Nettoarbeitszeit an (also Sp. F - Sp. H).
In Sp. X trage ich ein Stichwort für die jeweilige Tätigkeit ein (z.B. "Wörterliste", "Korrespondenz" usw.).

Nun habe ich eine zweite Tabelle dahinter angelegt (Name: "Summen").
Dort trage ich in Sp. B jedes Stichwort aus $Stoppuhr.X... jeweils einmal ein.
Sp. C sollte mir dann mit folgender Formel die Nettoarbeitszeiten für die verschiedenen Tätigkeiten summieren (Beispiel aus Zeile 4):
=SUMMEWENN($Stoppuhr.X$2:X$100;B4;$Stoppuhr.G$2:G$100)
Ergebnis 1: Drei Rauten - ###. Dabei spielt die Spaltenbreite keine Rolle.
Daraufhin wähle ich den Befehl Zellen formatieren. Eingestellt ist das Format Standard. Ich stelle um auf Zahl.
Ergebnis 2: Es wird eine sechsstellige Zahl ausgegeben, mit drei Dezimalstellen.
Ich vermute, es handele sich um Sekunden, und ergänze die Formel vor Ergebnis 1 wie folgt:
=SUMMEWENN($Stoppuhr.X$2:X$100;B4;$Stoppuhr.G$2:G$100)/60/60
Ergebnis 3: Es wird eine Gesamtzahl von 69,57 ausgegeben.
Ich vermute zunächst, es handele sich um die Stunden. Eine "händische" Kontrollrechnung ergibt aber, dass die tatsächliche Nettoarbeitszeit wesentlich kürzer war. Genau die möchte ich mir mit der Formel natürlich ersparen.

Ich habe dann noch mit ZEITWERT experimentiert, aber dabei ist auch nichts Brauchbares herausgekommen.

Was tun?

Gruß,
Mau

[oleander]

Was tun?

Mal 'ne Mustertabelle hochladen

[Mau]

OK:

[paljass]

Hallo Mau,
da wirst du wohl deine ganze Tabelle noch mal überarbeiten müssen. Warum? Jetzt() ergibt halt nicht nur die Uhrzeit, sondern auch das Datum. Wenn du nun -wie in Zelle D31- händisch eine Uhrzeit eingibst, erhält die ein ganz anderes Datum als die automatisch generierte Uhrzeit. Da aber mit dem ganzen Wert (Datum Uhrzeit) gerechnet wird, wird das Ergebnis falsch.
Ich hab mal spaßeshalber die Spalten B, D, F, G und H auf Datum und Uhrzeit formatiert; da geht es mit den Datumswerten wild durcheinander, mal wird das Jahr 2013 angezeigt, dann mal 1999, auch 1986 ist vertreten. Deine Ergebnisse der Aufsummierung können daher nur falsch sein.
Ein Anfang wäre mal, aus den automatisch eingefügten Jetzt()-werten in Spalte F die Zeitwerte zu entnehmen. Das geht z.B. mit =ZEITWERT(TEXT(B2;"HH:MM")).
Weiteres zum Rechnen mit Datums- und Uhrzeitwerten findest du hier.

Gruß
paljass

[oleander]

paljass hat Recht mit dem "unglücklichen" Format der von JETZT gelieferten Daten. LO weist ja auch darauf hin, daß die Zellen dann als ZAHL formatiert werden sollen. Danach bemerkt man in der Tabelle Ausreisser, die ich mir nicht erklären kann. Evtl. hängt es mit den zirkulären Referenzen zusammen?

Ich habe neben G mal zwei Spalten eingefügt. In H steht jetzt der in Text umgewandelte Inhalt der Spalte G, und in I der in einen Wert zurückverwandelte Inhalt von H.

Diese zwei Hilfsspalten sind nötig, denn wenn man den Vorgang in eine Formel packt, bekommt man wieder den alten Fehler. Selbstverständlich können diese Spalten aber irgendwohin verschoben werden, wo sie das Aussehen der Tabelle nicht stören.

Mit der bekannten Formel SUMMEWENN findet sich dann (in meinem Beispiel) die richtige Stundenanzahl in Zelle I65.

[Mau]

Liebe(r) paljass, liebe(r) oleander,

ganz herzlichen Dank für die ausführliche Auseinandersetzung mit meinem Problem.

Ich war anscheinend schon dicht an der Lösung dran; ich hatte bereits eine Hilfsspalte mit der Formel TEXT eingerichtet, blieb dort aber hängen, weil die Ergebnisse nicht passten. Durch oleanders Version habe ich gemerkt, dass die Eingabe nicht TEXT(G2;"HH:SS") heißen kann, sondern TEXT (G2;"HH:MM") lauten muss.

Das muss einem aber auch gesagt werden, dass die nächstkleinere Einheit nach der Stunde die Minute ist - finde ich...

Bereits die zwei Hilfsspalten TEXT(...) und WERT(...) brachten das richtige Ergebnis.

Auf jeden Fall:
Ich wurde geholfen!!

Gruß,
Mau

[paljass]

Hallo allerseits,
irgendwie will sich beim Betrachten der Tabelle von oleander bei mir kein Hochgefühl einstellen.

In H steht jetzt der in Text umgewandelte Inhalt der Spalte G

Stimmt!

und in I der in einen Wert zurückverwandelte Inhalt von H.

Stimmt nicht! Du hast ja den "Text" mit 24 multipliziert, also kann H nicht gleich I sein.
Zu erkennen ist das, wenn du in Spalte I die Wert mal ins Format HH:MM packst. Dann steht nämlich in I2 der Wert 1:36 und das ist halt das Ergebnis von Spalte H (0:04) mal 24. Also ist H <> I, wodurch in der Aufsummierung der Spalte I auch 1346:48 Stunden rauskommen und nicht - wie richtig wäre - 56:07 Stunden.
Fazit: In Spalte I darf nicht mit 24 multipliziert werden.

Diese zwei Hilfsspalten sind nötig, denn wenn man den Vorgang in eine Formel packt, bekommt man wieder den alten Fehler.

Das ist Quatsch! Wenn ich innerhalb einer Formel einen Wert in einen Text umwandele, um ihn anschließend zurück zu wandeln, ist der alte Fehler natürlich durch die Umwandlung in Text ausgemerzt. Die Formel lautet daher einfach

Code: Alles auswählen

=Wert(Text(G2;"HH:MM"))

Nichts desto trotz würde ich bei meinem Vorschlag

Ein Anfang wäre mal, aus den automatisch eingefügten Jetzt()-werten in Spalte F die Zeitwerte zu entnehmen. Das geht z.B. mit =ZEITWERT(TEXT(B2;"HH:MM")).

bleiben, weil dann einfach in den anderen Spalten auch mit den eigentlich richtigen Werten gerechnet wird, denn wenn ich eine Berechnung mit falschen Werten mache, kann ich nie sicher sein, das schlussendlich der richtige Wert rauskommt.

Gruß
paljass

[Mau]

Liebe(r) paljass,

ja, die Idee, die Formeln der beiden Hilfsspalten so zu verbinden, wie du es getan hast, war mir inzwischen auch gekommen.

Aber deine Ansicht, mit der ZEITWERT-Funktion könne man ein genaueres Ergebnis bekommen als mit der WERT-Funktion, kann ich nicht bestätigen. Die ZEITWERT-Funktion erbrachte überall das gleiche Ergebnis wie die WERT-Funktion, und die Summierung auch dort das gleiche Ergebnis wie beim "händischen" Zusammenzählen.

Euch beiden noch einmal Dank,
Mau

[oleander]

und in I der in einen Wert zurückverwandelte Inhalt von H.

Stimmt nicht! Du hast ja den "Text" mit 24 multipliziert, also kann H nicht gleich I sein.
Zu erkennen ist das, wenn du in Spalte I die Wert mal ins Format HH:MM packst. Dann steht nämlich in I2 der Wert 1:36 und das ist halt das Ergebnis von Spalte H (0:04) mal 24. Also ist H <> I, wodurch in der Aufsummierung der Spalte I auch 1346:48 Stunden rauskommen und nicht - wie richtig wäre - 56:07 Stunden.
Fazit: In Spalte I darf nicht mit 24 multipliziert werden.

Bitte nicht so voreilig sein: Mittels JETZT erhaltene Daten enthalten Zahlen, aus denen durch die Zellformatierung gewünschte Inhalte extrahiert werden (in diesem Fall aus Datum und Uhrzeit die Stunden und Minuten), was aber nur die sichtbare Darstellung und nicht den Inhalt betrifft.

Natürlich entspricht der Inhalt von H dem von I. Der Begriff Umwandlung gilt sowohl für das Format als auch für die Einheit. Die "Zeit" wurde in diesem Fall von Tagen auf Stunden umgewandelt.

Diese zwei Hilfsspalten sind nötig, denn wenn man den Vorgang in eine Formel packt, bekommt man wieder den alten Fehler.

Das ist Quatsch! Wenn ich innerhalb einer Formel einen Wert in einen Text umwandele, um ihn anschließend zurück zu wandeln, ist der alte Fehler natürlich durch die Umwandlung in Text ausgemerzt. Die Formel lautet daher einfach

Code: Alles auswählen
=Wert(Text(G2;"HH:MM"))

Sorry - ich hab's doch selber vorher ausprobiert (weil ich mir die zweite Spalte auch lieber erspart hätte).

Und daß meine Lösung (übrigens mit Mau's manuellem Ergebnis übereinstimmend) funktioniert, erkennt man auch an der Formel in I65.


Es grüßt der
olle eander

[balu]

Hallo Leute,

sache moal, seht ihr denn alle nicht das in den Dateien ein "Rundungsfehler" existiert??? Der ist wohl nicht sehr groß, aber das läppert sich dann doch zusammen.

Schaut euch doch nur mal zum Bleistift die Zeilen 2 bis 8 an, und dort besonders die Spalten B, D und F!
Nehmen wir doch mal B2, D2 und F2.
23:16 23:21 00:04
Seit wann sind denn 23:21 minus 23:16 gleich 4 Minuten!?
Adam Riese würde garantiert dazu sagen: "Bei mir ergibt das immer noch 5 Minuten."

Es wird wohl von Euch nicht berücksichtigt, das =JETZT() nicht nur das Datum, sondern auch die Uhrzeit in Stunden, Minuten und Sekunden auswertet. Und durch aufruf dieser Funktion werden diese Daten intern vollständig verarbeitet, auch wenn nur durch die Formatierung Stunden und Minuten angezeigt werden.

Lassen wir mal das Datum aussen vor, und nehmen uns die Sekunden vor. So kann unter ungünstigen Bedingungen eine Zeitdifferenz von durchaus 1 Stunde zustande kommen, wenn pro Zeile 1 Minute differenz existiert und das bei 60 ausgefüllten Zeilen.

Egal ob ihr mit 1, oder mit 2 Hilfsspalten arbeitet, oder meinetwegen in Spalte F mit herausfiltern und umwandeln der Zeitwerte hantiert, so geht ihr alle den falschen Weg. Richtig ist nämlich; gleich beim erzeugen der Zeiten an der dementsprechenden Stelle die Zeiten richtig auszuwerten. Und das geht mit folgender Formel zum Bleistift in B2:

Code: Alles auswählen

=WENN(C2="";"";WENN(ZELLE("TYPE";B2)="v";B2;WERT(TEXT(JETZT();"HH:MM"))))

Und demzufolge ist die bisherige Formel z.B. in B2
=WENN(C2="";"";WENN(ZELLE("TYPE";B2)="v";B2;JETZT()))
einfach falsch, denn es werden immer die Sekunden intern voll mit verararbeitet. Jedoch durch die Formatierung werden diese nicht angezeigt, und auch sonst nicht weiter berücksichtigt. Und das lässt sich nur durch meinen Formelvorschlag vermeiden.

Wie gesagt, als Beispiel, da ja diese Formel sinngemäß noch in anderen Spalten vorkommt. Also müssen in den anderen Spalten die dortigen Formeln dementsprechend auch noch geändert werden.



Jetzt mal speziell an Mau.

Code: Alles auswählen

=WENN(ISTLEER(J2);+F2;+F2-H2)

Wer hat denn dir dies beigebracht?
Warum machst Du das nicht so rum?

Code: Alles auswählen

=WENN(ISTLEER(J2);F2;F2-H2)

Also verzichte auf führende Plus (+). Oder rechnest Du von Hand auf einem Blatt Papier genau so .



Gruß
balu