Excel Wochen von Montag bis Freitag mit Datum
Hallo liebe Leute,
vielleicht könnt Ihr mir weiterhelfen. Ich benötige einen von "Montag bis Freitag" Kalender in Excel für mind. 5 Jahre.
Der Aufbau soll folgender sein:
Spalte A : Daten (z.b 01.01.2010) Spalte B: Wochentag (Freitag)
Ich benötige nun das Datum des Montags und das des Freitags
Das ganze müsste über Jahre kopierbar sein :( und da versagen meine Excel Kenntnisse. Ich bekomme weder eine Wenn funktion noch einen Sverweis zum laufen.
Ich hoffe ihr könnt mir helfen.
Angefügt habe ich noch einen Screenshot wie es ungefähr aussehen könnte.
Ganz herzlichen Dank für eure Hilfe im vorraus und beste Grüße
Realz
5 Antworten
Eventuell hilft dir das:
Falls schlecht erkennbar hier die Formeln:
- A1 down Wochentage: =WAHL(WOCHENTAG(B1;2); "Montag"; "Dienstag";"Mittwoch";"Donnerstag";"Freitag";"Samstag";"Sonntag")
- B1 down Datumsspalte: 01.01.2010, 02.01.2010, ...
- D5 Filter für Montag: =SVERWEIS("Montag";BEREICH.VERSCHIEBEN(rWoche;7*$G5;0);2)
- E5 "bis" anzeigen: =WENN(D5<>""; "bis";"")
- F5 Filter für Freitag: =SVERWEIS("Freitag";BEREICH.VERSCHIEBEN(rWoche;7*$G6;0);2)
- G5 Hilfsspalte: G5 = 0, G6 = G5 + 1, G7 = G6 + 1, ...
- Benannter Bereich: A1: A7 = rWoche
Ich finde deine Lösung sogar besser, weil sie so simpel ist. Habe mich schon geärgert, warum ich es mir so umständlich gemacht habe. Natürlich benötigt man nur einen Montag. Der Rest ergibt sich, indem man 7 addiert. :(
Zunächst trägst du in einer Zelle (z. B. A2) das Startdatum ein. Dann sagt du in Zelle B2 "=A2" (natürlich ohne " "). Dann formatierst du das Format von Zelle B2 benutzerdefiniert mit TTT, d. h. er soll in der Zelle den Wochentag wiedergeben. Falls du den Wochentag komplett da stehen haben willst musst du statt TTT (abgekürzt) TTTT (komplett) eintragen.
Für den nächsten Tag gibst du in A3 "=A2+1" ein und in B3 "=A3". Dann kannst du die zwei Zeilen nach unten kopieren bis der Monat voll ist. Wie es weitergeht kannst du dir dann selbst ausmalen.
Falls du noch Fragen hast, meld dich.
Vielen Dank für den Denkanstoß!!!
Ich machs folgendermaßen: In Zelle A1 such ich mir den ersten Montag des Januars. Zelle A2 ist dann =A1+7. In Spalte B mach ich das gleiche mit Freitag.
:) Vielen Dank!
A1 Datum eintragen. Format für Dein Beispiel "TT.MM.JJJJ"
B1 die Formel =A1. Format für Dein Beispiel "TTTT" . Kopieren nach B2
Schreibe in A2 die Formel:
=A1+wenn(Rest(A1;7)=6;3;1)
A2:B2 markieren und am "Ausfüllkästchen" runterkopieren soweit Du willst. FERTIG!
Erklärung:
Da die Tage jeweils mit 1 hochgezählt werden seit 1.1.1900 (heute zB ist Tag Nr. 41205) und die Woche 7 Tage hat, hat jeder Tag/7 einen für den Wochentag charakteristischen Rest.
Durch eine Besondeheit in xl gibt es den Sa, 0.1.1900 mit Rest 0, alle Samstage haben also Rest 0, alle So 1, alle Freitage 6.
Am Freitag springt also die Formelzählung um 3 weiter auf Mo, ansonsten immer um 1 auf den nächsten Tag.
Nebenbemerkung: benutze als Mz von Datum nicht Daten, sondern Datums oder Datümer, weil im EDV-Bereich das Wort Daten für alle Informationen ("Daten-Verarbeitung") gebraucht wird.
So, wie im Englischen auch dates missverständlich ist!
Das wird mit Excel etwas schwierig. Denn es müssen ja auch Schaltjahre berücksichtigt werden.
Nach meinen Kentnissen müsstest du entweder dir per Programm eine Excelmappe generieren lassen oder du suchst ob du im Internet eine brauchbare findest.
Oder du erstellst einen mit allen Tagen und schreibst in eine (vll versteckte) Spalte den Tag. Dann blendest du einfach die Samstage und Sonntage aus.
Wow, vielen Dank!
Das ist eine ausführliche und elegante Lösung.
Ich habe es ganz simpel gemacht:
Erster Montag im Jahr 2010 ist der 04.01.10, der 2. Montag im Jahr ist dann 7 tage später, also +7 A2 =A1+7" Der Freitag ist 4 Tage vom Montag weg, also ist B1 = A1 +4
Die Spalten lassen sich dann beliebig formatieren nach (TTTT.MMMM.JJJJ) oder weniger :)
Damit ist meine Frage beantwortet und ich möchte mich ganz herzlich bedanken!!!
Beste Grüße
Realz!