Office: (Office 2019) Zeiten rechnen mit Ausnahmen

Hallo zusammen,
ich muss von unseren Außendienstmitarbeitern Einsatzzeiten in der Rufbereitschaft für unsere Personalabteilung ausrechnen. Die Datei (hier eine Beispieldatei hochgeladen) dient also dann der Berechnung der Zeit, die die Mitarbeiter auf's Arbeitszeitkonto zusätzlich drauf bekommen.
Auf dem Tabellenblatt "Erfassung" werden die Daten eingegeben, wenn ein Einsatz war, in "Berechnung" wird dann für jeden Tag ausgerechnet, wie viel Zeitgutschrift erfolgt. Das Problem ist, dass laut Tarifvertrag der erste Einsatz pauschal mit 3 Stunden gerechnet werden muss, außer wenn er länger als drei Stunden gedauert hat - dann tatsächliche Zeit. Hat ein Mitarbeiter am selben Tag einen zweiten, dritten ... Einsatz, soll wieder die tatsächliche Zeit abgerechnet werden.
Ich habe jetzt schon viel versucht - aber es klappt nicht - bitte helft mir mit den Formeln ...

 

Hallo Tomam,

folgende Formeln:
In H11:
=WENNFEHLER(MAX(AGGREGAT(15;6;Erfassung!D$5:D$99/(Erfassung!B$5:B$99=A11);1)-AGGREGAT(15;6;Erfassung!C$5:C$99/(Erfassung!B$5:B$99=A11);1);1/8);0)
In I11:
=WENNFEHLER(AGGREGAT(15;6;Erfassung!D$5:D$99/(Erfassung!B$5:B$99=A11);2)-AGGREGAT(15;6;Erfassung!C$5:C$99/(Erfassung!B$5:B$99=A11);2);0)
in F11 analog da dann nur die zwei 2en durch eine 3 ersetzen.

und alle Formeln ziehend nach unten kopieren.Als Zellenformat würde ich benutzerdefiniert: h:mm;;; einsetzen.

 

oh vielen Dank
in meiner Beispieldatei hat es super funktioniert - ich werde das mal in meine Echtdatei übertragen und schauen ob das auch so toll klappt ...
Danke nochmal ...

 

Hi,

mit den neuen Möglichkeiten eines aktuellen Excel könnte es so aussehen:

Zunächst einmal habe ich aus beiden Tabellen Strg-T-Tabellen gemacht, damit man die Zeilenzahl nicht anpassen muss.
Dann kommt für den 1. Einsatz die Formel
C11: =WENNFEHLER(MAX(ZEIT(3;;);ZEILENWAHL((FILTER(Tabelle1[Tatsächliche Einsatzzeit];(Tabelle1[MA]=[@MA])*(Tabelle1[Datum]=[@Datum])));1));0)
2. Einsatz:
D11: =WENNFEHLER(ZEILENWAHL((FILTER(Tabelle1[Tatsächliche Einsatzzeit];(Tabelle1[MA]=[@MA])*(Tabelle1[Datum]=[@Datum])));2);0)
3. Einsatz die letzte 2 durch 3 ersetzen. Mit dem Zellformat [hh]:mm;;0;@ sieht es dann exakt so aus, wie beim Wunsch angegeben. Allerdings würde ich eher [h]:mm;;; verwenden.

 

Wow - Aufgabe somit Doppelt gelöst ->Daumen hoch!
Da ich nun schon die Version von Werner in meiner Real-Datei eingearbeitet habe (funktioniert super!) würde ich die dann auch gerne drin lassen. Vielen Dank euch!

 

Nun ja, meine Formeln dürften in Excel 2019 wohl eher nicht funktionieren, da das mWn weder Filter() noch Zeilenwahl() kennt. Ich habe diese nur aufgeführt, weil es vielleicht für andere Mitleser interessant sein könnte.

 

Hallo nochmal
ich bin gerade fertig geworden und da ist mir aufgefallen, dass ich eine Variable vergessen habe
Bezogen auf die Beispieldatei, Reiter "Erfassung", Spalte F, da würde noch "ja" oder "nein" eingetragen sein. Die Formel soll nur die Werte mit "ja" berücksichtigen.
Der Hintergrund aus der Realität ist, dass es Rufbereitschaftszeiten gibt und reguläre Arbeitszeiten. Wenn ein Einsatz innerhalb der Rufbereitschaft war, wird dieser mit "ja" gekennzeichnet, sodass der Mitarbeiter seine zusätzliche Zeitgutschrift bekommt. Sonst würde er ja alle Einsätze bekommen und somit die während der regulären Arbeitszeit doppelt ... Sorry mein Fehler
Könnte ich bitte noch die Erweiterung der Formel bekommen?

 

