Bestimmte Tage im Kalender zählen, die einen bestimmten Wert enthalten

[RPP63]

Moin!
Mal Deine neue Excel-Version nutzend:
Das Jahr in A1
Die Datumszeile wird mit einer einzigen Formelzelle unter Berücksichtigung eines Schaltjahres gespillt:
=DATUM(A1;1;SEQUENZ(;365+(MONAT(DATUM(A1;2;29))=2)))

Gruß Ralf

[onstore]

Hallo zusammen und vielen Dank für eure Mühe bisher...nur einen Kalender zu erstellen war meiner Teuersten leider zu wenig.


Sorry, ich musste gestern noch der Familie zur Verfügung stehen.

 
Also ich habe die Formel von schauan probiert, bin damit jedoch leider nicht zurechtgekommen.
Ersatzweise habe ich dann die leere Spalte zwischen Februar und März gelöscht und dann die Formel vom Februar bis zum Jahresende erweitert. Damit scheine ich nichts falsch gemacht zu haben.

Obwohl….bei den Jahren ohne Schaltjahr steht nach dem 31. Dezember nun immer der 1. Januar in der letzten Spalte der Jahresübersicht. Bei den Jahren mit 29 Tagen im Februar haut es hin. 

Um die Spalte mit dem ersten Januar des nächsten Jahres in der Jahresübersicht sozusagen auszublenden habe ich die Formel =WENNFEHLER(WENN(MONAT($NJ$7)=MONAT(NJ7+1);NJ7+1;"");"")
In diesen ersten Januar eingetragen.

Das klappt, bringt aber ein neues Problem. Durch das Verschwinden dieser Spalte mit dem ersten Januar funktioniert die Formel zum zählen der Anzahl von bestimmten Wochentagen (in dem Fall Donnerstag) mit dem Buchstaben „D“ in der Zelle nicht mehr, da diese letzte Spalte fehlt. Aber die Formel im Bereich um diese Spalte kürzen haut nicht hin, weil ich dann bei Schaltjahren nicht den korrekten Wert erhalten würde. So zumindest mein Gedanke.
 
Das ähnliche Problem habe ich bei den Tabellenblättern mit den einzelnen Monaten beim Februar, da ich da nicht in der Formel angeben kann, dass der Bereich von C6 bis AG6 geht, da das 31 Tage wären, der Februar aber entweder 28 oder 29 Tage hat.
 
Noch komplizierter wurde es, als meine Teuerste gestern den Wunsch geäußert hat, dass die Tabelle auch ausgeben soll, wieviele der Feiertage im Jahr bzw. Monat ein „D“ enthalten.
Die reinen Feiertage in einem Bereich zu zählen bekomme ich wohl hin. Aber zu zählen, bei wievielen Feiertagen der Buchstabe „D“ eingetragen ist, wird für mich unlösbar.
 
Genau wie eine Sache, die mir zu Beginn ein Kinderspiel zu sein schien. 
Ich habe im Tabellenblatt Feiertage mit Formeln die Feiertage des Jahres berechnen lassen. Um die entsprechend aufs Jahr anzupassen, ändere ich einfach das zugrunde liegende Jahr in dieser Tabelle. Ich fänd es aber angenehmer, wenn das Jahr im Tabellenblatt Feiertage aus der Jahresübersicht gezogen werden würde, damit ich nicht immer daran denken muss, nach einem Jahreswechsel auch in der Tabelle Feiertage das Jahr zu ändern.

Wenn ich aber versuche, das Jahr gleich dem Jahr aus der Jahresübersicht ausgeben zu lassen, dann bekomme ich eine Fehlermeldung, weil in der Jahresübersicht das Jahr als Format JJJJ angelegt ist und im Blatt Feiertage die Berechnung der Feiertage damit nicht funktioniert…dort wird das Jahr im Standardformat benötigt.
 
Also es macht schon wirklich viel Spaß mit Excel und es ist erstaunlich, was man für Möglichkeiten hat. Trotzdem habe ich es mir viel einfacher vorgestellt, als meine Partnerin mich um die Erstellung des Dienstplans gebeten hat. Grundsätzlich macht es Spaß zum Beispiel im WWW nach Lösungen zu suchen. Ich finde das nur dann besonders schwierig, wenn ich nicht so recht weiß, wie ich für Google das Problem benennen soll, um dafür dann auch die richtigen Lösungen zu finden. 

Grüße Marcel

[steve1da]

Edit: Durch deine Wenn-Formel erzeugst du in Nicht-Schaltjahren TEXT und das mag Summenprodukt iVm Wochentag nicht.
Schreib in J1 als Hilfszeile:

Code:

=WENNFEHLER(WOCHENTAG(J7;2);"")

