Format einer Zelle auslesen und Zeile einfärben

Ein vielbesuchter Beitrag hier auf Excelblog.ch illustriert, wie sich eine ganze Zeile mit bedingten Formatierungen einfärben lässt (siehe Ganze Zeile einfärben (bedingte Formatierung)). Darin wird dargestellt, wie aufgrund einer Zellen-Angabe eine gesamte Zeile farbig hervorgehoben werden kann. Dabei wird meist nach einem bestimmten Text gesucht. In diesem Beitrag stelle ich Dir eine weitere Möglichkeit dar, um auf das Format einer Zelle einzugehen und eine entsprechende Einfärbung zu bewirken.

Für das Einlesen des aktuellen Zellenformats verwenden wir die Formel "ZELLE". In einem anderen Post wurde diese Formel bereits verwendet, um den aktuellen Tabellenblatt-Namen auszulesen (Mittels Formel den Tabellenblatt-Name ausgeben). Gehen wir zuerst auf die Formel im Einzelnen ein, anhand eines Beispiels:

Füge in der Zelle "A1" einen beliebigen Wert ein, zum Beispiel ein Datum wie 17.05.2022. Füge dann die untenstehende Formel in eine beliebige Zelle ein (z.B. "A2"), um das Format der Zelle "A1" auszulesen:

=ZELLE("format";A1)

