Tutorials

Excel-Tutorial: Zeitbereiche prüfen und Werte zuweisen

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel (oder Google Sheets) mit Uhrzeiten arbeitest, Zeitbereiche prüfst und entsprechende Werte zurückgibst.

(If Time is Within Range | Greater/Less Than | Lookup Time)

In Excel werden Uhrzeiten als Dezimalzahlen zwischen 0 und 1 gespeichert (z. B. ist 12:00 Uhr mittags gleich 0,5). Um Fehler zu vermeiden, ist es wichtig, die richtigen Funktionen zu nutzen, damit Excel erkennt, dass du dich auf eine Uhrzeit beziehst.

1. Die Grundlagen: Uhrzeiten in Formeln verwenden

Bevor wir prüfen, müssen wir sicherstellen, dass Excel die Zeit erkennt. Du hast zwei Möglichkeiten, eine Zeit in einer Formel festzulegen:

  • Die ZEIT-Funktion: ZEIT(Stunde; Minute; Sekunde) – Empfohlen!
  • Der Text-String: -- "14:00" (Das doppelte Minus wandelt Text in eine Zahl um).

2. Wenn Zeit größer oder kleiner als X ist

Szenario: Du möchtest prüfen, ob eine Arbeitszeit nach 18:00 Uhr liegt, um einen Abendzuschlag zu berechnen.

Formel:

=WENN(A2 > ZEIT(18;0;0); "Zuschlag"; "Normaltarif")
  • A2: Die Zelle mit der Uhrzeit.
  • ZEIT(18;0;0): Entspricht 18:00 Uhr.

3. Wenn Zeit innerhalb eines Bereichs liegt (WENN + UND)

Szenario: Du möchtest prüfen, ob eine Uhrzeit in die Kernarbeitszeit zwischen 09:00 und 15:00 Uhr fällt.

Hierfür nutzt du die UND-Funktion innerhalb der WENN-Funktion.

Formel:

=WENN(UND(A2 >= ZEIT(9;0;0); A2 <= ZEIT(15;0;0)); "Kernzeit"; "Außerhalb")
  • Logik: Ist die Zeit (A2) größer/gleich 09:00 UND kleiner/gleich 15:00?
  • Ergebnis: Nur wenn beide Bedingungen wahr sind, wird "Kernzeit" ausgegeben.

4. Zeit in einer Tabelle nachschlagen (Lookup Time in Range)

Szenario: Du hast verschiedene Schichten und möchtest anhand der Uhrzeit automatisch den Namen der Schicht zuweisen.

Erstelle zuerst eine kleine Referenztabelle (z.B. in den Zellen D2:E5): | Startzeit (Spalte D) | Schichtname (Spalte E) | | :--- | :--- | | 00:00 | Nachtschicht | | 06:00 | Frühschicht | | 14:00 | Spätschicht | | 22:00 | Nachtschicht |

Lösung mit SVERWEIS (VLOOKUP):

Verwende den SVERWEIS mit dem Argument WAHR für eine ungefähre Übereinstimmung.

Formel:

=SVERWEIS(A2; $D$2:$E$5; 2; WAHR)
  • A2: Die gesuchte Uhrzeit.
  • $D$2:$E$5: Die Referenztabelle (mit $ fixiert).
  • 2: Die zweite Spalte (Schichtname) soll zurückgegeben werden.
  • WAHR: Excel sucht den nächstkleineren Wert, falls die exakte Uhrzeit nicht gefunden wird (ideal für Zeitbereiche).

Lösung mit XVERWEIS (XLOOKUP - für moderne Excel-Versionen):

Der XVERWEIS ist flexibler.

Formel:

=XVERWEIS(A2; $D$2:$D$5; $E$2:$E$5; ; -1)
  • -1: Suchmodus für "nächstkleineres Element". Das bedeutet, alles von 06:00 bis 13:59 wird als "Frühschicht" erkannt.

5. Sonderfall: Zeitbereiche über Mitternacht hinaus

Zeitbereiche, die über Mitternacht gehen (z.B. 22:00 bis 06:00 Uhr), sind in Excel tückisch, da 02:00 Uhr morgens mathematisch kleiner ist als 22:00 Uhr.

Die Lösung: Nutze die ODER-Logik.

Szenario: Nachtzuschlag von 22:00 bis 06:00 Uhr. Formel:

=WENN(ODER(A2 >= ZEIT(22;0;0); A2 <= ZEIT(6;0;0)); "Nachtzuschlag"; "Normal")
  • Hier wird geprüft: Ist es später als 22:00 Uhr ODER früher als 06:00 Uhr?

Zusammenfassung der Tipps:

  1. Formatierung: Stelle sicher, dass die Zellen, in denen die Uhrzeit steht, auch als "Uhrzeit" formatiert sind.
  2. ZEIT-Funktion: Nutze immer ZEIT(h; m; s), um Fehler durch falsche Dezimaltrenner zu vermeiden.
  3. Ungefähre Suche: Bei Zeitbereichen in Tabellen ist der SVERWEIS mit WAHR (oder 1) dein bester Freund, da du nicht jede einzelne Minute in die Tabelle schreiben musst.
0