+ TIPP: Zeitzonen abfragen mit der Funktion ZEIT()

26.07.2010 | Tipps & Tricks

Um sich einen kleinen Zeitzonenrechner zu "basteln", müssen Sie die Funktionen ZEIT geschickt mit der Funktion SVERWEIS kombinieren. Im Einzelnen gehen Sie wie folgt vor:

Step by Step

1.  Legen Sie zunächst in einer neuen Tabelle den Datenbereich mit den verschiedenen Zeitzonen an.

2.  Hinterlegen Sie in Spalte E über die Funktion ABS() den absoluten Wert der Zeitabweichung, d. h. den Wert ohne Berücksichtigung des Vorzeichens, wie nachfolgend in Abbildung 1 dargestellt.

Abb. 1: So werden die diversen Zeitzonen hinterlegt

3. Erfassen Sie nun in Zelle B4 eine Gültigkeitsliste, welche die Zeitzonen-Kürzel per Dropdown auswählbar macht.

4. Hinterlegen Sie anschließend in C2 die Formel =JETZT(). Über diese Funktion wird die aktuelle Systemzeit Ihres Rechners (in Deutschland UTC korrigiert um die Sommerzeit) in die Excel-Zelle geschrieben.

5. Nun müssen Sie in der Zelle C4 die Formel =WENN(SVERWEIS(B4;B10:D62;3;FALSCH)<0;C2-ZEIT(SVERWEIS(B4;B10:E62;4;FALSCH);0;0);C2+ZEIT(SVERWEIS(B4;B10:D62;3;FALSCH);0;0)) erfassen.

6. Wenn Sie jetzt per Dropdown in Zelle B4 die Auswahl ändern, dann wird Ihnen in Zelle C4 immer die jeweils aktuelle Zeit der jeweiligen Zeitzone angezeigt (s. Abb. 2).

Abb. 2: So wird die aktuelle Zeit der jeweiligen Zeitzone angezeigt.

Das Ganze funktioniert so:

Über die WENN-Bedingung wird geprüft, ob die Zeitdifferenz positiv oder negativ ist und anschließend - je nachdem, ob die Bedingung erfüllt ist oder nicht - mit unterschiedlichen Formeln gearbeitet. Die Prüfung erfolgt über SVERWEIS(B4;B10:D62;3;FALSCH)<0. Es wird also geprüft, ob der Wert in der jeweiligen Zeile der gewählten Zeitzone in Spalte D positiv oder negativ ist.

Falls die Zeitdifferenz negativ sein sollte, also vor der aktuellen Systemzeit liegt, so wird mit der Formel C2-ZEIT(SVERWEIS(B4;B10:E62;4;FALSCH);0;0) gerechnet. D. h. es wird von der aktuellen Systemzeit in Zelle C2 der in Spalte E errechnete absolute Wert der jeweiligen Zeitzone abgezogen.

Falls die Zeitdifferenz positiv sein sollte, also vor der aktuellen Systemzeit liegt, so wird mit der Formel C2+ZEIT(SVERWEIS(B4;B10:D62;3;FALSCH);0;0) gerechnet. Das heißt, es wird zu der aktuellen Systemzeit in Zelle C2 der in Spalte E errechnete absolute Wert der jeweiligen Zeitzone hinzugerechnet.

 

Demo-Datei:

Zeitumrechnung.xls