Univeselle Formel zur Arbeitszeitauswertung!
08.08.2024 20:52:10
daniel
Hallo Alex
ich zeige dir jetzt mal eine Basisformel, mit der du alle deine Arbeitszeitauswertungen relativ einfach machen kannst (wenn du mal das Grundprinzip verstanden hast)
Die Grundformel sieht so aus, die Beispielformel ist geschrieben für die Zelle E7 deiner Beispieldatei:
=LET(
von;$A7+$B7;
bis;$A7+$C7+($C7$B7);
f;24*60;
X;SEQUENZ((bis-von)*f;;von*f;1)/f;
SUMME(...))
dabei ist
- von: der Arbeitsbeginn mit Datum und Uhrzeit
- bis: das Arbeitsende mit Datum und Uhrzeigt. Die Klammer sorgt dafür, dass das Arbeitsende am Folgetag liegt, wenn die Uhrzeit kleiner ist als die Uhrzeit des Arbeitsbeginns (hierzu müssen wir wissen: ein Tag = 1, ebenso ist ein WAHR aus einem Vergleich eine 1 und FALSCH eine 0, das brauchen wir später noch)
- f: ist der Umrechenfaktor der Uhrzeit, damit eine Minute = 1 ist.
- X: die Funktion Sequenz erstellt hier eine Schleife über jede Minute der Arbeitszeit. Damit können wir deine Arbeitszeit Minuten genau auswerten.
das ist die Vorbereitung diese ist immer gleich.
in der Summe machen wir dann die Auswertung.
wir prüfen also über das X für jede Minute unserer Arbeitszeit, ob eine Bedinung zutrifft oder nicht. Wir haben schon gelernt, dass ein WAHR wie 1 und eine FALSCH wie 0 gewertet wird, also können wir mit einfachen Bedingungsprüfungen unsere Auswertungen machen in dem wir dieses für jede Minute Aufsummieren.
wenn du also die Nachtminuten Zählen willst, dann wäre das
für die Zelle E7:
...;Summe((Rest(X;1)>=E$2)+(Rest(X;1)<E3)))
wenn du die Tagsminuten zählen willst ,dann
für die Zelle G7:
...;Summe((Rest(X;1)>=E$2)*(Rest(X;1)<E3)))
zur Erläuterung:
das REST(X;1) reduziert den Wert auf die Uhrzeit (Nachkommastelle) , so dass du ihn mit den Werten in Zeile 2 und 3 vergleichen kannst.
wenn zwei Bedingungen gleichzeitig zutreffen müssen, dann ist das UND (Tag ist nach 6:00 UND vor 20:00) und ein UND wird in der Berechnung durch * ausgedrückt
wen eine oder die andere Bedingung zutrifft, dann ist das ODER (Nacht ist nach 20:00 ODER vor 6:00), hier wird ein + verwendet,
noch ein Beispiel, du willst die Sonntagsminuten zählen:
...;Summe((WochenTag(X;2)=7)*1))
die *1 braucht man, weil ein WAHR nur in einer direkten Berechnung in eine 1 gewandelt wird. steht das WAHR allein, bleibt es WAHR und wird von der Summe ignoriert. Durch die Multiplikation wird es in eine 1 gewandelt
noch ein Beispiel, hier werden die Samtag-Nacht stunden gezählt :
...;Summe((Wochentag(X;2)=7)*((Rest(X;1)>=E$2)+(Rest(X;1)<E3))))
Feiertagsminuten würde man so zählen:
...;Summe(ZählenWenn($AB$2;$AB$14;Ganzzahl(X))))
das Ganzzahl entfernt die Uhrzeit und lässt nur das Datum stehen.
allerdings musst du in der TAbelle Spalte AB echte Datumswerte eintragen, keine Texte.
wenn du das Prinzip verstanden hast, sollte es dir einfach fallen, deine Zeitauswertungen per Formel zu erstellen
wenn du statt der Minuten Stunden oder echte Uhrzeiten haben willst, dann das Ergebnis noch durch 60 bzw 1440 teilen.
Gruß Daniel