In dieser Ausgabe erfahren Sie unter anderem, wie Sie in der Tabellenkalkulation Calc mit Datumswerten und Zeiten rechnen, Excel-kompatible Kalenderwochen erzeugen und warum der 1. Januar auf keinen Fall in der achten Kalenderwoche liegt.

Tipp: Geboren am 4. Juli

Möchten Sie genau wissen, wie viele Tage Sie schon auf diesem Planeten weilen? Dann geben Sie in der Tabellenkalkulation OpenOffice Calc in eine Zelle die Formel

=JETZT()-Ihr_Geburtsdatum

ein. Anstelle von Ihr_Geburtsdatum schreiben Sie ein Datum hin, also beispielsweise 4.7.1978. In der Zelle erscheint anschließend Ihr Alter in Tagen. Die Stellen hinter dem Komma können Sie dabei vernachlässigen (Abbildung 1). Übrigens: Nachdem das Ergebnis in der Zelle erscheint, sehen Sie in der Eingabezeile, wieviele Tage Ihr Geburtstag hinter dem Beginn der Calc-Zeitrechnung liegt.

Abbildung 1: Altersformel: Wer am 4.7.1978 geboren ist, lebt schon mehr als 10.000 Tage.

Tipp: Wann begann die Zeitrechnung nach Calc?

Wie kommt Calc eigentlich darauf, welcher Tag heute ist? Die Funktion JETZT() zeigt das aktuelle Datum an. Wenn Sie wissen möchten, wann der Tag 0 in Calc ist, dann geben Sie in die Eingabezeile den Wert 0 ein. Diesen Wert müssen Sie in ein Datumsformat umwandeln. Wählen Sie dazu Format / Zellen / Zahlen und dann in der Liste Kategorie den Eintrag Datum. Als Format markieren Sie 31.12.1999 und bestätigen mit OK (Abbildung 2). Anschließend sehen Sie das Datum 30.12.1899. Das ist der Standard-Starttag aller Datumsberechnungen. Unter Extras / Optionen / OpenOffice.org Calc / Berechnen können Sie im Bereich Datum zwei weitere wählen: Der 1. Januar 1900 war der Standard bis zur Version StarCalc 1.0, der 1. Januar 1904 ist in der Apple-Version das Ausgangsdatum.

Abbildung 2: Um das früheste darstellbare Datum zu ermitteln, formatieren Sie eine Zelle mit dem Inhalt “0” als Datum.

Tipp: Geboren im Jahre 3 vor Calc

Das Arbeiten mit dem Startdatum von Calc funktioniert auch mit Berechnungen, die vor diesem Datum liegen. Nehmen wir an, Ihr Urgroßvater ist genau sieben Jahre älter als der unverwüstliche Schauspieler Johannes Heesters. Er ist am 5.12.1896 geboren und hat – weil er täglich sein Glas Wasser getrunken hat – vor einigen Wochen seinen 110. Geburtstag gefeiert. Jetzt möchten Sie herausfinden, wie viele Tage der alte Herr schon erlebt hat. Das geht wieder über die Formel =JETZT()-5.12.1896. Werfen Sie nach dieser Berechnung einen Blick in die Eingabezeile, sehen Sie, dass Calc daraus korrekt =JETZT()–1120 gemacht hat: Zieht man eine negative Zahl ab, muss man sie zur ersten hinzuzählen (Abbildung 3).

Abbildung 3: So rechnet Calc richtig: Einem Datum vor der Calc-Zeitrechnung stellt das Programm einfach ein Minus voran.

Tipp: Formeln darstellen

In Zellen mit Formeln zeigt Calc normalerweise das Ergebnis an. Haben Sie einen Fehler in der Formel, erhalten Sie eine entsprechende Fehlermeldung. Möchten Sie statt des Ergebnisses lieber die Formel selbst sehen, wählen Sie Extras / Optionen / OpenOffice.org Calc / Ansicht und schalten im Bereich Anzeigen die Formeln ein. Dasselbe geht aber auch ohne das Ändern der Optionen. Nehmen Sie einfach die FORMEL(Zelle)-Funktion zuhilfe, um sich sowohl die Ergebnisse als auch die Formeln anzeigen zu lassen. Dazu geben Sie zum Beispiel in die Zelle A1

