Office: Berechnung von Rufbereitschafts-Einsätzen

Hallo zusammen,

ich bin schon am verzweifeln und hab schon viel versucht. Bitte helft mir bei folgendem Problem...

Ich habe eine Excel-Tabelle mit zwei relevanten Blättern:

Blatt „Erfassung“ (Datenquelle)
Jede Zeile entspricht einem Einsatz.

Relevante Spalten:

• Spalte G: Einsatzdauer (Zeitwert)
  
  
• Spalte H: Einsatz-Nr.
  
  • 0 = Einsatz nicht relevant (außerhalb der Rufbereitschaft)
    
    
  • 1 = erster relevanter Einsatz der Rufbereitschaft (Pauschalvergütung)
    
    
  • 2, 3, … = weitere Einsätze derselben Rufbereitschaft (Realzeit)
    
• Spalte J: Einsatzbeginn (Datum + Uhrzeit)
  → Diese Spalte ist die einzige Zeitbasis für die Berechnung
  

Die Daten beginnen ab Zeile 5.

Blatt „Dienstplan“
Jede Zeile entspricht einer Rufbereitschaftsschicht.

Relevante Spalten:

• Spalte D: Beginn der Rufbereitschaft (Datum + Uhrzeit)
  
  
• Spalte H: Ende der Rufbereitschaft (Datum + Uhrzeit, kann am Folgetag liegen)
  
  
• Spalte M: Zeitgutschrift für den ersten Einsatz
  
  
• Spalte N: Zeitgutschrift für weitere Einsätze
  

Die Berechnungen beginnen ab Zeile 10.

Gewünschte Logik
1. Zeitliche Zuordnung
Ein Einsatz aus „Erfassung“ gehört zu einer Rufbereitschaft aus dem „Dienstplan“, wenn:


Dienstplan!D ≤ Erfassung!J ≤ Dienstplan!H

• Dabei enthalten alle beteiligten Zellen Datum + Uhrzeit
  
  
• Rufbereitschaften können über Mitternacht gehen
  

2. Vergütungslogik
Spalte M (funktioniert bereits korrekt)

• Wenn mindestens ein Einsatz mit Einsatz-Nr. = 1
  innerhalb der Rufbereitschaft liegt:
  
  • → pauschal 03:00 Stunden
    
• Wenn kein solcher Einsatz existiert:
  
  • → Zelle bleibt leer
    

Spalte N (Problemfall)

• Es sollen alle Einsätze mit Einsatz-Nr. ≥ 2
  addiert werden,
  
  
• aber nur, wenn sie zeitlich innerhalb derselben Rufbereitschaft liegen
  
  
• Die zu addierende Zeit kommt aus Erfassung!Spalte G
  
  
• Wenn keine solchen Einsätze existieren:
  
  • → Zelle bleibt leer
    

Ziel
Ich suche eine stabile Excel-Formel für Spalte N,
die diese Logik korrekt umsetzt und zuverlässig die Realzeiten aller Einsätze mit Einsatz-Nr. ≥ 2 innerhalb einer Rufbereitschaft addiert.

Die Wunschergebnisse habe ich auch schon manuell ausgerechnet in der Spalte mit "Kommentar".

 

die datei kann nicht geöffnet werden.

 

@Tomam,

welche Excel-Version benutzt Du?

@Hajo, lege Dir einen neuen Rechner zu.

 

Hallo Tomam,

abgesehen davon, dass da etliche Formeln graußlich sind, vermisse ich die Wunschergebnisse in Spalte N. Zusätzlich würde mich interessieren, wann eine Rufbereitschaft anfängt und endet. Mir ist z.B. nicht klar, warum am 6.1. u. 8.1. zwei Rufbereitschaften gelten.

 

Hallo BoskoBiati
Meine Excel Version ist Microsoft 365 Apps for Enterprise Version 2512
Die Wunschergebnisse sind in der Spalte T in eingetragen, manuell ausgerechnet
Wenn andere Formeln noch zu optimieren sind, bin ich gerne für Verbesserungsvorschläge bereit, bin schon immer froh, wenn die Formeln überhaupt funktionieren, da ich kein Profi bin, eher sagen wir mal fortgeschrittener Anfänger ...
Die Rufbereitschaftszeiten stehen in dem Tabellenblatt "Dienstplan_Abrechnung". Dort kommen Tage nur doppelt vor, wenn z. B. zwei Kollegen sich die Rufbereitschaftszeit aus privaten Gründen teilen. Wann die Rufbereitschaft beginnt und endet, ist ja in Spalte D und H eingetragen. Die Formel braucht ja nur darauf schauen. Im Tabellenblatt "Erfassung" stehen Einsätze während der Rufbereitschaftszeit. Es kann gar keinen Einsatz geben, oder nur einen, oder zwei, oder noch mehr, das ist von der Situation abhängig - ob nun ein Notfall passiert oder nicht.

 

Hallo,

