Wochentag suchen mit Excel

  • Hallo Excelfreunde,
    ich suche eine Formel zur Berechnung des nächsten 20. eines Monats.
    Sollte diese aber auf ein Wochenende fallen, so soll der nächste Werktag danach angezeigt werden.
    Mit Monatsende habe ich probiert, aber komme nicht weiter,
    Kann mir jemand weiterhelfen?

    Gruß Frany22 :roll:

    • Anzeige

    Hallo!

    Wenn du gerade an deiner Website arbeitest oder dein aktuelles Hosting überdenkst: Wir betreiben mit NetzLiving eine Hosting-Plattform, die speziell auf Performance, Sicherheit und einfache Verwaltung ausgelegt ist.

    • ✔️ Schnelle Ladezeiten (optimiert für WordPress, WoltLab & Co.)
    • ✔️ Deutsche Server & DSGVO-konform
    • ✔️ Persönlicher Support (kein 0815-Ticket-System)

    Mehr erfahren

  • Nun, das geht zwar, aber nicht einfach mit einer Formel, sondern mit Umweg!

    Grund:

    Excel muss dafür ja "dynamisch" erst mal das richtige, zukünftige Datum SUCHEN, dann dessen Wochentag berechnen, den dann bewerten und dann noch ggf. den nächsten, darauf folgenden Werktag berechnen!
    Und das auch noch abhängig vom aktuellen Datum!!

    Außerdem gehe ich davon aus, dass wenn gerade der 20. IST, der NÄCHSTE 20. gemeint ist, richtig?

    Lösung:

    Zuerst legst Du ein neues, zusätzliches, leeres Tabellenblatt IN DER SELBEN TABELLE an. Dieses Blatt dient nur zur Berechnung. Ich nenne es mal "Tabelle2".

    Dort trägst Du in Zelle C1 ein:
    =HEUTE()

    Dann kommt in Zelle C2:
    =C1+1

    Dies kopierst Du in die darunter liegenden Zelle bis Zeile 33
    Die Spalte sollte nun als INHALTE haben
    =HEUTE()
    =C1+1
    =C2+1
    =C3+1
    u.s.w.

    ANGEZEIGT wird aber jeweils das Datum, beginnend mit heute, und da drunter jeweils der folgende Tag....

    Nun schreibst Du in Zelle B1:
    =WOCHENTAG(C1;2)
    (die "2" beschreibt den Typ, also hier: Zählung mit Montag=1 beginnen, Dienstag=2... bis Sonntag =7)

    Auch das kopierst Du runter bis in Zeile 33.
    Jetzt steht VOR jedem Datum also der jeweilige Wochentag als Zahl.

    In Zelle A1 schreibst Du nun:
    =TAG(C1)
    Damit wird dort das TAGESDATUM als Zahl angezeigt, also für den 30.8.2007 eine "30".
    Auch das bis Zeile 33 runter kopieren.

    Damit ist "Tabelle2" fertig.

    In Deiner eigentlichen Tabelle trägst Du nun, da, wo's gebraucht wird, folgende Formel ein:

    =WENN(SVERWEIS(20;Tabelle2!A2:C33;2)=6;SVERWEIS(22;Tabelle2!A2:C33;3);WENN(SVERWEIS(20;Tabelle2!A2:C33;2)=7;SVERWEIS(21;Tabelle2!A2:C33;3);SVERWEIS(20;Tabelle2!A2:C33;3)))

    Übersetzung:

    "SVERWEIS(20;Tabelle2!A2:C33;2)" bedeutet Suche die "20" In der "Tabelle2" im Bereich "A2 bis C33" in Spalte A, und gib den zugehörigen Wert aus Spalte 2 (=Spalte B) aus.

    Die Formel sagt also:

    wenn "sverweis" eine "6" (=Sa.) ergibt,
    gib das Datum (=Spalte 3, C) aus DER Zeile aus,
    wo "sverweis" eine "22" findet (übernächster Tag!),

    wenn "sverweis" eine "7" (=So.) ergibt,
    gib das Datum (=Spalte 3, C) aus DER Zeile aus,
    wo "sverweis" eine "21" findet (nächster Tag!),

    sonst gib das Datum (=Spalte 3, C) aus DER Zeile aus,
    wo "sverweis" eine "20" findet (Tag).

    Wenn Du diese Formel so eingetragen hast, zeigt die Zelle allerdings noch KEIN Datum, sondern eine fünfstellige Zahl!

    Du musst jetzt darum die Zelle noch mal markieren, und ihr unter "Format>Zellen..." das Format "Datum" zuweisen.
    Nun wird korrekt der nächste 20. (bzw. 21. oder 22.) angezeigt.

    :)

    Rabe

    P.S.: Die Formel geht davon aus, dass das Berechnungsblatt "Tabelle2" heißt! Falls es bei Dir anders heißt, z.B. "Berechnung", musst Du in der Formel jeweils "Tabelle2!" durch "Berechnung!" ersetzen. (Das "!" ist dabei der Verweis darauf, dass es sich dabei um ein anderes Tabellenblatt handelt!)

    Tipp: Damit nicht bei jedem Ausdruck Deiner eigentlichen Tabelle das Berechnungsblatt mit ausgedruckt wird, legst Du einfach für die "Haupttabelle" einen Druckbereich fest (Bereich markieren, dann Datei>Druckbereich>festlegen)....
    8-)

    Computer setzen logisches Denken fort!
    Unlogisches auch....

  • Hallo zusammen,
    ich hatte das Problem den nächsten 20. eines Monats mit Excel zu suchen.
    Ravenheart hat mir mit folgender Formel weitergeholfen. (am 29.08.2007)
    =WENN(SVERWEIS(20;Tabelle2!A2:C33;2)=6;SVERWEIS(22;Tabelle2!A2:C33;3);WENN(SVERWEIS(20;Tabelle2!A2:C33;2)=7;SVERWEIS(21;Tabelle2!A2:C33;3);SVERWEIS(20;Tabelle2!A2:C33;3)))

    Nun stellt sich folgendes problem.

    Wenn ich das Suchdatum 20 in der Formel durch eine Zelle (z.B. A4) mit dem gleichen Wert ersetze, und in der Hilfstabelle C1 nicht startdatum =HEUTE() sondern ein festen Datum aus einer anderen Zelle einsetze, dann funktioniert das ganze nur teilweise.

    Wichtig ist den nächsten 20. eines Monats nach einem festen Datum zu finden, dieser kann auch im gleichen Monat des Startdatums liegen.

    Meine Abgeänderte Formel: =WENN(SVERWEIS(A4;Tabelle2!A2:C33;2)=6;SVERWEIS((A4+2);Tabelle2!A2:C33;3);WENN(SVERWEIS(A4;Tabelle2!A2:C33;2)=7;SVERWEIS((A4+1);Tabelle2!A2:C33;3);SVERWEIS(A4;Tabelle2!A2:C33;3)))

    Warum geht das nun nicht mehr?

    Gruß Franky22

  • 1.
    Das geht nicht, weil es sich dabei um Parameter handelt!

    Wenn Du da "A4" eingibst, sucht er die Buchstabenfolge "A4"!

    Da er an der Stelle ein Suchparameter erwartet, und keine Zellenangabe, versteht er die Eingabe falsch!

    2.
    Auch mit dem Datum musst Du aufpassen!

    Excel verarbeitet Datumsangaben intern als fortlaufende Zahl - der 4.9.2007 ist z.B. 39329.... Dies wird dann nur als Datum ANGEZEIGT.
    (Deshalb ist auch die Spalte mit "Tag..." erforderlich, damit der Wert (für das o.a. Datum) "4" ist, und nicht 39329....)

    Gibst Du ein Datum fest ein, bitte drauf achten dass es auch wirklich ein DATUM ist, und nicht etwa ein Text!

    Rabe

    Computer setzen logisches Denken fort!
    Unlogisches auch....

Jetzt mitmachen!

Sie haben noch kein Benutzerkonto auf unserer Seite? Registrieren Sie sich kostenlos und nehmen Sie an unserer Community teil!