=FORMEL(B1)

ein. Dann zeigt Calc in der Zelle A1 die Formel aus der Zelle B1, in der Zelle B1 hingegen weiterhin das Ergebnis der Formel (Abbildung 4).

Einziger Haken: Steht in der abgefragten Zelle gar keine Formel, gibt Calc die Fehlermeldung #NV aus. Aber die schalten Sie mit wenig Aufwand ab: Kombinieren Sie dazu einfach die FORMEL-Funktion mit den Funktionen WENN() und ISTFORMEL():

=WENN(ISTFORMEL(B1);FORMEL(B1);"")

Das bewirkt Folgendes: Steht in der Zelle B1 eine Formel, zeigt diese Funktion sie an. Befindet sich in der Zelle keine Formel, sondern eine Zahl, ein Text oder auch nichts, sehen Sie nichts.

Abbildung 4: So zeigt Calc alles: Mit der FORMEL-Funktion machen Sie Formeln und Ergebnisse sichtbar.

Tipp: Zeitdifferenzen ausrechnen

Möchten Sie für ein Projekt die Stunden abrechnen, die Sie dafür aufgewendet haben, machen Sie das in Calc üblicherweise so: Sie schreiben zum Beispiel in die Zelle A1 die Uhrzeit, zu der Sie mit der Arbeit begonnen haben, und in die Zelle B1 die Zeit, zu der Sie aufgehört haben. In der Zelle C1 berechnen Sie die Differenz dieser beiden Zeitangaben.

Verwenden Sie dazu die Formel =B1-A1, geht das in den meisten Fällen gut, denn die zweite Zeitangabe ist gewöhnlich größer als die erste, wenn Sie zum Beispiel um 14.00 Uhr mit einem Job begonnen und ihn um 18.00 Uhr beendet haben. Ein falsches Ergebnis erhalten Sie, falls Sie bis nach Mitternacht arbeiten: Haben Sie um 14:00 Uhr angefangen und waren erst um 1:00 Uhr in der Nacht fertig, ergibt die genannte Formel eine Arbeitszeit von -13:00 Stunden. Rechneten Sie einen Tag, also 24 Stunden, dazu, stimmte die Rechnung wieder.

Die Ursache für diese “Rechenschwäche”: Die Uhrzeitfunktionen in Calc rechnen nur in Bruchteilen von Tagen, während ganze Zahlen immer ganze Tage repräsentieren. Die Angabe 12:00 etwa ist durch den Wert 0,5 vertreten, 18:00 durch den Wert 0,75 und 1:00 in der Nacht durch 0,04 hat. Wenn Sie eine Zelle mit einer Uhrzeitangabe als Tag formatieren, zeigt Calc als Ergebnis immer 30.12.1899 an.

Mit dem Verständnis für diese Rechenweise können Sie nun leicht eine Formel erzeugen, die Ihre Arbeitszeiten auch über den Tag hinaus richtig berechnet. Die Frage ist nur: Wie soll Calc wissen, dass die Eingabe 1:00 in der Zelle B1 schon den folgenden Tag meint, und daher eine 1 hinzurechnen?

Dazu nutzen Sie logische Wahrheitswerte. Damit erweitern Sie die Differenzformel so, dass Calc mit Hilfe eines Vergleichsoperators zunächst überprüft, welcher Wert größer ist:

=(B1<A1)+B1-A1

Im Klartext bedeutet die Formel (Abbildung 5): Wenn die zweite Zeitangabe in Zelle B1 kleiner ist als die erste in A1, ist (B1<A1) wahr, ansonsten falsch. Eine wahrer Wert ergibt immer 1, ein falscher Wert immer 0. So zählt Calc dann entweder eine 1 oder eine 0 zur Differenz der Stunden dazu, und die Formel rechnet Zeiten korrekt.

Abbildung 5: Gewusst wie: Wenn Sie logische Werte hinzuzählen, können Sie mit Calc Zeitangaben richtig berechnen.

Tipp: Unterschiedliche Datumsschreibweisen benutzen

Normalerweise gibt man ein Datum mit Punkten zwischen Tag, Monat und Jahr ein. Sie können stattdessen aber auch ein Minuszeichen oder einen Schrägstrich als Trenner benutzen – Calc interpretiert diese Zeichen bei Eingaben wie 20-2 oder 20/2 nicht als arithmetische Operatoren, sondern als Datumsangaben, und setzt sie entsprechend um.