Zieh das ganz nach rechts. Dann für die "d" an Donnerstagen:

Code:

=SUMMENPRODUKT((J1:NK1=4)*(J8:NK32="d"))

Wenn du das Jahr aus einem Datum auslesen willst, brauchst du dafür die Formel Jahr().
Die Feiertage die ein "d" haben kannst du ebenfalls mit einer Hilfszeile ausgeben lassen, zB in J33:

Code:

=ZÄHLENWENN(Feiertage!$F$1:$F$13;J2)

Diese Formel nach rechts ziehen und dann wieder mit Summenprodukt() wie oben schon die Zahl ermitteln.

[RPP63]

Moin!
Ich weiß, Manöverkritik in #24 ist vielleicht etwas spät, dennoch:
Ich finde den Aufbau (vor allem wegen seiner Schein-Dynamik) höchst unglücklich!
Was machst Du denn bei Jahreswechsel?
Richtig, Du musst erst die Inhalte der Blätter Januar bis Dezember löschen!
Deshalb:
Erstelle eine leere Vorlage (.xltx) und erstelle anhand der Vorlage einmal im Jahr eine neue Datei.

Dann hat sich auch die ganze Schaltjahresproblematik erledigt.
Entweder man weiß, wann ein Schaltjahr ist  oder man benutzt halt meine dynamische Formel von oben.

Gruß Ralf

[onstore]

hey...ich danke euch für die Mühe!

@RPP63: ich habe deine dynamische Formel gestern schnell noch auf einer leeren Tabelle probiert und war überrascht wie schnell es ging.

Ich habe vorhin im Büro nochmal kurz ein bisschen was versucht und somit eine neue Version der Tabelle. Die sieht nicht anders aus, ich habe nur ein paar Kleinigkeiten gemacht.
@steve1da: ich weiß nicht, ob die Formeln von dir da nun noch passen in der neuen Date. 

Da ich schon ne Weile an der Sache sitze, scheue ich mich ehrlich gesagt, nun alles nochmal neu zu bauen.
Wenn man den Plan hat, ist das wahrscheinlich in einer Stunde erledigt. Aber wenn man ansonsten nur mit einfachen Filtern oder Formeln zu tun hat, ist das schon eine ordentliche Herausforderung. 

Grüße, Marcel


  Dienstplan_MB_2.xlsx (Größe: 325,52 KB / Downloads: 4)

[schauan]

Hallöchen,

mal abgesehen davon, in der zuletzt geladenen Datei hast Du in der Spalte BQ im Schaltjahr immer noch das "falscher Monat" Problem.
Enrweder folgst Du Ralf's Vorschlag mit zwei verschiedenen Vorlagen oder setzt dort meinen Ansatz aus #16 um.

[Kuwer]

Hi André,

... Ralf's Vorschlag mit zwei verschiedenen Vorlagen ...

Ralf schlug eine Vorlage vor. 

Gruß Uwe

[steve1da]

@steve1da: ich weiß nicht, ob die Formeln von dir da nun noch passen in der neuen Date. 

Probier es doch einfach aus....

[schauan]

Hi Uwe,

ich habe das ausgebaut, wegen dem Schaltjahr

@Marcel,

im Anhang mal die Änderungen eingebaut. Zusätzlich hab ich noch die Zählspalten ab Do angepasst mit WENNFEHLER. Das müsstest Du in der mit Mo-Mi noch tun. Dort kommt nur kein Fehler, weil Du auf NJ6 gekürzt hast statt bei NK zu bleiben

  Dienstplan_MB_2as.xlsx (Größe: 328,25 KB / Downloads: 7)

[onstore]

Hallo!

ich habe mir die neue Version hat. direkt runter geladen. 
Klasse...wirklich toll! Fast hatte ich ein schlechtes Gewissen, die einfach zu verwenden.

Ich habe nun tatsächlich fast alles so, wie ich bzw. meine Partnerin es haben wollte.

Das Einzige, was ich noch nicht hinbekommen habe, ist das mit den Feiertagen.

Ich habe auch die Formel von steve1da benutzt und damit nun jeweils unter der Tabelle in einer Hilfszeile jeweils eine 1 stehen, wenn der Tag darüber ein Feiertag ist.
Auch habe ich es hinbekommen, die Anzahl der Feiertage gesamt zählen zu lassen.

Ich steige aber trotzdem noch nicht durch, wie ich nun dafür sorgen kann, dass Excel die Feiertage nur dann zählt, wenn ein "d" drin steht. 

Vielleicht habe ich hier den Tipp von steve1da nicht ganz verstanden, dass ich über die Formel in der Hilfszeile dann an anderer Stelle über SUMMENPRODUKT die Feiertage mit "D" zählen lassen kann.

Ich brauche noch einen Tipp bitte!