Tipp 0448 Datumszuweisung in Tabellenzelle
Autor/Einsender:
Datum:
  Angie
18.04.2005
Entwicklungsumgebung:   Excel
Aufbauend auf den Tipp Datumseingabe prüfen, in dem gezeigt wird, wie eine Datumseingabe in TextBoxen auf einer UserForm auf "Gültigkeit" geprüft werden kann, wird hier auf die Zuweisung eines Datums in eine Tabellenzelle eingegangen.
Excel unterstützt zwei Datumssysteme: Das 1900- und das 1904-Datumssystem. In Microsoft Excel für Windows wird standardmäßig das 1900-Datumssystem verwendet. Das heißt also, dass in einem Excel-Tabellenblatt nur Daten ab 1. Januar 1900 als Datum dargestellt werden können!
1900-Datumssystem
Im 1900-Datumssystem ist der 1. Januar 1900 der erste Tag, der unterstützt wird. Wenn Sie ein Datum eingeben, wird dieses Datum in eine fortlaufende Zahl umgewandelt, die für die Anzahl der Tage steht, die seit dem 1. Januar 1900 vergangen sind. Wenn Sie zum Beispiel den 5. Juli 1998 eingeben, wandelt Microsoft Excel das Datum in die fortlaufende Zahl 35981 um.
1904-Datumssystem
Im 1904-Datumssystem ist der 1. Januar 1904 der erste Tag, der unterstützt wird. Wenn Sie ein Datum eingeben, wird dieses Datum in eine fortlaufende Zahl umgewandelt, die für die Anzahl der Tage steht, die seit dem 1. Januar 1904 vergangen sind. Wenn Sie zum Beispiel den 5. Juli 1998 eingeben, wandelt Microsoft Excel das Datum in die fortlaufende Zahl 34519 um.
In Microsoft Excel kann für jede Arbeitsmappe ein anderes Datumssystem festgelegt werden. Wenn das Datumssystem für eine Arbeitsmappe geändert wird, die bereits Datumsangaben enthält, werden die Datumsangaben um vier Jahre und einen Tag verändert.
Weitere Infos zum Thema Excel und Datum können der VB(A)-Hilfe und den Links zum Thema entnommen werden.
Vergleich Datumszuweisung mit Typumwandlung und einfache String-Übergabe
Je nachdem wie einer Zelle ein Datumsausdruck zugewiesen wird, mit vorheriger Typumwandlung beispielsweise mit CDate oder einfache String-Übergabe, wird das Datum von Excel als solches automatisch erkannt und auch interpretiert.
Die beiden folgenden Anweisungen können zu unterschiedlichen Ergebnissen führen:
 
'Umwandlung in ein Datum
Cells(1, 1).Value = CDate("03-11-2004")

'String-Übergabe
Cells(1, 1).Value = "03-11-2004"
 
Welche gravierenden Unterschiede es gibt, kann folgender Tabelle entnommen werden, getestet mit Gebietsschema Deutsch (Deutschland), ohne im Gebietsschema selbst Veränderungen vorzunehmen.
Als Datumsausdruck wurde ein String angenommen, das beispielsweise eine Eingabe in einer TextBox entspricht. Für eine eindeutige Datumsausgabe, wurde den Spalten 'CDate(String)' und 'String' das Zahlenformat (NumberFormat-Eigenschaft) dd. mmmm yyyy zugewiesen.
  Datum (Eingabe) CDate(String) String 1) Datum 2)
  Beabsichtigtes Rückgabe-Datum: 3. November 2004
1 3. November 2004 03. November 2004 3. November 2004 nein
2 November 3, 2004 03. November 2004 03. November 2004 ja
3 November 3 2004 03. November 2004 November 3 2004 nein
4 3 November 2004 03. November 2004 03. November 2004 ja
5 03.11.2004 03. November 2004 03.11.2004 nein
6 03-11-2004 03. November 2004 11. März 2004 ja
7 03/11/2004 03. November 2004 11. März 2004 ja
8 11/03/2004 11. März 2004 03. November 2004 ja
9 11/03/04 11. März 2004 03. November 2004 ja
10 04/11/03 04. November 2003 11. April 2003 ja
11 2004/11/03 03. November 2004 03. November 2004 ja
12 2004-11-03 03. November 2004 03. November 2004 ja
1) Beim Test mit den Gebietsschemata Englisch (Großbritannien (UK)) und Englisch (USA) wurden in den Zellen in der Spalte 'String' (einfache String-Übergabe) die selben Daten und Texte angezeigt!
  Die Werte für die Spalte 'CDate(String)' (Datumsausdruck mit CDate in ein Datum umgewandelt) entsprachen den jeweiligen Rückgaben in der Tabelle in Tipp Datumseingabe prüfen.
2) Die Anzeige des Datums im Tabellenblatt richtet sich nach dem Zahlenformat der Zelle. Bei der Eingabe eines Datums, das von Excel als solches erkannt wird, wechselt das Zellformat vom Zahlenformat "Standard" zu einem vordefinierten Datumsformat. Standardmäßig wird das Datum in einer Zelle rechtsbündig ausgerichtet. Kann Excel das Datumsformat nicht erkennen, wird das Datum als Text eingegeben, der in der Zelle linksbündig angeordnet ist. Die Rückgabewerte beziehen sich hier auf die Spalte 'String'.
Wie bereits geschrieben, im 1900-Datumssystem ist der 1. Januar 1900 der erste Tag, der von Excel unterstützt wird. Wenn Sie ein Datum eingeben, wird dieses Datum in eine fortlaufende Zahl umgewandelt, die für die Anzahl der Tage steht, die seit dem 1. Januar 1900 vergangen sind. Das heißt, in einem Excel-Tabellenblatt können nur Datumswerte ab 1. Januar 1900 als Datum dargestellt werden, frühere Daten nur als Text.
Bei der Zuweisung eines Werts als Datum (z. B. Typumwandlung mit CDate, Datumsliteral oder Date) bei Daten vor dem 30. Dezember 1899 wird der
   Laufzeit '1004': Anwendungs- oder objektdefinierter Fehler
ausgelöst:
 
'Beispiel 1 - Typumwandlung mit CDate
1:   Dim strDate As String

2:   strDate = "1899-11-03"
3:   Cells(1, 1).Value = CDate(strDate)

'Beispiel 2 - Datumsliteral
4:   Dim varDate As Variant

5:   varDate = #11/3/1899#
6:   Cells(1, 1).Value = varDate

'Beispiel 3 - Variablentyp Date
7:   Dim dtmDate As Date

8:  dtmDate = DateSerial(1899, 11, 3)
9:  Cells(1, 1).Value = dtmDate
 
Es stellt sich hier also die Frage, wie man einer Zelle ein Datum so zuweisen kann, so dass
  - kein Laufzeitfehler auftritt
  - das Datum im entsprechenden Zahlenformat (NumberFormat-Eigenschaft) angezeigt wird
  - das Datum von Excel automatisch als solches erkannt und auch "richtig" interpretiert wird, egal welches Gebietsschema gewählt ist.
Dies lässt sich recht einfach lösen: Nach dem Zuweisen des gewünschten Zahlenformats (NumberFormat-Eigenschaft), übergibt man das Datum als String im Format yyyy-mm-dd (ISO 8601).
 
'Beispiel 1
1:   Dim strDate As String

2:   strDate = "2004-11-03"	  	
3:   Cells(1, 1).NumberFormat = "dd. mmmm yyyy"
4:   Cells(1, 1).Value = strDate

'Beispiel 2
5:   Dim varDate As Variant

6:   varDate = #11/3/2004#
7:   Cells(2, 1).NumberFormat = "dd. mmmm yyyy"
8:   Cells(2, 1).Value = Format$(varDate, "yyyy-mm-dd")

'Beispiel 3
9:   Dim dtmDate As Date

10:  dtmDate = DateSerial(2004, 11, 3)
11:  Cells(3, 1).NumberFormat = "dd. mmmm yyyy"
12:  Cells(3, 1).Value = Format$(dtmDate, "yyyy-mm-dd")
 
