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.