Jahres Abfall Kalender auf Basis des Jahreskalenders von Andreas Thehos

[meierchen006]

Hallo ich bin neu hier.

Mein Wissen von Excel ist bescheiden doch habe ich schon vieles gemacht komme aber immer mal wieder an meine Grenzen.
Heute habe ich mal wieder eine große Herausforderung mit der ich alleine nicht Klar komme.

Jahres Abfall Kalender auf Basis des Jahreskalenders von Andreas Thehos. 
https://thehosblog.com/excel-2010-2013/
 
Anleitung zum Jahreskalender >> Videos bei Youtube: 
Teil 1: https://www.youtube.com/watch?v=O9nz1yVqeLA
Teil 2: https://www.youtube.com/watch?v=mV4QNy1IXdM&t=263s
Teil 3: https://www.youtube.com/watch?v=788TzG7q-RQ&t=5s
Teil 4: https://www.youtube.com/watch?v=fp7o8RVNcUA&t=15s
 
Ich habe den Kalender weiter geändert und vier Spalten für Abfallentsorgung eingefügt. 
Wie gehabt sieht mein Kalender so aus:

Spalte A = Kalenderwoche
Spalte B = Kalendertage
Spalte C = Wochentage
 
Spalte D = Restmüll                Dienstag alle 2 Wochen          ungerade Wochen
Spalte E = Gelber Müll            Donnerstag alle 2 Wochen      ungerade Wochen
Spalte F = grüne Tonne          Dienstag alle 2 Wochen          gerade Wochen
Spalte G = Blaue Tonne          Dienstag alle 4 Wochen          ungerade Wochen
 
Spalte H = Ereignisse (Feiertage)

und dann der nächster Monat mit Spalte J von vorne mit Kalenderwoche usw.

der erste eines Monats ist immer Zeile 7
 
Bis dahin funktioniert der Kalender ohne Probleme.
 
So jetzt die Herausforderung: 
Es kann passieren das an einem Abholtag des Mülls ein Feiertag ist, dann verschiebt sich der Abholtag auf den nächsten Tag. 
Allerdings habe ich bisher für diesen Fall noch keine Formel gefunden. 

Vielleicht geht es mit Hilfe von Euch Excel Experten die bedingten Formatierungen so zu erstellen dass es funktioniert.  
 
Als Beispiel habe ich mal den Monat Mai ausgewählt, dort sieht man was ich meine,
am Donnerstag 10.05.2018 ist Feiertag das heißt der Abfall (gebe Säcke) wird am Freitag den 11.05.2018 abgeholt.
  
Meine Excel Version
Microsoft ® Excel ® 2016 MSO (16.0.8625.2121) 32 Bit (Aktuell Stand 28.11.2017)
 
Somit habe ich die (WENNS) Funktionen leider nicht, weil sie nicht in dieser Version vorhanden sind!

[Der Steuerfuzzi]

Hallo,

das Abholdatum kann man abgleichen mit:
=ARBEITSTAG(Datum an dem eigentlich Abgeholt wird - 1;1;Feiertagsliste)

[Der Steuerfuzzi]

Für Deine bedingte Formatierung anhand der grauen und gelben Tonne:

Arbeitsblatt mit dem Namen 'Jahreskalender-Saar'
	D	E
8

Name	Bezug
Feiertage	=Feiertage!$A$10:$A$31
Feiertage_Saar	=Feiertage!$A$10:$D$41

Zelle	bedingte Formatierung...	Format
D8	1: ARBEITSTAG(B8-WOCHENTAG(B8;3)+1-1;1;Feiertage)*(REST(KALENDERWOCHE(B8;21);2)=1)=B8	abc
D8	2: WOCHENTAG(B8;2)>5	abc
D8	3: C8="So"	abc
D8	4: SVERWEIS(B8;Feiertage_Saar;3;FALSCH)="ja"	abc
E8	1: ARBEITSTAG(B8-WOCHENTAG(B8;3)+3-1;1;Feiertage)*(REST(KALENDERWOCHE(B8;21);2)=1)=B8	abc
E8	2: WOCHENTAG(B8;2)>5	abc
E8	3: C8="So"	abc
E8	4: SVERWEIS(B8;Feiertage_Saar;3;FALSCH)="ja"	abc

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Die Formel habe ich zum Verständnis "ausführlich" gelassen:

