Excel / mit Datum rechnen

  • Mal wieder eine Frage zum rechnen mit Datum:

    Ich habe ein Datum, z.B. 01.05.2005, von welchem ich einen Monat abziehen möchte.
    Das Ergebnis soll hier also 01.04.2005 lauten.

    Bekomme die Formel einfach nicht hin. Wer kann mir helfen?

    Goebi

    • 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

  • O.K...

    Grundsätzlich:
    Excel speichert Datumsangaben als fortlaufende Zahlen, um sie in Berechnungen verwenden zu können. Der 1. Januar 1900 wird standardmäßig als fortlaufende Zahl 1 gespeichert. Der 1. Januar 2008 wird beispielsweise als fortlaufende Zahl 39448 gespeichert, da dieses Datum 39.448 Tage auf den 1. Januar 1900 folgt. (Zitat aus: Excel-Hilfe)

    Wir haben also 2 Probleme zu lösen:

    1. Haben nicht alle Monate gleich viel Tage.
    2. Hat der Februar auch noch gelegentlich 29 statt 28 Tage.
    Aber keine Angst, beides lässt sich ganz einfach berücksichtigen!

    Um vom 1.1.2008 (s.o.) auf den 1.12.2007 zurück zu rechnen, muss man von der Datumszahl 39448 ja 31 (den Dez.) abziehen. Also 39448 - 31 = 39417. Um aber vom 1.3.2008 auf den 1.2.2008 zu kommen, müssen 29 (Tage) abgezogen werden (2008 IST ein Schaltjahr!).

    Also los:

    Meine Tabelle ist folgendermaßen aufgebaut:
    In Zeile 1 stehen Überschriften:

    A1 "Eingabe"
    B1 "Ergebnis"
    C1" MonatA2"
    D1 "JahrA2"
    E1 "1.1."
    F1 "+59"
    G1 "29oder1?"

    In A2 gibst Du das Datum ein das umgewandelt werden soll.
    In B2 soll dann das richtige Ergebnis stehen, also der 1.1. des Vormonats.

    Dafür müssen wir erst mal feststellen, welcher Monat das Eingabedatum hat, das da in A2 steht! Dafür kommt nach C2 die Formel:
    =MONAT(A2)

    Außerdem müssen wir wissen, welches Jahr es hat. Das macht die Formel in D2:
    =JAHR(A2)

    (Kommt einem komisch vor, schließlich steht ja beides SCHEINBAR in A2... Aber eben nur SCHEINBAR! Aus Sicht von Excel steht in A2 ja nur eine Zahl wie 39448!)

    So, nun müssen wir wissen, welche Zahl zum 1.1. des Jahres gehört, das wir in D2 ermittelt haben. Das macht E2 mit der Formel:
    ="01.01."&D2
    (Eingeben, dann Zelle als "Datum" formatieren!)

    Dann geht es darum, welches Datum der 59. Tag NACH dem 1.1. DES JAHRES hat.

    Wieso der 59.? Nun, in einem Schaltjahr ist das der 29.Februar - sonst aber der 1. März!! Alles klar?

    Also berechnet die Formel in F2 erst mal:
    =E2+59
    (Zelle als "Zahl" formatieren!)

    Und nun schauen wir in G2 nach was für ein Tag das ist:
    =TAG(F2)

    Zwischenbilanz:
    Ist das in A2 eingegebene Datum ein Datum aus einem Schaltjahr,
    steht nun in G2 eine 29. Ist es kein Schaltjahr, steht da eine 1.

    Schnitt.

    Um, abhängig vom Monat des Eingabedatums, immer die richtige Zahl an Tagen abzuziehen, brauchen wir eine "Hilfstabelle", aus der wir die jeweils richtige raussuchen können.

    Die Tabelle habe ich in 2 Spalten angelegt, nach dem Muster

    Monat ... Tage, also

    1 ... 31
    2 ... 28
    3 ... 31
    4 ... 30
    .
    .
    .
    12 ...31

    (Die Liste steht bei mir hier im Beispiel im Bereich A4 bis B15!)

    Aber halt - wie war das mit dem Schaltjahr? Der Feb. (2) hat ja mal 28, mal 29! Richtig. Daher steht bei Feb. (2) eben NICHT "28", sondern die Formel:
    =WENN(G2=29;29;28)
    Übersetzt: Wenn die Berechnung >des 59. Tages vom Jahr des Eingabedatums< ergibt, dass es ein 29. ist, soll hier "29" stehen, sonst 28.

    Cool, was? :D

    Tja, und nun haben wir ALLE Angaben, die wir brauchen!

    Das (fast!, s.u.) richtige Ergebnis in B2 zu erhalten wir mit der Formel:
    =A2-(SVERWEIS((C2-1);A4:B15;2;FALSCH))

    Erläuterung:

    Ausgangsbasis ist die Datumszahl, also das Eingabedatum in A2. davon wird die richtige Zahl an Tagen abgezogen (=A2 - ....)

    In C2 steht ja der Monat des Eingabedatums (s.o.).

    Um vom 1.3.2008 auf den 1.2.2008 zurück zu kommen, müssen die 29 Tage des Februars, also des VORMONATS abgezogen werden. Darum "C2 - 1"

    Der Befehl "SVERWEIS" beinhaltet 4 Parameter, getrennt jeweils durch Semikolon:

    Suchbegriff (hier: C2 - 1, also der Vormonat)
    Suchbereich (hier: A4:B15, also meine Hilfstabelle)
    Angabe, wo in der Tabelle das Ergebnis steht (hier: 2, also Spalte 2 der Hilfstabelle) und
    Angabe, ob genau oder ungefähr gesucht wird (hier: FALSCH)

    Die Formel =A2-(SVERWEIS((C2-1);A4:B15;2;FALSCH))
    lautet übersetzt also:

    Nimm díe Datumszahl aus A2, und ziehe davon ab:
    die Anzahl Tage,
    die in der Hilfstabelle,
    für den VORmonat
    in Spalte 2 steht.

    Das war's fast;
    Problem gibt's aber, wenn das Eingabedatum ein 1. Januar ist, dann gibt C2-1 ja "0"..

    Darum muss die Formel für B2 lauten:
    =WENN(C2=1;A2-31;A2-(SVERWEIS((C2-1);A4:B15;2;FALSCH)))

    (Wenn C2 = 1 ist, dann zieh 31 (für Dez.) ab, sonst... (s.o.)....

    So is es perfekt. War doch ganz einfach, oder? 8-)

    Rabe

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

  • Hi,

    wenn du auf eine Spalte, Zelle per Rechtsklick auf "Zellen formatieren" gehst, kannst du die Zelle dort als Zelle mit dem inhalt "Datum" formatieren.
    Excel erkennt weiß dann dass es sich bei den eingetragenen Zahlen um Daten handelt.

    Bin aber nicht sicher ob dass in deinem Fall allzuviel bringt.

    MfG
    The_Godfather

  • Zitat

    Bin aber nicht sicher ob dass in deinem Fall allzuviel bringt.

    Das Formatieren der Zelle ist mir durchaus bekannt und hat aber leider nichts mit meiner Ausgangsfrage zu tun.

    Goebi

Jetzt mitmachen!

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