Tipp: Feiertage ausrechnen

In Berechnungen, die kalendarische Daten enthalten, gilt es mitunter, auch die Feiertage zu berücksichtigen. Die kann man zwar von Hand eingeben; allerdings ist das nicht besonders effektiv – schließlich soll der Computer ja die Arbeit erleichtern. Da trifft es sich, dass Sie sich von Calc die variablen Feiertage auch berechnen lassen können.

Das Jahr beginnt mit den festen Feiertagen Neujahr (1. Januar) und Heilige Drei Könige (6. Januar, nur in Baden-Württemberg und Bayern). Diese Feiertage müssen Sie von Hand eingeben. Der erste bewegliche Feiertag, den es zu berechnen gilt, ist der Karfreitag.

Basis für alle Feiertagsberechnungen ist der Ostersonntag. Den kriegen Sie in Calc ganz einfach mit der Formel

=OSTERSONNTAG(2008)

heraus. Den Karfreitag ermitteln Sie danach genauso schnell mit der Formel OSTERSONNTAG(2008)-2. Ähnliches gilt für den Ostermontag. Die anderen variablen Feiertage rechnen Sie ebenso mit Hilfe der OSTERSONNTAG-Funktion aus:

• Christi Himmelfahrt: =OSTERSONNTAG(2008)+39
• Pfingstsonntag: =OSTERSONNTAG(2008)+49
• Pfingstmontag: =OSTERSONNTAG(2008)+50
• Fronleichnam: =OSTERSONNTAG(2008)+60

Fronleichnam ist in Baden-Württemberg, Bayern, Hessen, Nordrhein-Westfalen, Rheinland-Pfalz, im Saarland sowie in Teilen Sachsens und Thüringens ein Feiertag.

Fehlt noch der Buß- und Bettag. Dieser bewegliche Feiertag wurde 1995 in den meisten Bundesländern zugunsten der Finanzierung der Pflegeversicherung abgeschafft. Nur in Sachsen ist er noch ein Feiertag. Das Berechnen des Datums ist aber nicht so einfach wie bei den anderen beweglichen Festen, er fällt immer auf den Mittwoch vor dem letzten Sonntag des Kirchenjahrs. Das Kirchenjahr beginnt am ersten Advent. Der liegt drei Wochen vor dem vierten Adventssonntag, welcher wiederum der letzte Sonntag vor dem ersten Weihnachtstag ist.

Der erste Weihnachtstag fällt jedes Jahr auf den 25. Dezember. Um nun herauszufinden, auf welchen Wochentag ein Datum fällt, bietet Calc die Funktion WOCHENTAG(Datum):

=WOCHENTAG(25.12.2008)

ergibt 5. Das bedeutet, der erste Weihnachtstag im Jahr 2008 fällt auf einen Donnerstag. (Calc nummeriert die Tage von 1 bis 7, wobei die Zählung mit dem Sonntag beginnt.) Von da aus zum vierten Adventssonntag zurück zu gelangen, geschieht mit folgender Rechnung:

=25.12.2008-WOCHENTAG(25.12.2008-1)

Von der Zahl, die Sie mit der Wochentag-Funktion berechnen, müssen Sie 1 abziehen, weil der Sonntag in dieser Funktion 1 ergibt, und nicht 0. Als Ergebnis dieser Berechnungen sehen Sie zunächst die Zahl 39803 und nach dem Formatieren der Zelle als Datum die korrekte Anzeige 21.12.2008. Subtrahieren Sie von diesem Datum 28 Tage und 4 Tage, um zu dem Mittwoch zu gelangen, an dem der Buß- und Bettag ist. Die komplette Formel dafür sieht so aus, wie in Abbildung 6 in Zeile 16 zu sehen:

=25.12.2008-WOCHENTAG(25.12.2008-1)-28-4

Abbildung 6: Alle beweglichen Festtage außer dem Buß- und Bettag errechnen Sie mit Hilfe der “OSTERSONNTAG”-Funktion.

Tipp: Wie viele Arbeitstage hat das Jahr 2008?

