Frage von Christofwieland, 51

Excel Stundenliste ?

ich habe eine Stundenliste erstellt wo jeder Mitarbeiter seine Arbeitszeiteitragen kann. Auf der zweite Arbeitsmappe habe ich einen Kalender erstellt und diesen so formatiert das Samstag und Sonntag jeweils hell oder dunkel grau sind.

Jetzt würde ich gerne wenn ich oben rechts November eintrage, dass die Spalte A1 selber erkennt wie viele Tage der hat November wann ist Samstag diesen Hellgrau einfärbt und wann ist Sonntag und diesen Dunklegrau einfärbt

hat jemand ein Idee wie das geht?

danke

Expertenantwort
von Iamiam, Community-Experte für Excel, 6

Füge an Deinen Kalender in Zeile 32 noch folgende Formel ein (alle Monate, ich mach das exemplarisch im Mai):

=MAX(E29:E32)

Das kompensiert die unterschiedlichen Tageszahlen der Monate und gibt den Letzten des Monats nochmals wieder [Alternativ: =Monatsende(E2;0) ]

Die Feiertage (egal, ob Original oder Übernahmekopie) sind in O3:O16 gelistet. (kann auch länger sein). Ich habe nun folgende Formeln, die Du nach Erfordernis variieren oder zusammensetzen kannst:

  • M2: =TEXT(E$2;"MMM JJ ")&"hat"
  • M3: =SUMMENPRODUKT(($O$3:$O$16>=E$2)*($O$3:$O$16<=E$33))
  • M4: 'Feiertage.
  • M6: 'Es fallen
  • M7: =SUMMENPRODUKT((REST($O$3:$O$16;7)=1)*($O$3:$O$16>=E$2)*($O$3:$O$16<=E$33))
  • M8: 'Feiertage
  • M9: ="im "&TEXT(E$2;"MMM")&" auf"
  • M10: 'einen So
  • im Klartext: Mai 16 hat 4 Feiertage. Es fallen 2 Feiertage im Mai auf einen So

Es fallen also M3-M7 Feiertage NICHT auf einen Sonntag

M7 kannst du für Sa )=0)* und So )=1)* summieren, um die Feiertage, die aufs Wochenende fallen, zu summieren [kürzer, aber schwerer verständlich: )<=1)* ]

Alle Sonntage im Mai

erhältst du mit

=SUMMENPRODUKT((REST($E$2:$E$32;7)=1)*1)+N("Sonntage im Monat") alle Sams- und Sonntage

mit

=SUMMENPRODUKT((REST($E$2:$E$32;7)=0)*1+(REST($E$2:$E$32;7)=1)*1)+N("Sa+Sonntage im Monat")

oder wieder in Einem:

=SUMMENPRODUKT((REST($E$2:$E$32;7)<=1)*1)+N("Sa+Sonntage im Monat")

[übrigens: <=1 ist bei nur ganzen Zahlen <2]

Am Ende kannst Du die Feiertagsliste beliebig versetzen, auch auf ein anderes Blatt, wenn nötig. Die Formeln passen sich an.

Mit diesen Formeln solltest du in Deinem Kalender recht gut jonglieren können. Frag ggf nochmal nach!

Expertenantwort
von Ninombre, Community-Experte für Excel, 40

Statt dem Wort November solltest Du 01.11.2016 eintragen. Die Zelle kann man benutzerdefiniert formatieren als MMMM dann steht trotzdem "November" drin. Ohne richtiges Datum lassen sich Schaltjahre und Wochentag ja nicht ermitteln. Alternative: Du machst mit der Datengültigkeitsprüfung > Liste die Auswahl als Dropdown. Irgendwo auf der Tabelle oder in anderem Tabellenblatt legst Du die Liste der gültigen Auswahlmöglichkeiten an: Zellinhalt 01.01.2016, 01.02.2016 etc. und diese dann als MMMM benutzerdefiniert formatieren. Der Anwender sieht dann in der Anzeige nur den Monatsnamen, setzt aber ein richtiges Datum.

In A6 (wo der 1. Tag des Monats steht) =G1 (oder wo Du den 01.11.2016 eingetragen hast.)

Ab A7

=WENN(MONAT(A6+1)=MONAT(A6);A6+1;"")

Damit werden nur die tatsächlich vorhandenen Tage je Monat angezeigt

Sa/So sind zwei Regeln für die bedingte Formatierung

Die Spalte markieren, neue Regel anlegen mit Formel zur Ermittlung der Formatierung
=wochentag(A6;2)=6 ist Sa und wochentag(A6;2)=7 der Sonntag

Kommentar von Ninombre ,

Die Anzeige der Tage in der Stundenliste als 1.,2. etc. erreichst Du auch über die benutzerdefinierte Zellformatierung T.

