Stundenberechnung nach Datum und Wochentag für Mitarbeiter X

[Drey0r]

Hallo Hallo,

ich tüftel seit geraumer Zeit an einem mir gegebenen Schichtplan und bekomme leider die Stundenberechnung nicht hin.
Ich hoffe ihr könnt mir helfen.

   


In Zelle A2 wird der aktuelle Monat eingegeben. Die Tage werden automatisch darunter aufgelistet.
in den Spalten B und C wird der entsprechende Mitarbeiter für die Schicht eingetragen.
Die Farben etc sind per bedingter Formatierung gelöst, funktioniert alles.

Ich habe folgendes Problem:
In den Zellen B 36-41 bzw C 36 - 41 sollen nun die Stunden der Mitarbeiter aufaddiert werden.
Jede Frühschicht hat 10 Stunden, jede Spätschicht 9 Stunden AUßER Sonntags, da ist es immer eine Stunde weniger.


Ich bekomme leider die Abfrage nicht hin, dass wenn ein Mitarbeiter Sonntags arbeitet ihm die richtigen Stunden aufaddiert werden.
Ich möchte das ganze gerne ohne Hilfsspalte lösen.

Was bisher klappt ist:


=ZÄHLENWENN(B$3:B$33;$A36)*9

und nun muss berücksichtigt werden, wenn es sich um einen Sonntag handelt ist es eine Stunde weniger.


=WENN(UND(WOCHENTAG($A3)=1;B3=$A$36);ZÄHLENWENN($B3;$A$36)*9;ZÄHLENWENN($B3;$A$36)*10)

Für eine Zeile klappt es, aber wie bekomm ich es für den kompletten Monat abgefragt bzw aufaddiert?

Ich hoffe ihr könnt mir helfen.

Vielen Dank

[Werner.M]

Hallo,

Formel in B36 und nach unten ziehen bis B41:
=(SUMMENPRODUKT((WOCHENTAG($A$3:$A$32)=1)*($B$3:$B$32=$A36))*9)+(SUMMENPRODUKT((WOCHENTAG($A$3:$A$32)>1)*($B$3:$B$32=$A36))*10)

Formel in C36 und nach unten ziehen bis C41:
=(SUMMENPRODUKT((WOCHENTAG($A$3:$A$32)=1)*($B$3:$B$32=$A36))*8)+(SUMMENPRODUKT((WOCHENTAG($A$3:$A$32)>1)*($B$3:$B$32=$A36))*9)


Gruß Werner

[Kuwer]

Hallo Werner,

es geht doch nur um die Spätschichten, welche Sonntags eine Stunde kürzer sind.
Also für C36: =ZÄHLENWENN(C$3:C$32;$A36)*9-(SUMMENPRODUKT((WOCHENTAG(A$3:A$32)=1)*(C$3:C$32=$A36)*1))

Gruß Uwe

[Drey0r]

Hi Werner, Hi Kuwer

vielen vielen Dank. Großartig.


Ich bekomme allerdings einen Fehlerwert in Monaten mit weniger als 31 Tagen, also wo die Zeilen 31 und 32 z.B. leer sind.

Die Datumswertde in Spalte A ergeben sich für die letzten Tage im Monat folgendermaßen:

=WENN(MONAT(A28+3)=MONAT($A$2);A28+3;"")

Gibt es einen eleganten weg das zu berücksichtigen?
Ich würde es sonst mit =Wenn(istleer(... lösen und die einzelnen Varianten dann verschachteln in einer Formel

[Kuwer]

Hallo,

Ich bekomme allerdings einen Fehlerwert in Monaten mit weniger als 31 Tagen, also wo die Zeilen 31 und 32 z.B. leer sind.

Die Datumswertde in Spalte A ergeben sich für die letzten Tage im Monat folgendermaßen:

=WENN(MONAT(A28+3)=MONAT($A$2);A28+3;"")

Gibt es einen eleganten weg das zu berücksichtigen?

ob folgende Variante "elegant" ist, will ich nicht beurteilen (Formel in C36):

=ZÄHLENWENN(BEREICH.VERSCHIEBEN($C$3;0;0;EDATUM($A$3;1)-$A$3;1);$A36)*9-(SUMMENPRODUKT((WOCHENTAG(BEREICH.VERSCHIEBEN($A$3;0;0;EDATUM($A$3;1)-$A$3;1))=1)*(BEREICH.VERSCHIEBEN($C$3;0;0;EDATUM($A$3;1)-$A$3;1)=$A36)*1))

Gruß Uwe

[Josef B]

Hallo

Hier noch eine andere Variante

Code:

=SUMMENPRODUKT((C$3:C$33=$A36)*(A$3:A$33<>"")*(9-ISTZAHL(1/(WOCHENTAG(A$3:A$33)=1))))

Gruss Sepp

[Drey0r]

Vielen vielen Dank Josef B

Ein Traum  :45: