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")
|
|
|
|
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
|
|
|
|
Windows-Version |
95 |
|
|
98 |
|
|
ME |
|
|
NT |
|
|
2000 |
|
|
XP |
|
|
Vista |
|
|
Win
7 |
|
|
|
Excel-Version |
95 |
|
|
97 |
|
|
2000 |
|
|
2002
(XP) |
|
|
2003 |
|
|
2007 |
|
|
2010 |
|
|
|
|