Möchten Sie wissen, wie viele Tage Sie im Jahr 2008 arbeiten müssen? Das rechnen Sie mit Hilfe von Calc schnell aus. Die Tabellenkalkulation verfügt über ein Analysis-Add-in, das schon bei der Installation mit eingerichtet wird. In diesem Add-in finden Sie die Funktion NETTOARBEITSTAGE(Anfang;Ende;Freie Tage), mit der Sie die Anzahl der Arbeitstage ausrechnen können. Geben Sie ein Anfangs- und ein Enddatum als Parameter ein, zeigt Calc die Zahl der Arbeitstage zwischen diesen beiden Tagen:

=NETTOARBEITSTAGE(1.4.2008;30.4.2008)

zeigt die Zahl der Arbeitstage im April 2008, nämlich 22. Würden Sie diese Funktion auf den Januar anpassen, käme 23 heraus, weil der Neujahrstag nicht automatisch als Feiertag berücksichtigt wird. Der 6. Januar 2008 fällt auf einen Sonntag, ist also sowieso arbeitsfrei:

=NETTOARBEITSTAGE(1.1.2008;31.1.2008)

Tatsächlich hat der Januar 2008 aber nur 22 Arbeitstage. Damit die Funktion richtig rechnet, geben Sie die Anzahl der Feiertage als dritten Parameter an. Das kann ein einzelner Feiertag sein oder eine Liste an anderer Stelle in dem Tabellendokument – zum Beispiel die Feiertagstabelle aus dem vorhergehenden Tipp Feiertage ausrechnen. Stehen die Feiertagstermine etwa, wie in Abbildung 6 zu sehen, in den Zellen B2 bis B18, berechnen Sie so die Arbeitstage im Januar:

=NETTOARBEITSTAGE(1.1.2008;31.1.2008;B2:B18)

Nun erhalten Sie das korrekte Ergebnis 22. So können Sie die Arbeitstage der weiteren Monate ebenso schnell ausrechnen, Sie müssen dazu nur die Datumsangaben entsprechend anpassen.

Tipp: 2008 ist ein Schaltjahr

Vielleicht wissen Sie es auch so: 2008 ist ein Schaltjahr, weil die Jahreszahl durch 4 und nicht durch 100 teilbar ist. Der Februar hat also 29 Tage, nicht nur 28. Das wirkt sich natürlich auch in Calc aus, etwa wenn Sie Nettoarbeitstage errechnen (siehe Tipp Wie viele Arbeitstage gibt es 2008?). Dabei hilft eine Formel wie =NETTOARBEITSTAGE(1.2.2008;29.2.2008). Doch was machen Sie im nächsten Jahr 2009? Das ist kein Schaltjahr, und Sie müssten die Formel wieder anpassen.

Besser ist es, Sie definieren die Formel gleich so, dass sie immer gültig ist. Dazu bietet Calc Ihnen die Funktion ISTSCHALTJAHR(Datum), die einen logischen Wahrheitswert zurückgibt: 1, wenn dieses Datum in einem Schaltjahr liegt, 0 für jedes andere Jahr. Wenn Sie diese Funktion nun in einer Bedingung formulieren und entsprechend in die Formel integrieren, haben Sie die Allgemeingültigkeit (Abbildung 7):

=NETTOARBEITSTAGE(1.2.2008;WENN(ISTSCHALTJAHR(28.2.2008);29.2.2008;28.2.2008))

Zunächst prüft die Funktion, ob der 28.2.2008 in ein Schaltjahr fällt. Ist das – wie hier – der Fall, rechnet Calc mit dem 29.2.2008, sonst mit dem 28.2.2008. Wichtig: Prüfen Sie nicht mit ISTSCHALTJAHR(29.2.2008). Den 29. Februar gibt es zwar in diesem Jahr, nicht aber in den drei Jahren danach und davor. Oder verwenden Sie einfach die folgende Formel: =NETTOARBEITSTAGE(1.2.2008;1.3.2008-1).

Abbildung 7: Die “ISTSCHALTJAHR”-Funktion kümmert sich darum, das richtige Enddatum im Februar zu finden.

Tipp: Rechnen mit Kalenderwochen

