Kann ich in Excel eine Formel erstellen, welche mir durch eingeben von Texten (z.B. Früh oder Spät) meine Arbeitszeiten einträgt?
Hallöchen, Gibt es eine Excel Formel, welche es mir ermöglicht durch das eingeben des Wortes Früh oder Spät mir meine Arbeitsstunden direkt ausrechnet? Wenn die Frühschicht z.B. von 08:00 bis 16:00 Uhr ist, die Spätschicht von 14:00 bis 22:00 Uhr und die Mittelschicht von 12:00 bis 20:00 Uhr ist. Im Optimalfall sollte noch eine Formel zum Abzug der Pause (z.B. -1/48) drinne stehen.
Vielen Dank im Voraus!
LG JR
4 Antworten
Mein Vorschlag:
In Spalte A (formatiert als Text) trägst du ein wann deine Schicht beginnt (Früh, Mittel, Spät)
In Spalte B (formatiert als Uhrzeit) =(WENN(A2="Früh";"8:00";WENN(A2="Mittel";"12:00";WENN(A2="Spät";"14:00";""))))
Damit steht in Spalte B jeweils die Uhrzeit wann du anfängst. Ich habe extra Zelle A2 genommen, damit du als Überschrift für B noch "Anfang" nehmen kannst.
In Spalte D (formatiert als Uhrzeit) =(WENN(A2="Früh";"16:00";WENN(A2="Mittel";"20:00";WENN(A2="Spät";"22:00";""))))
Dann steht da die Uhrzeit, wann die Schicht endet. Überschrift "Ende".
In Spalte C würde ich händisch einfach die Pause eintragen.
Spalte E wäre dann =(D2-B2)*24-C2
Damit wird in E ausgerechnet wie lange du gearbeitet hast.
ich beziehe mich auf die (richtige) Antwort von XurijB.
Da nur EIN Buchstabe zutreffen kann, geht es auch -leichter verständlich bzw korrigierbar- unverschachtelt (die Ergebniszelle -hier B2- muss als Uhrzeit formatiert werden):
=WENN(A2="F";"8:00"*1;0)+WENN(A2="M";"12:00"*1;0)+WENN(A2="S";"14:00"*1;0)+WENN(A2="N";"20:00"*1;0)
Hab das jetzt vorsichtshalber nochmal ausprobiert und stelle fest, dass es auch ohne *1 geht (nachvollziehbar, da die Zeit-Strings in einem Rechenzusammenhang stehen, nämlich "Zeitstring"+0, da das aber in vielen anderen Zusammenhängen notwendig ist, um den Wert als Uhrzeit rechenbar zu bekommen, behalte ich es bei - schadet ja nichts. Lernen von vielen Spezialfällen verdrängt nur Wichtigeres aus dem Hirn).
Kannst auch Kleinbuchstaben f, m, s, n eingeben (Bin zu faul, immer die Umschalttaste zu bedienen).
Fürs Arbeitsende die entsprechenden anderen Zeiten eingeben (zB in C2) oder -einfacher- in C2: =B2+"8:00"+"0:30"
Die Arbeitszeit abzüglich Pause errechnet sich zB in D3 mit
=C2-B2-1/48
Das wären aber nach Deinen Angaben 7 1/2 - h-Schichten, wenn es 8-h-Schichten sind, musst du das Arbeitsende um 1/2 h verlängern (einfach zur Arbeitsende-Formel in C2 +1/48 anhängen, dann kannst du die genannten Arbeitsende-Zeiten beibehalten)
Den Nachtschicht-Teil kannst du auch weglassen, habe ich nur vorsichtshalber angefügt-. Kann Arbeit über Mitternacht auftreten, dann zur Arbeitszeit-Auswertung diese Formel verwenden
=REST(C2-B2;1)-1/48
Die funktioniert auch untertags.
[=Rest(Ende-Anfang;1Tag)-0:30, ist schwer zu erklären bzw nachzuvollziehen, warum. siehe Verhalten von Rest() bei negativen Zahlen]
Kein Bug: Verweis hat im Ggs zu SVerweis kein ;0/1-Argument und verlangt deshalb (steigend) geordnete Listen!
(vermutlich uralter Befehl mit eben noch eingeschränktem Funktionsumfang)
Danke @lamiam.
Das war mir bis jetzt nicht bewusst, zumal es im Assi nicht erwähnt wird (wohl aber in der Hilfe steht):
Merk ich mir (hoffentlich!)
Das ist relativ einfach, allerdings widersprechen sich deine Angaben ein wenig:
Was willst du genau? Eine Zelle, in der die Arbeitsstunden drinnen stehen? das wären doch in jedem Fall 8 (minus Pause)
Oder zwei Zellen, in die Arbeitsbeginn und Arbeitsende eingetragen werden? In diesem Fall: wovon willst du hier die Pause abziehen?
wenn die Schichtdauer 7:30 wäre, ginge das widerspruchsfrei. Bin aber auch skeptisch, ob das so gemeint war!
Nein, auch das ergibt keinen Sinn: wenn er Uhrzeit von Beginn/Ende der Arbeitszeiten haben will, So kann man die Dauer der Pause zusätzlich angeben, aber nirgendwo abiehen.
Wenn er die Arbeitsstunden für jede Schicht will, so kann zwar die Pausenzeit abgezogen werden, aber die Auswertung, welche Schicht es ist, ist überflüssig, da es ja immer 8 Stunden (minus Puse) sind.
Vielleicht will er aber auch beides, so hat er es aber nicht artikuliert...
Klar geht das. Hast du dich schon mal mit der Wenn-Funktion auseinander gesetzt?
Oder, analog zu Deiner Formel:
=WENN(A2="F";"8:00"*1;0)+WENN(A2="M";"12:00"*1;0)+WENN(A2="S";"14:00"*1;0)+WENN(A2="N";"20:00"*1;0)
und den dort aufgeführten Werten auch:
=VERWEIS(A2;{"F";"M";"N";"S"};{"8:00";"12:00";"20:00";"14:00"})
Interessanterweise klappt hier der VERWEIS nur richtig, wenn die Buchstaben alphabetisch sortiert sind. Also das N vor dem S.
Das halte ich für eine Bug. Oder?
Auf jeden Fall werde ich in Zukunft mit dem VERWEIS vorsichtig umgehen.