für Mo 05.01.2026 steht in Spalte T von dir händisch berechnet 01:08. wie kommt diese Zeit zustande? Aus der Liste im Blatt "Erfassung" in der relevanten Zeile 5 ist da keinerlei Zusammenhang zu erkennen.
Beschreibe wie du diese Zeit in T händisch errechnest.

Gruß Uwe

 

In dieser Schicht, die von 05.01. 17:00 bis 06.01. 08:00 geht, gab es zwei Einsätze. Wie in der Beschreibung angegeben, wird der erste Einsatz innerhalb einer Schicht immer mit 03:00 Arbeitszeit vergütet. Jeder weitere Einsatz dann in Realzeit. Da es ansonsten nur noch diesen zweiten Einsatz gab, muss die Zeit aus Erfassung Spalte G verwendet werden. Er Einsatz dauerte 1:08 h. Wäre noch ein weiterer Einsatz, müsste die Arbeitszeit hier hinzuaddiert werden.

 

Ich habe schon mal eine Formel dafür hier im Forum bekommen, allerdings hat sie nicht so gut funktioniert, weil die Formel nicht richtig unterscheiden konnte, welcher der erste Einsatz war. Daher habe ich in "Erfassung" eine zusätzliche Spalte eingefügt (H), in der definiert wird, welcher der Erste Einsatz war. Zusätzlich kommen immer mehr Einsätze vor, wir hatten damals nur vorgesehen dass max. drei Einsätze pro Schicht gibt. Daher gab es die gleiche Formel auch noch in Spalte O. Die Änderung ist aber, dass alle weiteren Einsätze ab einschließlich dem zweiten in Spalte N addiert werden sollen.

Hier beispielhaft aus der Zelle N853

=WENNFEHLER(MAX(AGGREGAT(15;6;Erfassung!$E$5:$E$3007/(Erfassung!$A$5:$A$3007=$B853)/(Erfassung!$I$5:$I$3007="Ja");--LINKS(N$9;1))-AGGREGAT(15;6;Erfassung!$C$5:$C$3007/(Erfassung!$A$5:$A$3007=$B853);--LINKS(N$9;1));(--LINKS(N$9;1)=1)/8);0)

 

für die Tabelle in Blatt Erfassung habe ich derweil die Formel dynamisch eingebaut bis Zeile 1000. Diese enthalten nun echte Datumszahlen.
Für die Berechnung für 1:08 h braucht es eine sichere Erkennung wann eine Schicht beginnt und wann diese endet. Also Aufbau der Erfassung muss überdacht werden.

Gruß Uwe

 

Hallo Uwe
danke dir.
Mein Gedanke warm dass in Erfassung die Spalte J hilft, abzugleichen, in welcher Schicht der Einsatz war. Denn Schichtende und -beginn sind ja in "Dienstplan_Abrechnung" drin. Auch mit Format Datum + Uhrzeit.

 

Mit dem Versuch Prüfung auf Arbeitszeitbeginn ohne Schichtende wirst du ohne klar erkennbaren Schichtverlauf keine zuverlässige Berechnung dieser Zeit erhalten. Egal was du versuchst.

Es würde schon reichen, wenn du eine Spalte einfügst, welche Schichtnummern enthalten. Ordne dies in Spalte A weiter ein.
Vergibt diese der Einfachkeit halber immer einmalig.

 

Zwar verstehe ich das nicht ganz denn, wie gesagt, sind Schichtbeginn und Ende ja definiert in "Dienstplan_Erfassung" Spalte D und H, aber wenn's nicht anders geht, dann soll's so sein. Wichtig ist aber dann meine Formel aus Spalte M in Dienstplan, da muss man dann schauen ob die noch weiterhin funktioniert, wenn in Erfassung was verschoben wird ...

 

Hi,

genau darauf zielte auch meine Frage ab: Wann sind offizieller Beginn und offizielles Ende der Rufbereitschaften. Der erste Alarm kann ja durchaus 5h nach Beginn kommen.
Ohne diese Angaben benötigt man eben genaue Daten, welcher Einsatz welcher Rufbereitschaft zuzuordnen ist.

 

@Edgar,
das wird schon in Zeile 11 in Dienstplan_Abrechnung scheitern, da sich 2 einen Dienst teilen.
@Tomam:
Es braucht im Dienstplan_Abrechnung eine klare Zuordenbarkeit zum Blatt Erfassung und eine vollständig aussagekräftige Vorbereitung der Beispieltabelle. Sonst drehen wir uns im Kreise.

Gruß Uwe

 

Bisher war das so gedacht: Alarmzeit Größer-Gleich Schichtbeginn und Kleiner-Gleich Schichtende, dann fällt es in die jeweilige Schicht. Die Spalte J in Erfassung ist also der Wert, der geprüft werden soll und kann dann automatisch in die richtige Schicht eingetragen werden. So war mein Ansatzpunkt. Weil man diese Daten ohnehin eingeben muss. Aber wenn es anders besser läuft, okay, dann bin ich auch für alternative Lösungen offen