Die Norm ISO 8601 definiert seit den 70er Jahren des vergangenen Jahrhunderts den Montag als den ersten Tag der Woche. Begrifflich komplizierter ist dagegen die erste Woche eines Jahres zu fassen. Diese ist so definiert, dass immer der erste Donnerstag im Januar in diese Woche fällt. Dadurch fällt der 4. Januar immer in die erste Woche, auch, wenn es ein Sonntag ist – normalerweise der letzte Tag einer Woche.

Um die Kalenderwoche auszurechnen, bietet Calc die Funktion KALENDERWOCHE(Datum;Modus). Als Modus verwenden Sie 2, das definiert den Montag als ersten Wochentag. Schreiben Sie in dieses Feld 1 rein, ist Sonntag der erste Tag der Woche, wie das zum Beispiel die USA handhaben. Ohne Angabe des Modus gibt Calc eine Fehlermeldung zurück. An den folgenden Beispielen sehen Sie, wie sich die unterschiedlichen Modi auf den 1. Januar 2006 auswirken, der auf einen Sonntag fiel:

• KALENDERWOCHE(“1.1.2006”;1) ergibt 1
• KALENDERWOCHE(“1.1.2006”;2) ergibt 52 (ISO-8601-konform)

Tipp: OpenOffice berechnet falsche Kalenderwochen

Wenn Sie die Wochenberechnungen in Abbildung 8 betrachten, müssten Sie eigentlich zu dem Schluss kommen, dass die Funktion KALENDERWOCHE(Datum;Modus) falsch rechnet. Dieser Eindruck täuscht aber, auch wenn anhand der Formel drei verschiedene Werte herauskommen. Richtig sind die Ergebnisse in den Zellen A1 und A2, allerdings liefert nur die Formel in A2 eine ISO-konforme Wochenzahl.

Die Formel in der Zelle A4 ist identisch mit der in A1. Doch anstatt einer 1 liefert sie das falsche Ergebnis 52 zurück. Noch unerklärlicher ist das Ergebnis 8 in Zelle A5. Dier Formel ist identisch mit der in der Zelle A2, wo korrekt 52 zu sehen ist.

Wieso ist das so? Wir haben die Zellen A1 und A2 als Zahl formatiert, die Zellen A4 und A5 dagegen als Kalenderwoche – über das Menü Format / Zelle / Zahlen in der Kategorie Datum. Dort finden Sie in der Format-Liste den Eintrag 52, der den Format-CodeWW für “Kalenderwochen” repräsentiert (Abbildung 9). Was dort nicht steht: In Verbindung mit der KALENDERWOCHE-Funktion erzeugt dieses Format eine fehlerhafte Anzeige. Ändern Sie stattdessen das Zahlenformat auf Zahl und Standard, erscheinen die richtigen Werte.

Abbildung 8: Wie geht das: Dieselben Formeln liefern unterschiedliche Ergebnisse?

Abbildung 9: Erwischt: Der Formatcode “WW” erzeugt die nicht nachvollziehbaren Fehler.

Tipp: Excel-kompatible Kalenderwochen

OpenOffice steht traditionell anderen Office-Programmen sehr aufgeschlossen gegenüber. Das gilt auch für den – mitunter notwendigen – Datenaustausch mit Excel-Nutzern. Allerdings sollten Sie beim Datenverkehr nicht die Fehler der Microsoft-Kalkulation übernehmen. Die Funktion KALENDERWOCHE_ADD(Datum;Rückgabetyp) berechnet Wochennummern genau wie die Tabellenkalkulation Microsoft Excel – und genauso falsch. Denn die Excel-Funktion KALENDERWOCHE berücksichtigt nicht die Vorschrift der ISO-Norm 8601, nach der in die erste Kalenderwoche immer mindestens vier Januartage fallen. Schön zu sehen ist das am Beispiel des 1. Januar 2006, einem Sonntag. Nach der Excel-Funktion dauert die erste Kalenderwoche nur einen einzigen Tag.

Die Funktion KALENDERWOCHE_ADD ist verfügbar, wenn das Analysis-Add-in installiert ist. Das ist unter Linux allerdings immer der Fall. Die Funktion hat die gleiche Syntax wie die OpenOffice-Funktion KALENDERWOCHE. Wenn Sie als Rückgabetyp 1 einsetzen, beginnt die Woche am Sonntag. Schreiben Sie eine 2 hin, ist der Wochenstart am Montag. Eine ISO-konforme Kalenderwoche erhalten Sie also zum Beispiel mit dieser Formel:

