Da man von Zeit zu Zeit immer mal wieder über das Thema Berechnungen mit Datumswerten im Excel stolpert und im Netz in vielen Blogeinträgen zwar diese Funktion aber mit Nachfragen zum Fehler #Zahl! ohne entsprechende Lösung zu finden sind, möchte ich das hier mal am Beispiel einer Rechnungsübersicht hinschreiben.
Problemstellung: Man möchte die einzelnen Verzugszeiten zwischen Eingang und Zahlungsfrist tracken. Dazu benötigt man natürlich die Differenz der Datumswerte, hier im Beispiel in Tagen (das könnte man in anderen Fällen vllt. auch ausgeben wollen in x Monate y Tage).
Die in der Excel Online-Hilfe nicht dokumentierte Funktion datedif ist dafür sehr gut geeignet:
datedif(Startdatum, Enddatum, Zeitheit).
Dabei ist die korrekte Reihenfolge wichtig. Bei Negativwerten (Startdatum liegt nach Enddatum) wird der Fehler #Zahl! ausgegeben. Die Zeitheit wird als Schalter verwendet: (y,m,d, md, ym, yd).
Nun gibt es aber im Beispiel eine Reihe von Fällen zu beachten:
- Zahlung ist eingegangen oder noch offen
- Zahlungsfrist kann überschritten sein (offen oder nicht) oder vor Ablauf bezahlt
- Rechnungseintrag kann angelegt sein, aber noch nicht verschickt (kein Datum Zahlung bis)
Mit diesem Befehl wird der 'Verzug in Tagen' korrekt ausgegeben, eine Farbabstufung (Bedingte Formatierung) schafft Klarheit auf einen Blick. Für das Beispiel: In Spalte H, in der steht der Wert 'Zahlung_bis'. In Spalte J steht 'Eingang am'. Die Formatierung der Berechnungszelle sollte 'Standard' sein.
=WENN(ISTLEER(H2);""; WENN(ISTLEER(J2); WENN(ISTFEHLER (DATEDIF(H2;HEUTE();"d"));-DATEDIF (HEUTE();H2;"d"); DATEDIF(H2;HEUTE();"d")); WENN(ISTFEHLER(DATEDIF(J2;H2;"d")); DATEDIF(H2;J2;"d"); -DATEDIF(J2;H2;"d"))))
Da die Formel in der kompakten Schreibweise einigermaßen Schwer zu lesen ist, hier noch eine kommentierte Variante.
= WENN(
ISTLEER(H2); // ist Datum für 'Zahlung bis' vorhanden?
"nicht verschickt"; // falls noch nicht, kommentieren
WENN( // falls vorhanden, weiter berechnen
ISTLEER(J2); // ist Datum für 'Eingang am' vorhanden?
WENN( // falls nein, Verzug zu Heute berechnen
ISTFEHLER(DATEDIF(H2;HEUTE();"d")); // Reihenfolge feststellen
-DATEDIF(HEUTE();H2;"d"); // liegt Zahlungsfrist in Zukunft
DATEDIF(H2;HEUTE();"d") // sonst ist Zahlungsfrist überschritten
);
WENN( // ansonsten Verzug mit eingetragenen Datumswerten berechnen
ISTFEHLER(DATEDIF(J2;H2;"d")); // Reihenfolge feststellen
DATEDIF(H2;J2;"d"); // wurde mit Verzug nach Frist bezahlt
-DATEDIF(J2;H2;"d") // wurde vor Fristablauf bezahlt
)
)
)