Falls in "A1" tatsächlich ein Datum steht, erhältst Du "D1" als Formel-Resultat. Ändere nun das Format der Zelle "A1", beispielsweise zu Standard. Beachte: Das Formel-Resultat verändert sich nicht umgehend (eine technische Anmerkung: Obschon unter Formeln die Berechnungsoptionen auf "Automatisch" eingestellt sind). Aktualisiere die "ZELLE"-Formel erneut (gehe dafür in den "Bearbeitungsmodus" und bestätige die Formel mit der Enter-Taste erneut. Nun wird Dir für die "Standard-Formatierung" ein "S" ausgegeben.

Weitere Format-Optionen und Formel-Resultate

Schauen wir uns einige weitere Optionen an, teste gerne auch eigene Fälle und aktualisiere jeweils die "ZELLE"-Formel, um das Resultat zu sehen:

Formel ZELLE format

Beachte ein Muster bei Zahlen oder Prozentwerten und deren Dezimalstellen: Der Zähler erhöht sich jeweils, wobei "P" für Prozent (percentage) steht; "F" repräsentatiert "figure" (Engl.: Zahl).

Anwendung zur Einfärbung einer Zelle oder gesamten Zeile

Mit diesen Vorkenntnissen können wir nun eine bedingte Formatierung bei der Zelle selbst hinterlegen, in unserem Beispiel die Zelle "A1". Gehe dazu via "Start" → "Bedingte Formatierung" auf "Neue Regel...". Wähle dort die unterste Option "Formel zur Ermittlung der zu formatierenden Zelle verwenden" und füge die nachfolgende Formel ein:

=ZELLE("format";A1)="D1"

Wir wissen ja, dass wir im Falle eines Datums (17.05.2022) "D1" als Formel-Resultat zurückerhalten. Wir prüfen demnach, ob die Zelle A1 entsprechend formatiert ist und färben die Zelle daraufhin grün ein:

Formatierungsregel ZELLE Format

Um eine gesamte Zeile einzufärben, basierend auf der Angabe in Spalte "A", müsstest Du die Formel leicht anpassen, mit einem Dollarzeichen vor "A1":

=ZELLE("format";$A1)="D1"

Wir fixieren damit nur die Spalte, nicht die Zeile (da wir wahrscheinlich die bedingte Formatierung auf einen grösseren Bereich anwenden möchten; zum Beispiel von A1 bis F100. Setze dadurch in den bedingten Formatierungen unter "Regeln verwalten" den Bereich "=$A$1:$F$100" bei "Wird angewendet auf":

Formatierungsregel angewendet auf

Du kannst unter "Regel bearbeiten" Deine Formel jederzeit anpassen (z.B. auf "P2" für den gesuchten Prozentwert im Format "85.00%") sowie die Formatierung (Farbe, Schriftart, etc.) setzen.

Beachte: Wir haben oben erwähnt, dass die Formel bei einer Änderung der Formatierung erneut eingegeben werden muss. Das ist bei der bedingten Formatierung nicht der Fall. Solltest Du also einen Wert und dessen Formatierung anpassen (z.B. von einem Datum in einen Prozentwert umwandeln), wird umgehend die (bedingte) Formatierung zur Anwendung kommen.

Cheat-Sheet zu Uhrzeit und Datum (angelehnt an UiPath/RPA)

Mit Datumsangaben sowie Uhrzeiten haben wir immer wieder zu arbeiten (und zu kämpfen). Ich möchte eine umfassende Zusammenstellung aus dem UiPath Forum von Palaniyappan P. mit Euch teilen, die als "Cheat-Sheet" dienen kann. Die grundlegenden Gedanken und Formatierungen können auch bei der klassischen Excel-Zellenformatierung zur Anwendung kommen. Achte darauf auf Deine Spracheinstellungen (z.B. "t" anstelle von "d"). Als einfacher Einstieg kann Dir auch dieser Post hier auf Excelblog helfen: Dynamische Titel (Formel TEXT)

In Palaniyappan's Beitrag findest Du mehr als 20 Lösungen zu häufig gestellten Fragen rund um Uhrzeiten und Datumswerte. Dies ist seine umfassende Zusammenstellung: forum.uipath.com/t/all-about-datetime-uipath/

Vorlage Arbeitszeiterfassung

Im neuen Jahr werden üblicherweise in den Personalabteilungen die Überträge von Gleitzeit und Ferienguthaben getätigt. Als Angestellter ist die Kontrolle über die eigene Gleitzeit und das eigene Ferienguthaben nicht immer einfach und kann schnell unübersichtlich werden. Gerne stellen wir dazu unsere Excel-Arbeitszeiterfassungs-Vorlage zur Verfügung, welche fast alle Berechnungen übernimmt.

Die Arbeitszeit kann in den Tabellenblätter 01 – 12 (Januar – Dezember) erfasst werden. Dafür sind die Spalten «Arbeitsbeginn» / «Arbeitsende» vorgesehen, jeweils für den Morgen und Mittag. Die Spalte «Status» hat keinen Einfluss auf die Berechnungen und dient lediglich der Übersicht. Die Berechnungen findet man im Tabellenblatt «2022» beziehungsweise in der «Arbeitszeiterfassung Jahresübersicht».

Handhabung der Arbeitszeiterfassungsvorlage

  • Die Arbeitszeit muss im jeweiligen Monatstabellenblatt erfasst werden.
  • Bei Krankheits- und Sonderurlaub-Absenzen muss die Soll-Arbeitszeit erfasst werden (z.B. 8 Stunden), damit man die entsprechende Monats-Sollzeit erreicht.
  • Ferientage werden im Tabellenblatt «2022» erfasst unter Ferienbezug.

Generelle Einträge

Diese Einträge müssen vor Verwendung der Arbeitszeiterfassungsvorlage im Tabellenblatt «2022» angepasst werden!

Vor- und Nachname:
Dieser Eintrag wird danach in alle Monats-Tabellenblätter übertragen.

Pensum:
Daraus wird die effektive Sollzeit und Ferienguthaben berechnet.

Standardwerte:
Sollzeit (pro Tag) und Ferienguthaben (in einem 100% Pensum) gemäss Arbeitgeber eintragen. Aus dem bereits eingetragenen Pensum (z.B. 60%) und den Standardwerten wird danach die effektive Sollzeit und das effektive Ferienguthaben berechnet.

Übertrag Gleit- und Ferienzeit 2021:
Gleitzeit und Ferienguthaben vom Vorjahr übertragen.

Anpassen der Feiertage (für die Anzahl Arbeitstage pro Monat):
Mit der rechten Maustaste auf die Tabellenblätter klicken und Einblenden auswählen. Danach kann das Admin-Tabellenblatt eingeblendet werden. Darin sind die Feiertage geregelt und können entsprechend angepasst werden.

Excelblog wünscht einen guten Start ins neue Jahr und viel Spass mit der Arbeitszeiterfassungsvorlage.

Download:

Vorlage Arbeitszeiterfassung