Ich würde auch da nicht einfach nur eine 1 reinschreiben, sondern das richtige Datum verwenden (bspw. über die Formel, die ich genannt habe) und dann nach belieben die Anzeige über die Formatierung einrichten. Wenn es ein richtiges Datum ist, kannst Du damit später bei Bedarf noch Berechnungen vornehmen, nur mit einer 1 drin eben nicht.

Kommentar von Christofwieland ,

super vielen Dank - hat geklappt. Hast du eine Idee ob das mit den Feiertagen auch funktioniert das diese dunkelgrau werden? ist mir gerade eingefallen das diese auch dunkelgrau sein sollten.

Kommentar von Ninombre ,

Wenn Du eine Liste der Feiertage hinterlegst, kannst Du mit einer weiteren bedingten Formatierung arbeiten:

=zählenwenn(Tabelle1!A$1:A$10;A1)>0

wenn die Feiertage in einem Tabellenblatt namens Tabelle1 stehen sollten. Grundidee: Prüfen, ob das Datum in der Liste gefunden wird, also zählenwenn >0 ist.

Kommentar von Christofwieland ,

okay verstehe ich soweit aber wie bringe ich diese formel im excel rein weil ich habe ja in der Spalte A bereits eine Formel drin stehen damit der Kalender funktioniert wie kann ich diese hintendran setzen?

Kommentar von Ninombre ,

Da war meine Antwort etwas zu knapp formuliert: Die Formel war für eine bedingte Formatierung gemeint.

Wenn Sonn- und Feiertage die gleiche Farbe bekommen sollen, kannst Du diese bedingte Formatierung auch zusammenlegen:

=ODER(wochentag(A6;2)=7;zählenwenn(Tabelle1!A$1:A$10;A1)>0)

Kommentar von Christofwieland ,

die bedingte formatierung von vorhin für sonntag kann ich löschen und dann diese Formel einfügen

dann kommt das ich bei einer bedingten formatierung nicht auf andere Arbeitsmappen zugreifen kann

das geht nicht.

Kommentar von Iamiam ,

Tipp für die Schnelleingabe von 1.Nov. 16: 11/16 , das erzeugt den Monatsersten. Die Zahl des Monatsletzten als Datum kriegst Du mit =MONATSENDE("11/16";0), die Zahl der Tage (jetzt im Juli) mit

=Monatsende("7/16";0)-"7/16"+1

Die Zahl der Sonntage im Mai 16 ist =SUMMENPRODUKT((REST(E2:E31;7)=1)*1)

(Samstage: =0, Fr: =6, ...)

Die Feiertage kannst du mit der Formel von Ninombre ergänzen, aber da werden Feiertage, die auf Sa/So fallen mitgezählt. Das auszuschließen, ist mir im Augenblick zu kompliziert.

Kommentar von Iamiam ,

bei einer bedingten Formatierung nicht auf andere Arbeitsmappen zugreifen kann:

Du meinst (Tabellen=)Blätter?

Dann spiegle die Tabelle im Blatt der bedingt formatierten Zellen! einfach irgendwo weit draussen:

=1.Quellzelle und runterziehen!

Das übernimmt dann alle Änderungen der Originaltabelle, wenn Du die Feiertage zB fürs nächste Jahr anpasst. Sieh auch einige Zellen mehr vor, falls Du das Gebiet wechselst: Rheinland:Rosenmontag, Sachsen:Buß-und-Bettag, Bayern:Fronleichnam. Mariä Himmelfahrt, ggf lokale Feiertage (früher gabs den Siemenstag:Pfingstdienstag oder Augsburg:Friedenstag, evtl auch spez. Geburtstage...)

am Ausschluss der Sonntags-Feiertage arbeite ich noch, sollte in Kürze fertig sein.

Expertenantwort
von funcky49, Community-Experte für Computer, 51

Wie wäre es denn mal mit googlen, in 10 sek das hier gefunden:

http://praxistipps.chip.de/excel-dynamischen-kalender-anlegen_2900

Kommentar von Ninombre ,

chip hat sich da ja richtig Mühe gemacht und nicht einmal die Formel angegeben, wie man die unterschiedliche Anzahl von Tagen je Monat abbildet

Kommentar von funcky49 ,

Tja, alles habe ich mir nun nicht durchgelesen, aber einen Kalender ohne die korrekten Monatstage würde ich nicht Kalender nennen, vielleicht hast Du ja was übersehen, es gibt ja auch einen Download-Link...

Für Excel gibt es massenweise Tipps und Tricks und auch Zusatztools, ansonsten einfach mal im  Internet googlen...

Kommentar von DeeDee07 ,

Der Downloadlink führt zu Adware. Das steht sogar da. Und dafür wollen sie einem sogar noch den Chip-Installer andrehen. Selbst ohne den ("manuelle Installation") ist es eine exe-Datei. Und das für 'ne poplige Excel-Tabelle.

Nein, danke.

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten