Welche Formel für Arbeitszeiten in Exceltabelle?
Hallo.
Ich bin schon lange am googlen und tüfteln aber bekomme es nicht hin.
Ich möchte eine in Excel mit Hilfe einer Formel Arbeitszeiten automatisch ausrechnen lassen. Dabei soll die geleistete Zeit als Dezimalzahl angezeigt werden auf der Basis einer Minuten-Staffelung. Volle Stunden werden ganz angerechnet, die Minuten mit folgender Staffelung:
0-4 Minuten = 0,0 Gutschrift
5-8 Minuten = 0,08 Gutschrift
9-14 Minuten = 0,23 Gutschrift
15 - 29 Minuten =0,25 Gutschrift
30-44 Minuten = 0,50 Gutschrift
45-59 Minuten = 0,75 Gutschrift
- Beispiel: jemand hat von 15:00 bis 16:15 gearbeitet. Dann soll die Formel als Ergebnis 1,25 Stunden auswerfen.
- Beispiel: jemand hat von 15:00 bis 16:05 gearbeitet, dann soll die Formel 1,08 als Ergebnis auswerfen.
- Beispiel: jemand hat von 15:00: bis 17:58 gearbeitet, dann sol die Formel 2,75 auswerfen
- Beispiel jemand hat von 15:45 bis 16:15 gearbeitet, dann soll die Formel 0,5 auswerfen.
Ich bin über jede Hilfe und jeden Ansatz dankbar.
Ich selbst hatte eine Tabelle erstellt mit der o.g. Staffelung und dann versucht mit einen Sverweis zu arbeiten: z.b.
10:00 - 11:15, dann 16-15= 1 Stunde
15 - 0 = 15 Minuten
Sverweis auf 15-29 Minuten = 1,25
geht aber nicht auf, wenn stundenübergreifend keine volle stunde gearbeitet wurde: zb. 15:45 - 16:15. Mit meiner Formel würde dann 16-15 gerechnet und eine volle Stunde angerechnet und 15 - 45 bei den Minuten und so ein Negativwert entstehen, der nicht in der verwiesenen Tabelle steht.
2 Antworten
Ich hab das mal nachgebaut. Dabei hat sich wieder einmal gezeigt, dass Zeiten-Berechnungen in Excel tückisch sein können.
In A und B sind die Minutengrenzen und der jeweilige Faktor. Dann habe ich eine Arbeitszeittabelle ab D erstellt mit verschiedenen Beispielzeiten und Stunden und Minuten per Formel extrahiert. Da Excel Zeiten als Zahlen behandelt, aber gelegentlich an der Genauigkeit scheitert, können dabei eigenartige Ergebnisse rauskommen, wie z.B. in H3. Ähnliches gilt für I8, wo der Sverweis mit 45 eigentlich auf 0,75 verweisen sollte, aber 0,5 ausgibt. Durch eine Formelauswertung bin ich darauf gekommen, dass die 45 in H8 genau eine 44,99999999... ist, deshalb wird falsch verwiesen.
Darum habe ich diese Spalte im unteren (korrigierten) Bereich gerundet. Die Formeln:
F15: =E15-D15
G:15: =GANZZAHL(F15*24)
H15: =RUNDEN(REST(F15*24;1)*60;0)
I15: =G15+SVERWEIS(H15;A:B;2;1)
Natürlich kann man diese einzelnen Bestandteile auch direkt in eine lange Formel packen, ohne die ganzen Spalten. Die habe ich hier nur zum Verständnis und der Übersichtlichkeit angelegt.
Vielen lieben Dank!!!! Das Ding hat mich richtig nach vorne gebracht. Ich habe auf deiner Basis versucht die Tabelle kompakter zu gestallten. Ist mir nur semi gelungen. ;-) Ich habe eine Tabelle erstellt in dem die gearbeitete Uhrzeit als Dezimalzahl dargestellt wird. Dann einen sverweis gelegt zu einer Staffelung die alle Dezimalstellen zu den Minuten 0-59 beinhaltet mit der jeweiligen Zeitgutschrift. Das Problem mit der Rundung bestand natürlich auch hier. War die Arbeitszeit 1:10 wurde die Zeitgutschrift von 5 Minuten genommen und nicht von 10 Minuten. Angezeigt wurde nämlich eine Dezimalzahl von 0,17, aber im Hintergrund waren es 0,16xxxx...... Also die 10 Minuten mit der Dezimalzahl 0,16 und 0,17 hinterlegt. Aber auf all das bin ich nicht selbst gekommen, sondern dank dir. Das erleichtert mir mega meine Arbeit. Fühl dich social-distancing-mäßig gedrückt. Gruß
das kannst du über eine Verschachtelung von Wenn-dann-sonst realisieren
wenn(Minuten <=4; gutschrit 0; wenn (und(minuten>5; minuten<=8 ); gutschrift 0,08; wenn (... usw)))
ich weiss was du meinst, aber dazu fällt mir grade auch nichts sinnvolles ein.
Du könntest die Zahlenwerte "1" und "0,24" in separate Zellen schreiben und den Zellbezeichner in die Formel übernehmen - dann muss du nicht die Formel ändern, wenn Du andere Grenzwerte benötigst.
Das hilft mir weiter. Ich taste mich langsam ran. Kannst du mir evtl. die Bezeichnung einer Dezimalzahl vor und hinter dem Komma in einer Formel nennen? Beispiel in A1 steht 1,25. Formel: wenn(Zahl hinter dem Komma>0,24;...) Die Bezeichnung für "Zahl hinter dem Komma und Zahl vor dem Komma und sie in der Formel zu differenzieren.