Datum und Zeit - Berechnen oder Formatieren?

Version: 20091217 Autor: George Burghardt Datei: ./3_7.txt ./3_7.htm Zurück ./2_7.htm | Weiter ./4_7.htm

Grundlagen

Datums und Zeitangaben sind in Excel nur speziell formatierte Zahlen! Die Zahl vor dem Komma (ganze Tage) werden für die Datumsangaben, die Nachkommastellen (Bruchteile eines Tages) für die Uhrzeitangabe herangezogen! Beispiele: Die Zahl 1 entspricht dem 01.01.1900 (Zellformat TT.MM.JJJJ). Die Zahl 2 dem 02.01.1900, Die Zahl 3 dem 03.01.1900, ... , Die Zahl 36526 dem 01.01.2000 (100Jahre*365Tage/Jahr+26Schalttage) Es dürften nur 25 Schalttage sein, da es den 29.02.1900 nicht gab! Aber alle Daten ab dem 01.03.1900 berechnet Excel wieder korrekt! ( http://de.wikipedia.org/wiki/Schaltjahr ) Die Zahl 1,5 entspricht dem 01.01.1900 12:00 (Zellformat TT.MM.JJJJ hh:mm) Die Zahl 1/24 entspricht einer Stunde 01:00:00 (Zellformat hh:mm:ss) Die Zahl 6*1/24 entspricht 06:00:00 Die Zahl 1/24/60 entspricht einer Minute 00:01:00 Die Zahl 30*1/24/60 entspricht 00:30:00 (30 Minuten) aber auch 0,5*1/24 entspricht 00:30:00 Die Zahl 1/24/60/60 entspricht einer Sekunde 00:00:01 Die Zahl 25*1/24/60/60 entspricht 00:00:25 Die Zahl 3/24 + 5/(24*60) + 22/(24*60*60) = 0,128726851851852 entspricht 03:05:22 = 3Stunden + 5Minuten + 22Sekunden (Zellformat hh:mm:ss) Eigentlich ganz einfach ... ... 1 in Excel entsprechen 24 Stunden, 3 Stunden entsprechen somit 3/24 in Excel. ... 1 in Excel entsprechen 24*60 Minuten, 5 Minuten entsprechen 5/(24*60) in Excel. ... 1 in Excel entsprechen 24*60*60 Sekunden, 22 Sekunden entsprechen 22/(24*60*60) in Excel ... http://de.wikipedia.org/wiki/Dreisatz Tip! Wird eine Eingabe nicht mit [Enter] sondern mit [F9] abgeschlossen verwandelt Excel die Formel in ihr Ergebnis (in der Zelle steht dann z.B. nicht =6*1/24 sondern die Zahl 0,25)!

Anwendungen

Denkbare Anwendungen für Berechnungen mit Datum und Uhrzeit ... 1. Sie führen eine Rechnungverwaltung u.a. mit Rechnungsdatum und Zahlungsziel. Rechnungen, die überzogen (Zahlungsziel überschritten) sind sollen von Excel automatisch gekennzeichnet werden. (siehe Übung ... ) 2. Sie führen einen Stunden-Zettel, auf dem Sie Ihre Arbeitstunden notieren. Nun wollen Sie mit Excel nach einer Woche Ihr Einkommen berechen. (siehe Übung ... ) 3. In der Personalliste soll das aktuelle Alter der Mitarbeiter aus dem Geburtsdatum berechnet werden. (siehe Übung ... ) 4. In einer Vereinsliste (Mitgliederliste) soll neben den Geburtsdaten die aktuelle Anzahl der Tage bis zum nächsten Geburtstag stehen bzw an einem Geburtstag soll "HAPPY BIRTHDAY!" angezeigt werden (anstatt 0). Hinter der Anzahl der Tage soll "Tage" bzw "Tag" steht. (siehe Übung ... ) 5. Für eine minutengenaue Abrechnung werden Ihnen LOG-Dateien mit Kundennummern sowie Datum und Uhrzeit des Ein- sowie des Aus-Loggens bereitgestellt. Welche Beträge werden den einzelnen Kunden in Rechnung gestellt? (siehe Übung ... ) 6. In einer Monatsübersicht sollen die Wochenenden automatisch eingefärbt werden... 7. Das Zeiterfasungssystem liefert Ihnen 177,500 zu 177,533 Arbeitsstunden (Soll zu Ist). Überstunden sollen abgefeiert werden - wieviele Minuten könnten Sie heute früher gehen?

Datums- und Zeitwerte eingeben

Bei der Eingabe von Datumswerten sind "." "/" oder "-" als Trennzeichen geeignet. So können Sie z.B. den 15.05.2009 eingeben indem Sie 15/5/09 oder 15-5-09 eingeben. Excel erkennt, daß es sich hierbei um einen Datumswert handelt und formatiert die aktuelle Zelle automatisch. Löschen Sie später das eingegebene Datum, ist die Zelle auch weiterhin mit einem Datumsformat versehen und muss ggf. neu Formatiert werden. Excel erkennt, daß es sich hierbei um einen Datumswert handelt und formatiert die aktuelle Zelle automatisch so daß die Zahl 39948 als Datum 15.05.2009 bzw. 15.05.09 angezeigt wird. Löschen Sie später das eingegebene Datum, ist die Zelle auch weiterhin mit einem Datumsformat versehen und muss ggf. neu Formatiert werden. Zeitwerte können ebenfalls direkt eingegeben werden. Stunden, Minuten und Sekunden werden jeweils durch einen Doppelpunkt getrennt. Geben Sie z.B. 12:00:00 bzw. 12:00 ein, wendet Excel automatisch das Zeitformat hh:mm:ss bzw. hh:mm auf die Zahl 0,5 an. Zu beachten ist, daß trotz Eingabe eines Datumwertes (z.B. 01.01.2000) Excel diese sofort in eine Zahl (36526 = 01.01.2000) wandelt und nur durch die Formatzuweisung das Datum erscheint. Diese vorgehensweise wendet Excel auch bei Zeitangaben an!

Tastenkombinationen

[Strg]+[.] Fügt das aktuelle Datum ein [Strg]+[Umschalt]+[.] Fügt die aktuelle Uhrzeit ein Hinweis: Die so eingefügten Datum- und Uhrzeit-Werte verändern sich beim Speichern, Öffnen oder Drucken der Datei nicht! Im Gegensatz zu den Funktionen =HEUTE() und =JETZT()

Formatierungen

Bei der Eingabe von Datum- und Zeitwerten wird von Excel sofort das entsprechende Format zugewiesen. Zu beachten ist, daß trotz Eingabe eines Datumwertes (z.B. 01.01.2000) Excel diese sofort in eine Zahl (36526="01.01.2000") wandelt und nur durch die Formatzuweisung das Datum erscheint. Diese vorgehensweise wendet Excel auch bei Zeitangaben an! Jede Zelle kann als Datum (z.B. TT.MM.JJJJ) oder Zeit (z.B. hh:mm:ss) formatiert werden ohne das der Inhalt der Zelle verändert wird! So wird z.B. 0,25 mit hh:mm formatiert als 06:00 angezeigt, der Inhalt der Zelle ist aber immer noch 0,25! zum ausprobieren: 06:00 Stunden * 10,00 Euro pro Stunde = 60 Euro ? Gartenzaunphänomen bei "negativen-Zeiten" Werden negative Zahlen (z.B. als Ergebniss von Berechnungen) als Datum oder Uhrzeit formatiert kommt es zum Gartenzaunphänome Beispiele: 1. Liste von SollStunden IstStunden Differenz EndSumme 2. Rechnungsverwaltung/Terminplanung/Tage bis zu Rente... Es gibt nun mal keine negativen Zeiten sondern lediglich negative Werte und Excel rechnet absolut richtig mit ihnen, selbst wenn es kein Format für ihre Darstellung gibt, daher der 'Gartenzaun'... Eine andere und im Grunde bessere Methode bietet die Formel "=(A1-B1) * 24". Dadurch werden die Stunden zwar in Industriestunden umgewandelt. Allerdings bietet dies eine erleichterte Weiterberechnung, gerade in Bezug auf die Berechnung von Löhnen...

Funktionen

=heute() Aktuelles Datum =jetzt() Aktuelles Datum mit Uhrzeit Tip! Das leere Klammernpaar wird gerne vergessen. Ohne die Klammern sucht Excel einen Namen z.B. heute oder jetzt da es den aber meistens nicht gibt zeigt Excel den Fehler #NAME? an. Datum aus Einzelangaben zusammensetzen: =Datum(JahrWert;MonatWert;TagWert) z.B.: =DATUM(2000;1;1) => 1.1.2000 (Format TT.MM.JJJJ) bzw. 36526 (Format 0) =Datum(2010;5;13) => 13. Mai im Jahre 2010 =Datum(2010;5+2;13+7) => Datum des 13. Mai 2 Monate und 7 Tage später. Jahreszahl aus Datum auslesen: =Jahr(Datumsangabe) z.B. =Jahr("28.1.1967") => 1967 (als Zahl oder Standard formatiert) Bei der direkten Eingabe eines Datums muss dieses in Anführungszeichen eingeschlossen werden. oder: =Jahr(jetzt()) => aktuelles Jahr oder: =Jahr(A3) sofern in A3 ein Datum steht, wird das Jahr daraus als Zahl zurückgegeben Monatszahl aus Datum auslesen: =Monat(Datumsangabe) z.B. =Monat("28.1.1967") => 1 (als Zahl oder Standard formatiert) Bei der direkten Eingabe eines Datums muss dieses in Anführungszeichen eingeschlossen werden Tageszahl aus Datum auslesen: =Tag(Datumsangabe) z.B. =Tag("28.1.1967") => 28 (als Zahl oder Standard formatiert) Bei der direkten Eingabe eines Datums muss dieses in Anführungszeichen eingeschlossen werden Wochentag als Zahl zurückgeben: =Wochentag(Datumsangabe) z.B. =WOCHENTAG("28.1.1967") => 7 (als Zahl oder Standard formatiert) 7 entspricht dem Samstag Die Excel-Woche beginnt mit dem Sonntag (=1) und endet mit dem Samstag(=7) Datum aus Einzelangaben zusammensetzen Teil 2: = Datum(Jahr-Wert;Monats-Wert;Tages-Wert) z.B. =Datum(Jahr(heute());12;24) => Diesjähriges Weihnachten oder: =Datum(Jahr(heute());Monat(A1);Tag(A1)) => Diesjähriger Geburtstag oder: =Wochentag(Datum(Jahr(heute())+10;12;31)) => Wochentag von Silvester in 10 Jahren Bitte berechnen Sie: Wie viele Tage leben Sie schon? Wie viele Tage haben Sie gelebt, wenn Sie Ihren 66. Geburtstag feiern? (und wie viele Schaltjahre haben Sie dabei erlebt)? In A1 soll die aktuelle Uhrzeit der Öffnung der Mappe stehen. In A2 soll das Datum von heute stehen, das sich bei einem erneuten Öffnen der Datei nicht verändert. Wie viele Tage sind es noch bis Weihnachten? Auf welchen Wochentag fällt dieses Jahr Ihr Geburtstag? Wie viele Tage ist Ihr letzter Geburtstag schon her, in wie vielen Tagen ist Ihr nächster Geburtstag? Wie viele Tage leben Sie schon? Weil Excel jedes Datum als Anzahl von Tagen behandelt, können wir einfach den Geburtstag vom heutigen Datum abziehen und erhalten das gesuchte Ergebnis: Mengenlehre: Der Menge aller Tage vom 1.1.1900 bis heute die Menge aller bis dahin nicht von Ihnen gelebten Tage (also Tage vom 1.1.1900 bis zu Ihrer Geburt) abziehen: Rest: Ihre bisher verlebten Tage Formel: wenn in A1 Ihr Geburtstag steht, bzw. wenn der 15.8.1985 Ihr Geburtstag ist = HEUTE()-A1 = HEUTE()-"15.8.1985" Achten Sie darauf, das Zellenformat auf Standard zu stellen! Wie viele Tage haben Sie gelebt, wenn Sie Ihren 66. Geburtstag feiern; wie viele Schaltjahre haben Sie dabei erlebt? Gleiches Prinzip wie eben, nur dass wir nun das Datum Ihres Geburtstages von dem Datum Ihres 66. Geburtstages abziehen müssen. Letzteres erhalten wir, indem wir einfach beim Datum Ihres Geburtstages die Jahreszahl um 66 erhöhen Formel: wenn in A1 Ihr Geburtstag steht schreiben Sie in B1: = DATUM(JAHR(A1)+66;MONAT(a1);TAG(a1)) ?A1 In A1 soll die je aktuelle Uhrzeit der Öffnung der Mappe stehen. Die Uhrzeit entspricht der Nachkommastelle der Datumszahl. Ich erhalte sie, indem ich das, was vor dem Komma steht auf Null setze, indem ich das Datum von heute() von Datum mit Uhrzeit von jetzt() abziehe. Formel: Zellenformat ?Uhrzeit? = jetzt()-heute() In A2 soll das Datum von heute stehen, das sich bei einem erneuten öffnen der Datei nicht verändert. Der Wert darf nicht berechnet werden, weil er sich sonst bei jedem Öffnen des Dokuments verändern würde. Entweder lassen Sie sich das aktuelle Datum mit =heute() ausgeben, kopieren es als Datum und fügen es anschließend mit Bearbeiten/ Inhalte einfügen/ Werte wieder ein. Stellen Sie das Zellformat auf ?Datum? Formel: Gehen Sie nach A2, drücken Sie STRG + Umschalt + Doppelpunkt Wie viele Tage sind es noch bis Weihnachten? Wie bei den Lebenstagen: Datum von Weihnachten künstlich erzeugen, davon das heutige Datum abziehen. Formel: = DATUM(JAHR(HEUTE());12;24)-HEUTE() Auf welchen Wochentag fällt dieses Jahr Ihr Geburtstag? Mit Wochentag-Formel den Wochentag bestimmen (und dann mittels der SVerweis-Funktion den richtigen Tag der Zahl zuordnen ...) Formel: in A1 steht Ihr Geburtstag =Wochentag(A1) Wie viele Tage ist Ihr letzter Geburtstag schon her, in wie vielen Tagen folgt Ihr nächster? Zuerst müssen Sie prüfen, ob Sie in diesem Jahr bereits Geburtstag hatten. Ihr diesjähriger Geburtstag ergibt sich aus dem aktuellen Jahr und Monat und Tag von Ihrem Geburtstag. Wenn Ihr Geburtstag schon vorbei ist, dann ist die heutige Datumszahl von Excel größer als die Datumszahl Ihres diesjährigen Geburtstags, weil heute weiter vom 1.1.1900 entfernt ist. Wenn Sie in diesem Jahr bereits Geburtstag hatten, dann müssen Sie dieses Datum vom heutigen Datum abziehen und das Zellformat mit Standard festlegen. Andernfalls müssen Sie Ihren Geburtstag des Vorjahres jahr(heute())-1 vom heutigen Datum abziehen. Formel: wenn in A1 Ihr Geburtstag steht und in B1 Ihr diesjähriger Geburtstag Ihr diesjähriger Geburtstag in B1: =DATUM(JAHR(HEUTE());MONAT(A1);TAG(A1)) Ihr Geburtstag im Vorjahr in C1: =DATUM(JAHR(HEUTE())-1;MONAT(A1);TAG(A1)) Bedingung: HEUTE()>B1 Dann: HEUTE()-B1 Sonst: heute()-datum(JAHR(b1)-1;MONAT(b1);TAG(b1)) Also: =WENN(HEUTE()>B1;HEUTE()-B1;HEUTE()-DATUM(JAHR(HEUTE())-1;MONAT(A1);TAG(A1))) Oder: = WENN(HEUTE()>B1;HEUTE()-B1;HEUTE()-C1) Der nächste Geburtstag: Ihr Geburtstag im nächsten Jahr in D1: =DATUM(JAHR(HEUTE())+1;MONAT(A1);TAG(A1)) Bedingung: HEUTE()>B1 (?der Geburtstag war schon in diesem Jahr) Dann: D1- HEUTE() ...
Datum und Zeit - Nur eine Frage der Formatierung?