Excel Überstunden und Minusstunden Tabelle möglich?
Hallo!
ich möchte mit Excel eine eigentlich sehr simple Tabelle erstellen um Überstunden bzw Minusstunden zu dokumentieren:
3 Spalten: 1. Spalte Datum 2. Spalte Überstunden 3. Spalte Minusstunden
Wenn ich jetzt z.B. 2 Überstunden und 3 Minusstunden an zwei Tagen habe, möchte ich dass mir Excel einen "Saldo" von - 1 Stunde anzeigt. Es muss aber auch möglich sein bspw 20 Minuten Überstunden einzugeben, also 00:20 h
Ich scheitere allerdings schon daran, Minusstunden also negative Zeitwerte anzuzeigen.
Hat da jemand eine Idee wie ich das am besten lösen könnte? Ich habe Excel 2016
VG
Thomas
7 Antworten
Mit einem kleinen Klimmzug gehts:
Gib Deine Zeiten als Text ein (also zB '8:40 oder in eine VORHER als Text formatierte Zelle) und werte Bereiche aus mit
=Text(Summenprodukt(ZelleOderBereich*1);"[h]:mm:ss"))
- Hintergrund:
- In xl wird ein Text, der einer Zahl gleicht, mit *1 oder +0 in eine solche umgewandelt.
- Summenprodukt liefert formatlose Zahlen für die Zwischenergebnisse, die auch negativ sein können.
Ich kann das nur in Libre Office testen, das ohnehin auch negative Zeiten zulässt.
Es könnte sein, dass xl schon bei =Format(was Negatives;"Zeitformat") muckt, in diesem Fall müsste die Fkt ein kleinwenig komplizierter ausfallen. Aber probier erst mal das hier und melde Dich ggf nochmal innerhalb dieser Frage!
Hab jetzt doch gleich noch die Alternative erstellt:
=WENN(Summenprodukt(Bezug*1)>0;Text(Summenprodukt(Bezug*1);"[h]:mm:ss");"-"&Text(-Summenprodukt(Bezug*1);"[h]:mm:ss"))
ist zwar ein wenig komplizierter, aber keine Hexerei und sollte auch für xl keinerlei Problem darstellen. Du musst nur wissen, dass Du genaugenommen nicht mit Zeiten, sondern mit Texten in Zeitformatdarstellung arbeitest.
Für die Summenproduktformeln darf natürlich keine Text-formatierte Zelle verwendet werden, da käme nur die Formel als Text zurück.
Die Uhrzeit kann man ganz einfach in eine Zahl umwandeln, indem man sie mit 24 multipliziert. So ergibt 0:45 z. B. 0,75.
Diese Zahlen dann berechnen und bei z. B. 1 Stunde und 25 Minusstunden würde da dann stehen -1,42.
Wenn du diese dann wieder als Uhrzeit anzeigen lassen möchtest, multiplizierst du sie zuerst mit -1 (wenn die Zahl negativ sein sollte) und dann dividierst du das Ergebnis mit 24 (die Zelle mit diesem Ergebnis muss dann natürlich als Uhrzeit formatiert sein).
? Die Spalte muss natürlich ein Zahlenformat aufweisen. 0:20 ist aber kein Zahlenformat. Das bedeutet, Du musst die Zeit in Dezimalstunden anzeigen also beispielsweise 0,25 für 15 Minuten.
Zum Selbermachen einer Tabelle mit einem VBA-Code mangels Anhangsmöglichkeit bei Gutefrage.net (der erstellt eine Tabelle, die dann selbst kein VBA benötigt):
Sub BaueDateiMitNegativenZeiten() 'Code erstellt einmalig Tabelle (diese ist dann ohne VBA)
Workbooks.Add xlWorksheet
[F:F].HorizontalAlignment = xlRight
[A:A].NumberFormat = "m/d/yyyy"
[B:F].NumberFormat = "[h]:mm"
[A1:F1] = Split("Datum Start Ende Soll Ist Saldo"): [F2] = 0
[D3:E9].FormulaArray = "={8,9;8,6;8,7;8,10;6,9;8,7;8,3.5}/24": [D3:E9] = [D3:E9].Value
a = "IF(CODE(R[-1]C)=45,-MID(R[-1]C,2,9),R[-1]C)"
[F3:F9] = "=TEXT(ABS(RC[-1]-RC[-2]+" & a & "),IF(RC[-1]+" & a & "<RC[-2],""-"",)&""[h]:mm"")"
End Sub
Geht nur wenn du auf die 1904 Datumswerte umstellst. Kann aber sein dass dir hierbei alle Daten die bereits eingetragen wurden zerschossen werden.