Fazit
Weist man einer Tabellenzelle ein gültiges (!) Datum als String im Format yyyy-mm-dd (ISO 8601) zu, wird das Datum (ab 1. Januar 1900) von Excel automatisch als solches erkannt und auch "richtig" interpretiert, unabhängig vom eingestellten Gebietsschema. Das Datum wird im definierten Zahlenformat (NumberFormat-Eigenschaft) dargestellt, und standardmäßig auch rechtsbündig in der Zelle ausgerichtet.
Datumswerte vor dem 1. Januar 1900 werden als Text linksbündig in der Zelle in dem Format angezeigt, das in der Format-Anweisung festgelegt wurde, hier yyyy-mm-dd, das definierte Zahlenformat (NumberFormat-Eigenschaft) hat keinen Einfluss auf die Darstellung.
Diese Art der Übergabe macht generell Sinn, wenn man Daten vor dem 1. Januar 1900 nicht ausschließen kann, so zum Beispiel beim Datenimport aus einer Access-Datenbank per DAO oder ADO oder sonstigen Quellen.
Zellinhalte in Datum umwandeln
Wie aus obiger Tabelle hervorgeht, wurden die Daten "3. November 2004" und "03.11.2004" beim Zuweisen als String (ohne Typumwandlung mit CDate) von Excel nicht automatisch als Datum erkannt (linksbündig in Zelle), obwohl das Gebietsschema Deutsch (Deutschland) gewählt war. Es ist durchaus möglich, Excel nachträglich per VBA "beizubringen", dass es sich um ein Datum handelt. Dies lässt sich auch manuell nachvollziehen, und zwar in dem man die Zelle per Doppelklick oder mit der Taste F2 aktiviert und anschließend die Enter-Taste betätigt.
Mit folgender kleinen Prozedur können die Zellinhalte der selektierten Zellen in ein Datum umgewandelt werden.
Dieses Makro sollte allerdings nur dazu verwendet werden, um zu retten, was noch zu retten ist, da es meines Erachtens wenig Sinn macht, per VBA zuerst das Datum als String im Format "3. November 2004" oder "03.11.2004" in eine Zelle zu schreiben, um dann nachträglich zu versuchen, den Inhalt der Zelle in ein Datum umzuwandeln. Der Versuch könnte auch, je nachdem welches Datumsformat in den Regions- und Sprachoptionen definiert ist, scheitern.
 
Sub ConvertToDate()
   Dim rngCell As Range

   On Error Resume Next
   For Each rngCell In Selection
      If IsDate(rngCell.Value) Then
         rngCell.Value = DateValue(rngCell.Value)
      End If
   Next rngCell
   On Error GoTo 0
End Sub
 
Tipp für die manuelle Datumseingabe in Excel und Access
Geben Sie das Datum über den Ziffernblock im Format yyyy-mm-dd ein, also mit Bindestrich, die Jahreszahl vierstellig und Monat und Tag jeweils zweistellig. Das "beabsichtigte" Datum wird dann immer entsprechend den Einstellungen in den Regions- und Sprachoptionen und/oder im definierten Zell-/Feld-Format angezeigt.
Eingabebeispiel für den 3. November 2004:   2004-11-03
Weitere Links zum Thema
XL: Das 1900-Datumssystem und das 1904-Datumssystem im Vergleich
XL: Date Values Earlier Than 1900 Appear as Text
XL: Days of the week before March 1, 1900 are incorrect in Excel
XL2000: Date Returned in a Macro Is Four Years Too Early
XL2000: 12:00:00 AM Displayed for Dates Earlier Than 1900
XL2000: Copy and Paste of Dates Produces Incorrect Dates

Windows-Version
95
98
ME
NT
2000
XP
Vista
Win 7
Excel-Version
95
97
2000
2002 (XP)
2003
2007
2010


Vorheriger Tipp Zum Seitenanfang Nächster Tipp

Startseite | Projekte | Tutorials | API-Referenz | VB-/VBA-Tipps | Komponenten | Bücherecke | VB/VBA-Forum | VB.Net-Forum | DirectX-Forum | Foren-Archiv | DirectX | VB.Net-Tipps | Chat | Spielplatz | Links | Suchen | Stichwortverzeichnis | Feedback | Impressum

Seite empfehlen Bug-Report
Letzte Aktualisierung: Montag, 16. Mai 2011