Office: Zusammenhängende Zeiträume in einer Spalte erkennen und Start- sowie Enddatum ermitteln

Die Personalnummer ist wahrscheinlich einer der Faktoren, der gut zur Unterscheidung der Personen ist ohne auf die Namen und Vornamen Felder gehen zu müssen. Sie ist natürlich eineindeutig und eine Person kann nicht mehrere Zeilen für ein Datum haben...

 

Die Personalnummer ist natürlich eineindeutig und kann ggf. helfen die Personen zu unterscheiden?
LG Walle

 

Hi Walle,

anbei die Datei - schau bitte mal, ob das so passt. Wichtig ist die Spalte mit den Personalnummern (die ja eindeutig sind) - ich habe (fortlaufende - von 1 bis...) Dummy-Nummern vergeben.

Formel aus H3:
=WENN(E3-E2=1;"";LET(x;MTRANS(FILTER(E3:E$1172;D3:D$1172=D3));y;MTRANS(FILTER(E4:E$1173;D3:D$1172=D3));a;y-x;b;MIN(WENN(a<>1;SEQUENZ(;ANZAHL(a))))-1;TEXT(E3;"TT.MM.JJJJ-")&TEXT(E3+b;"TT.MM.JJJJ")))

EDIT:
Die beiden MTRANS kann man noch rausschmeißen (hatte ich zunächst zu Testzwecken) - dazu muss man dann das SEQUENZ-Array anstatt mit Spalten mit Zeilen erzeugen. Daher reicht auch diese Formel in H3 (und runterkopieren):

=WENN(E3-E2=1;"";LET(x;FILTER(E3:E$1172;D3:D$1172=D3);y;FILTER(E4:E$1173;D3:D$1172=D3);a;y-x;b;MIN(WENN(a<>1;SEQUENZ(ANZAHL(a))))-1;TEXT(E3;"TT.MM.JJJJ-")&TEXT(E3+b;"TT.MM.JJJJ")))

Aber am Ende machen beide Formeln das Selbe.

 

Hi {Boris},
um es einmal sehr deutlich zu sagen: DAS hätte ich nie herausgefunden :-)

Vielen Dank!
Ich werde mir die Formeln einmal sehr genau anschauen und versuchen zu verstehen. An solchen Sachen merkt man erst einmal wieder, wie klein das eigene Wissen über dieses mächtige Tool ist.

Nochmals: Vielen Dank!!!

LG Walle

 

Hi,

die Logik ist gar nicht so schwer:
Pro Zeile wird die Datumsspalte nach der jeweiligen Personalnummer (beginnend in der jeweiligen Zeile) gefiltert - zunächst "normal" und dann um 1 Zeile versetzt. Beide Datums-Filterergebnisse werden in den Variablen x bzw. y gespeichert. In der Variablen a werden dann beide Datumsarrays voneinander abgezogen. In der Variablen b wird dann die Position des ersten Eintrags ungleich 1 aus diesem Array ermittelt - und 1 abgezogen (-1). Zum Schluss wird das Datum mit b (das ist die Anzahl der Tage am Stück) addiert (Datum+b) und am Ende werden dann beide Datümer verkettet ausgegeben.
Zu Beginn der Formel wird nur noch mit WENN(E3-E2=1;"";...) geprüft, ob das Ergebnis ermittelt und angezeigt werden soll oder nicht.

 

Hi {Boris},
ich schaue mir das ganz in Ruhe an und versuche zu lernen.
Jetzt ist erst einmal ein großes Problem für mich gelöst :-)
Vielen Dank
Walle