Ich hätte jetzt schon versucht, die Daten vergleichen zu lassen und wenn ein ja dabei steht, dann die Formel von Werner - hat aber nicht geklappt - da krieg ich entweder einen Fehler oder Null

 

Hallo,

für Excel365 müsste folgende Formel funktionieren:


Sie erzeugt folgendes Resultat:

Ich habe hier bereits die gewünschte Zusatzspalte "Bereitschaft"="ja"/"nein" als 6. Spalte im Arbeitsblatt "Erfassung" bzw. Tabelle "tbEinsätze" eingefügt, mit ein paar Nein-Zeilen - deshalb die 3 Leerzeilen.

 

Okay, danke erstmal - aber wie kriege ich das in die Formelzeile oben?

 

Hallo Tomam,

mir ist noch unklar, wo Du das zusätzliche "ja" als Parameter zu stehen hast und wie es genau berücksichtigt werden soll.
Bin allerdings auch gleich wieder offline. Vor 19 Uhr werde ich wohl nicht dazukommen mir es dann noch einmal anzuschauen.

 

Hallo Werner,
ich habe nochmal eine Datei hochgeladen, bei dem die "Ja" und "Nein" eingetragen sind.
zur besseren Sichtbarkeit habe ich die "Nein" beim Wunschergebnis noch mit einem x markiert, damit du besser sehen kannst, welche Einsätze nicht berücksichtigt werden sollen.
Hoffe, du kannst das so verwenden ...

 

Hallo Tomam,

Deine Angaben beantworten aber noch nicht eindeutig meine Fragestellung. Kann es z.B. vorkommen, dass z.B. in Erfassung!F12 ein "nein" steht? Wenn ja, wie soll dann das Ergebnis aussehen?

Deine aktuellen Vorgabewerte erreichst Du mit folgender Formel in H11:

=WENNFEHLER(MAX(AGGREGAT(15;6;Erfassung!$D$5:$D$99/(Erfassung!$B$5:$B$99=$A11)/(Erfassung!$F$5:$F$99="Ja");--LINKS(H$10;1))-AGGREGAT(15;6;Erfassung!$C$5:$C$99/(Erfassung!$B$5:$B$99=$A11);--LINKS(H$10;1));(--LINKS(H$10;1)=1)/8);0)

Diese Formel kannst Du nun sowohl nach unten als auch rechts ziehend kopieren.

 

Mit der manuellen Eingabe von Ja oder Nein definiere ich einfach, ob es sich um einen Einsatz während der Rufbereitschaft gehandelt hat. Da einen Automatismus reinzukriegen halte ich für schwierig, wegen Wochenende, Feiertage usw.
Daher die Manuelle Eingabe. Theoretisch könnte bei jedem Datensatz entweder ein ja oder ein nein dabei stehen. Ich probier mal deine Formel aus ...

 

Hallo,

ich habe dir im Arbeitsblatt "Erfassung" die Tabelle in eine strukturierte (=intelligente) Tabelle namens "tbEinsätze" umgewandelt.

Im Arbeitsblatt "Berechnung" habe ich in Zelle H11 folgende Formel eingefügt:
=WENNFEHLER(MAX(AGGREGAT(15; 6; tbEinsätze[Einsatzende] / ((tbEinsätze[MA] = $B11) * (tbEinsätze[Datum] = $A11) * (tbEinsätze[Einsatz während der Rufbereitschaft?] = "ja")); H$10) - AGGREGAT(15; 6; tbEinsätze[Einsatzbeginn] / ((tbEinsätze[MA] = $B11) * (tbEinsätze[Datum] = $A11) * (tbEinsätze[Einsatz während der Rufbereitschaft?] = "ja")); H$10); WAHL(H$10; 1 / 8; 0; 0)); 0)
und habe diese Formel in den Zellbereich H11:J72 kopiert.

Damit diese funktioniert, habe ich in den Kopfbereich H10:J10 die Werte 1,2,3 mit benutzerdef.Format 0."Einsatz";;; eingefügt.

Die Spalte K mit den Summen enthält ab Zelle K11 die Formel:=SUMME(H11:J11)

Der besseren Übersicht halber zeige ich diese Formel aus H11 nochmals in strukturierter Form:


Datei liegt bei.