Code:

=ARBEITSTAG(B7-WOCHENTAG(B7;3)+3-1;1;Feiertage)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7

Für den Donnerstag addiere ich 3 Tage zum Montag hinzu und ziehe dann einen Tag ab (deshalb B7-WOCHENTAG(B7;3)+3-1).

[meierchen006]

Hallo Der Steuerfuzzi;

herzlichen Dank für dein Wissen und natürlich deine Formel.

Ich habe sie angewendet, für grüne und blaue Tonne noch erweitert und funktioniert.

Bedingte Formatierung:

Restmüll: =ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7

Gelbe Säcke: =ARBEITSTAG(B7-WOCHENTAG(B7;3)+3-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7

Grüne Tonne: =ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=0)=B7

Blaue Tonne: =ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);4)=3)=B7

Somit ist auch jetzt mein Endloser Kalender  für den Abfall verwirklicht.

[meierchen006]

Hallo,

heute kam der neue Plan für 2018 zur Abfuhr des Mülls, da habe ich festgestellt das ich einen Denkfehler in meinem Kalender habe.

Feiertagsregelung:
In einer Woche mit einem Feiertag findet die Abfuhr ab dem Feiertag einen Tag später als üblich statt.

Beispiel:
Wenn die Abfuhr normalerweise Dienstag stattfindet, in der Abfuhrwoche ein Feiertag auf Montag fällt, erfolgt die Abfuhr an einem Mittwoch.

Das heißt in meiner KW 1 ist schon ein Fehler in der bedingten Formel für Restmüll und gelber Müll.

Restmüll käme somit Mittwoch, gelber Müll Freitags in der 1. KW

Exceldatei siehe #4

[meierchen006]

Hallo,

keiner mehr eine Idee zu meinem Problem in #5?

[Der Steuerfuzzi]

Bevor ich jetzt eine Lösung poste:

Verschiebt sich der Tag grundsätzlich um einen Tag, wenn in der Woche (irgendwo) ein Feiertag ist oder nur, wenn der Feiertag einen Tag vor Abholung liegt?
Was passiert, wenn der Feiertag genau am Abholtag ist?

[meierchen006]

Hallo Steuerfuzzi,

ja genau so ist es.

Liegt der Feiertag vor  oder an dem Abholtag, dann wird ein Tag nach hinten verschoben.

Also so
Abholtag Dienstag, Feiertag Montag, dann wird verschoben auf Mittwoch

Abholtag Donnerstag, Feiertag Montag, wird verschoben auf Freitag

Feiertagsregelung:
In einer Woche mit einem Feiertag findet die Abfuhr ab dem Feiertag einen Tag später als üblich statt.

[Der Steuerfuzzi]

Ich bin mir leider nicht ganz sicher, ob es jetzt so stimmt. Denn wenn die Abholung Freitag ist und ein Feiertag in der Woche, dann verschiebt er jetzt die Abholung auf Samstag.

Ich habe die Formeln jetzt mal so angepasst:

Code:

ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc
ARBEITSTAG(B7-WOCHENTAG(B7;3)+3-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc
ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=0)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc
ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);4)=3)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc

[meierchen006]

Hallo Steuerfuzzi,

schön das Du mir helfen möchtest, Danke schon mal dafür  :19: .

Ich habe deine Formeln jetzt mal angeschaut und gesehen das am Ende was fehlen müsste: rot markiert

=ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc
=ARBEITSTAG(B7-WOCHENTAG(B7;3)+3-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=1)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc
=ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);2)=0)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc
=ARBEITSTAG(B7-WOCHENTAG(B7;3)+1-1;1;Feiertage_Saar_1)*(REST(KALENDERWOCHE(B7;21);4)=3)=B7-(SUMMENPRODUKT((Feiertage_Saar_1>=B7-WOCHENTAG(B7;2)+1)*(Feiertage_Saar_1B7))>0);abc

Bei dem rot markierten fehlt was zwischen (Feiertage_Saar_1 ? B7)

Was macht das ;abc am Ende in der Formel?