=KALENDERWOCHE_ADD("2.1.2006";2)

Wenn Sie diese Zelle als normale Zahl formatieren, erhalten Sie als Ergebnis 2 anstelle des richtigen Wertes 1 (Abbildung 10). Damit die richtige Kalenderwoche in Calc auch angezeigt wird, müssen Sie Zellen mit dieser Funktion als Kalenderwoche mit dem Formatierungs-Code WW formatieren. Das erledigen Sie unter Format / Zellen / Zahlen / Kategorie: Datum. Wählen Sie dort in der Liste Format die 52; hinter dieser Zahl verbirgt sich der Formatcode für die Kalenderwoche.

Abbildung 10: Nur mit dem Formatcode “WW” (Zellen A10 und A11) zeigt Calc die richtige Kalenderwoche für die “KALENDERWOCHE_ADD”-Funktion an.

Tipp: Fehlermeldungen in Calc-Tabellen erzeugen

Mitunter geraten Tabellendokumente sehr komplex. Da wäre es schön, wenn Sie bestimmte Werte auf Gültigkeit überprüfen könnten. In Calc geht das – Sie müssen nur entsprechende Formeln dafür schreiben.

Angenommen, in der Zelle A16 muss ein bestimmter Wert stehen. Falls dort allerdings kein Zahlenwert steht, erscheint die Fehlermeldung “#NV” für “Nicht verfügbar”. Diesen Fehlerstatus können Sie in Calc abfragen, die Funktion dazu heißt ISTNV(Zelle). An eine Bedingung geknüpft, erscheint schnell ein kurzer Text, ob alles in Ordnung ist oder ob ein Wert fehlt:

=WENN(ISTNV(A16);"Wert in Zelle A16 fehlt";"OK")

Diese Formel prüft, ob in der Zelle der “#NV”-Fehler auftritt. Ist das der Fall, erscheint ein Hinweis, sonst nur der Text OK. Genauso fragen Sie weitere Fehlermeldungen ab:

• ISTBEZUG(Zelle) prüft, ob es sich bei dem Inhalt einer oder mehrerer Zellen um Bezüge handelt, und gibt einen Wahrheitswert zurück.
• ISTFEHL(Zelle) liefert “WAHR”, wenn die geprüfte Zelle einen Fehlerwert ungleich “#NV” enthält.
• ISTFEHLER(Zelle) prüft Zellen auf allgemeine Fehlerwerte und erkennt auch den Fehlerwert “#NV”. Es wird ein Wahrheitswert geliefert.
• ISTKTEXT(Zelle) prüft, ob in der Zelle Text oder Zahlen stehen. Bei Text liefert die Funktion als Ergebnis “FALSCH”.
• ISTLEER(Zelle) liefert “WAHR”, wenn eine Zelle leer ist. Eine Zelle mit einer Formel ist niemals leer, selbst wenn das Ergebnis der Formelberechnung ein leerer Text ist.
• ISTLOG(Zelle) liefert “WAHR”, wenn in der Zelle ein logisches Zahlenformat steht. Die Funktion hilft, auf WAHR- und FALSCH-Werte zu testen.
• ISTTEXT(Zelle) liefert im Gegensatz zur ISTKTEXT-Funktion den Wahrheitswert “WAHR”, wenn in der geprüften Zelle Text steht.
• ISTZAHL(Zelle) liefert “WAHR”, wenn in der geprüften Zelle eine Zahl steht.

Es gibt noch mehr solche Abfragefunktionen. Allerdings reichen die genannten aus, um Fehlermeldungen abzufangen und zum Beispiel auf einem anderen Tabellenblatt auszugeben.

Noch genauer geht das mit der Funktion FEHLERTYP. Diese liefert die Nummern der Fehlercodes zurück, wenn in einer Zelle etwas Falsches steht. Mit Hilfe der Nummer können Sie gegebenenfalls eigene Fehlertexte im Dokument ausgeben. Dafür stehen Ihnen verschiedene Fehlercodes zur Verfügung, die von 501 (ungültiges Zeichen) bis 527 (interner Überlauf) durchnummeriert sind.