Ich habe eine Zeiterfassung gemacht im Excel, da wir die Tagesstunden vorgegeben haben, habe ich fünferblöcke gemacht. Ich will das Datum automatisch erfassen. Das heisst ich muss nun zwingend der erste Samstag im Monat in Zelle b12 und der Sonntag in b13 angezeigt haben, dann soll der Rest automatisch ausgefüllt werden in den Zellen b7 - b40 je nach Monat den ich vorgegeben habe. (Pro Monat ein Tabellenblatt). Vielen Dank für Eure Unterstützung
Automatische Datumserfassung ab einer bestimmten Zelle
- Ersteller wulli51
- Erstellt am
Turakos
Stammgast
Hallo wulli51
Viele Fragezeichen?
Welche Excel-Version hast du?
Der Monat beginnt bei dir am ersten Samstag, wo sind die anderen Tage eines Monats?
z.B. im Dezember 2013 der erste Samstag ist am 07.12.2013 - wo sind dann der 01.12.2013 bis 06.12.2013?
In der Monats-Tabelle sind alle(?) Tage aufzulisten, inklusive Wochenende, Feiertage, andere arbeitsfreie Werktage usw.?
Für jeden Monat ein separates Tabellenblatt in der gleiche Arbeitsmappe, überleg dir, ob du nicht alle Monate auf einem Tabellenblatt auflisten solltest und dann ein weiteres Tabellenblatt als Übersicht/Zusammenfassung oder eine Pivot-Tabelle.
Ich würde das ganze noch einmal überlegen und dann stellst du mindestens einen Screenshot in das Forum, damit man erkennen kann wie das Aussehen sollte.
Hier trotzdem zwei Formeln, für November 2013:
a) für den ersten Samstag in B12): =DATUM(2013;11;1)+7*1-REST(DATUM(2013;11;1)+7;7)
b) für die weiteren Tage in B13: =WENN(B12="";"";WENN(MONAT(B12+1)=MONAT($B$12);B12+1;""))
Die zweite Formel herunterziehen, listet aber nur die Tage des gleichen Monats auf.
In der ersten Formel ist die Funktion DATUM enthalten (siehe die Excel-Hilfe wegen der Syntax).
Viele Fragezeichen?
Welche Excel-Version hast du?
Der Monat beginnt bei dir am ersten Samstag, wo sind die anderen Tage eines Monats?
z.B. im Dezember 2013 der erste Samstag ist am 07.12.2013 - wo sind dann der 01.12.2013 bis 06.12.2013?
In der Monats-Tabelle sind alle(?) Tage aufzulisten, inklusive Wochenende, Feiertage, andere arbeitsfreie Werktage usw.?
Für jeden Monat ein separates Tabellenblatt in der gleiche Arbeitsmappe, überleg dir, ob du nicht alle Monate auf einem Tabellenblatt auflisten solltest und dann ein weiteres Tabellenblatt als Übersicht/Zusammenfassung oder eine Pivot-Tabelle.
Ich würde das ganze noch einmal überlegen und dann stellst du mindestens einen Screenshot in das Forum, damit man erkennen kann wie das Aussehen sollte.
Hier trotzdem zwei Formeln, für November 2013:
a) für den ersten Samstag in B12): =DATUM(2013;11;1)+7*1-REST(DATUM(2013;11;1)+7;7)
b) für die weiteren Tage in B13: =WENN(B12="";"";WENN(MONAT(B12+1)=MONAT($B$12);B12+1;""))
Die zweite Formel herunterziehen, listet aber nur die Tage des gleichen Monats auf.
In der ersten Formel ist die Funktion DATUM enthalten (siehe die Excel-Hilfe wegen der Syntax).
Zuletzt bearbeitet:
Datumserfassung
Hallo Grippen vielen Dank für Deine Antwort, ich arbeite mit der Excel-Versin 2010
Antwort
Mo 1.12.13 ist in Zelle b7. 31.12.13 ist Zelle b39. Es sind alle Tage eines Monats aufzulisten, Feiertage werden nicht berücksichtigt.
Ich versuche das Problem noch zu prezisieren
Ich schreibe in Zelle b1 Das Jahr (2014) in Zelle b1 den Monat (11)
in Zelle b7 =WENN(A1="";"";DATUM($A$1;$C$1;1))
in Zelle b8 =WENN(MONAT(A5+1)=MONAT($A$5);A5+1;"") Das funktioniert alles
nun habe ich einfach das Problem dass es die Wochenende bezw.die Wochentage verschiebt wenn ein anderer Monat gewählt wird. Darum möchte ich den ersten Samstag als Anfang immer in der gleichen Zell b12 haben. Nun soll der Rest vom Datum nach unten und oben ergänzt werden.
Ich hoffe ich konnte Das Problem verständlich machen.
Hallo Grippen vielen Dank für Deine Antwort, ich arbeite mit der Excel-Versin 2010
Antwort
Mo 1.12.13 ist in Zelle b7. 31.12.13 ist Zelle b39. Es sind alle Tage eines Monats aufzulisten, Feiertage werden nicht berücksichtigt.
Ich versuche das Problem noch zu prezisieren
Ich schreibe in Zelle b1 Das Jahr (2014) in Zelle b1 den Monat (11)
in Zelle b7 =WENN(A1="";"";DATUM($A$1;$C$1;1))
in Zelle b8 =WENN(MONAT(A5+1)=MONAT($A$5);A5+1;"") Das funktioniert alles
nun habe ich einfach das Problem dass es die Wochenende bezw.die Wochentage verschiebt wenn ein anderer Monat gewählt wird. Darum möchte ich den ersten Samstag als Anfang immer in der gleichen Zell b12 haben. Nun soll der Rest vom Datum nach unten und oben ergänzt werden.
Ich hoffe ich konnte Das Problem verständlich machen.
Turakos
Stammgast
Hallo wulli51
Realsierungs-Idee nach deiner Problemdarlegung:
Die Formeln basieren auf Spalte B, das wäre deine Datum-Spalte.
B1: Eingabe des Monatsersten im Format dd.mm.jjjj, Zelle Benutzerdefiniert MMM JJJJ
a) B12: =$B1-1-WOCHENTAG($B1-6;3)+7*1 (berechnet den ersten Samstag eines Monats)
b) B13 bis B42: =WENN(B12="";"";WENN(MONAT(B12+1)=MONAT($B$12);B12+1;""))
c) B6 bis B11: =WENN(B7="";"";WENN(MONAT(B7-1)=MONAT($B$12);B7-1;""))
B6 bis B42: Zellformat Benutzerdefiniert TTT TT.MM.JJJJ (als Beispiel).
Die Formel b) In Zelle B13 eintragen und herunterziehen bis B42
Die Formel c) In Zelle B6 eintragen und herunterziehen bis B11
Als "Option" Bedingte Formatierungen: Zellbereich B6 bis B42:
erste BF: Zellwert =HEUTE() und eine Zellformatierung > markiert die Zelle mit dem heutigen Tag
zweite BF: Formel =WOCHENTAG(B6;2)>5 und eine Zellformatierung > markiert die Wochenende
Vorgehen als Vorschlag: Tabellenblatt Jan erstellen mit obigen Formeln, testen > Tabellenblatt kopieren für jeden Monat ein Tabellenblatt. In die Zelle B1 den ersten Tag des Monats eintragen > Ergebnis Monatskalender automatisch ausgefüllt.
Falls du meine Test-Arbeitsmappe möchtest bitte danach fragen.
Realsierungs-Idee nach deiner Problemdarlegung:
Die Formeln basieren auf Spalte B, das wäre deine Datum-Spalte.
B1: Eingabe des Monatsersten im Format dd.mm.jjjj, Zelle Benutzerdefiniert MMM JJJJ
a) B12: =$B1-1-WOCHENTAG($B1-6;3)+7*1 (berechnet den ersten Samstag eines Monats)
b) B13 bis B42: =WENN(B12="";"";WENN(MONAT(B12+1)=MONAT($B$12);B12+1;""))
c) B6 bis B11: =WENN(B7="";"";WENN(MONAT(B7-1)=MONAT($B$12);B7-1;""))
B6 bis B42: Zellformat Benutzerdefiniert TTT TT.MM.JJJJ (als Beispiel).
Die Formel b) In Zelle B13 eintragen und herunterziehen bis B42
Die Formel c) In Zelle B6 eintragen und herunterziehen bis B11
Als "Option" Bedingte Formatierungen: Zellbereich B6 bis B42:
erste BF: Zellwert =HEUTE() und eine Zellformatierung > markiert die Zelle mit dem heutigen Tag
zweite BF: Formel =WOCHENTAG(B6;2)>5 und eine Zellformatierung > markiert die Wochenende
Vorgehen als Vorschlag: Tabellenblatt Jan erstellen mit obigen Formeln, testen > Tabellenblatt kopieren für jeden Monat ein Tabellenblatt. In die Zelle B1 den ersten Tag des Monats eintragen > Ergebnis Monatskalender automatisch ausgefüllt.
Falls du meine Test-Arbeitsmappe möchtest bitte danach fragen.
Hallo Grippen
Sorry ich habe noch ein Anliegen/Anpassung
Ich schreibe in Zelle b1 Das Jahr (2014) in Zelle b1 den Monat (11) (SORRY da hat sich ein Fehler eingeschlichen der Monat schreibe ich in Zelle c1, (zB 11), das hat den Vorteil dass ich in der Januartabelle nur die Jahreszahl ändern muss die dann in den anderen Tabellenblätter mit (=Jan!b1)übernommen wird. Leider kann ich das nicht Programmieren dass es funktioniert. Hast Du für diese schreibweise auch eine Lösung? Zum voraus vielen Dank
in Zelle b7 =WENN(A1="";"";DATUM($A$1;$C$1;1))
in Zelle b8 =WENN(MONAT(A5+1)=MONAT($A$5);A5+1;"") Das funktioniert alles
Sorry ich habe noch ein Anliegen/Anpassung
Ich schreibe in Zelle b1 Das Jahr (2014) in Zelle b1 den Monat (11) (SORRY da hat sich ein Fehler eingeschlichen der Monat schreibe ich in Zelle c1, (zB 11), das hat den Vorteil dass ich in der Januartabelle nur die Jahreszahl ändern muss die dann in den anderen Tabellenblätter mit (=Jan!b1)übernommen wird. Leider kann ich das nicht Programmieren dass es funktioniert. Hast Du für diese schreibweise auch eine Lösung? Zum voraus vielen Dank
in Zelle b7 =WENN(A1="";"";DATUM($A$1;$C$1;1))
in Zelle b8 =WENN(MONAT(A5+1)=MONAT($A$5);A5+1;"") Das funktioniert alles
Turakos
Stammgast
Hallo wulli51
Ich denke, ich habe deine Beschreibung verstanden. Ich schreibe dazu nur den Hinweis:
Anzupassen ist nur die Formel in B12 (berechnet den ersten Samstag des Monats) alle anderen Formeln müssen nicht geändert werden, wenn du die automatische Datum-Ableitung ab Zelle B12 nach oben (in Richtung Monatserster) bzw. nach unten (in Richtung Monatsletzter) so übernommen hast, wie ich beschrieben habe.
Ich denke, ich habe deine Beschreibung verstanden. Ich schreibe dazu nur den Hinweis:
Anzupassen ist nur die Formel in B12 (berechnet den ersten Samstag des Monats) alle anderen Formeln müssen nicht geändert werden, wenn du die automatische Datum-Ableitung ab Zelle B12 nach oben (in Richtung Monatserster) bzw. nach unten (in Richtung Monatsletzter) so übernommen hast, wie ich beschrieben habe.