Getagged: Stundenzettel

Excel – Stundenzettel – Gesamtstunden und Entgelt

Teil 5…
Die bislang aufgelisteten Stunden sollen auch addiert werden. Hierbei kann aber eine Stundenzahl größer 24 Stunden auftreten. Zudem muss das Entgelt ermittelt werden, wobei Sonntage mit plus 50% bewertet werden.

Hier wird SVERWEIS, SUMMEWENN, SUMME, Plus und Multiplikation verwendet.
Es folgt später noch Text… aber zunächst gibt es die Übungsdateien zum Nacharbeiten der Übung.

Und hier gibt es die Ausgangsdatei:
Excel608_Ergebnisdatei_Start

Und hier die resultierende Datei:
Excel608_Ergebnisdatei_Ende

Hier geht es zum Video:
Videolink: http://youtu.be/uL69YgFgD10

Excel – Stundenzettel – Berechnung der Arbeitszeiten

Teil 4…
Nun sollen die täglichen Arbeitszeiten abzüglich der geregelten Pausen errechnet werden. Zudem gibt es hier noch eine maximale tägliche Arbeitszeit von 10 Stunden.

607a

Die Dauer wird nur errechnet, wenn ein Start- und Enddatum vorhanden sind:

607f1

Dann wird geprüft, ob eine Dauer vorhanden ist, falls ja, wird diese über die Funktion VERWEIS in der kleinen Pausenmatrix geprüft. Kann der Wert dort einsortiert werden, wird die Pausenzeit links daneben geliefert.

607f2

Zum Schluss wird geprüft, ob das Nachbarfeld belegt ist. Falls ja, wird von der Dauer die Pausenzeit abgezogen. Falls diese aber insgesamt dann 10 Stunden überschreitet, wird über die Funktion MIN der Wert 10 Stunden genommen.

607f3

Doch was macht man bei einem Wechsel über Mitternacht – hier drei Ansätze:

a) Prüfen, ob der Anfangszeitwert größer als der Endwert ist, somit ist man also über Mitternacht geblieben und sieht ab =1-D5+E5 Das macht nur Sinn bei weniger 24 Stunden-Schichten.

b) bei längeren Abwesenheitszeiten kann zusätzlich zur Uhrzeit noch das Datum mit eingegeben werden. Also Start 28.10.2014 22:00 und Ende 29.10.2014 07:00 Uhr. Dann Ende minus Start – wie oben. Hier ist die Dauer beliebig.

c) Nur mit der Startzeit und der Dauer rechnen. Eingeben, dass man um 22:00 Uhr gekommen und 9 Stunden geblieben ist. Dann wird die Endzeit errechnet.

Hier ist die Übungsdatei:
Excel607_Ergebnisdatei

Hier geht es zum Video:
Videolink: http://youtu.be/Dbo0d56bllE

Excel – Stundenzettel – Datum farbig hervorheben – Bedingte Formatierung

Teil 3…
Der Stundenzettel soll mit der Hilfe von bedingter Formatierung übersichtlicher gestaltet werden.
Zum einen sollen generell die Tage von Wochenenden grau hinterlegt sein – in der ganzen Zeile.
Dann soll auch eine graue Linie bei einem Wochenwechsel erschein.
Zudem sollen begonnene Einträge bei Leistung so lange orange hinterlegt sein, wie in beiden Nachbarzellen noch keine Uhrzeiten angelegt worden sind.

606a

Zunächst markiere ich den Datenbereich von Zelle A5:H35, da hier später die bedingte Formatierung für die grauen Wochenendzeilen gelten soll. Dann klicke ich auf START und anschließend auf die Befehlsschaltfläche Bedingte Formatierung.

Hier sieht man schon die Bereiche für die fertigen Regeln.

606b

Für eine neue Regel klicke ich auf Neue Regel und wähle den unteren Eintrag aus, dass ich eine Regel auf Basis des Ergebnisses einer Formel erstellen möchte. Wenn das Formelergebnis den Wert WAHR ergibt, gilt die Formel. Es soll hier einfach nur geprüft werden, ob vorne in Spalte A der gleichen Zeile der Wert Sa oder So steht.

606e

Nach Eingabe der Regel nicht vergessen, dann auch noch das Format über die Schaltfläche Formatieren anzupassen. Hier wähle ich z.B. den grauen Hintergrund.

Anschließend markiere ich den Bereich A6:H35 – also ohne die obere Datumszeile.
Hier wird geprüft, ob der Wochentagswert in Spalte B größer ist, als der Tag der eigenen Woche. Falls ja, fand ein Wochenwechsel statt und die Linie kann gesetzt werden.

606d

Hier wähle ich das Format, dass bei Geltung der Regel der Rahmenlinienstrich oben in grau gesetzt wird.

Zuletzt markiere ich den Datenbereich von Zelle c5:c35, da hier später die bedingte Formatierung für die Prüfung liegen soll, ob die Nachbarzellen belegt sind und in der eigenen Zelle ein Wert ausgewählt wurde.

606c

Hier wähle ich z.B. Orange.

Hier ist die Ergebnisdatei aus Video Excel # 606:
Excel606_Ergebnisdatei

Hier geht es zum dritten Video:
Videolink: http://youtu.be/bFEmzlZ1J60

Excel – Stundenzettel – Wochentage per Kontrollkästchen auswählen

Der Stundenzettel aus dem vorherigen Beitrag wird noch um die Bedienung per Kontrollkästchen erweitert. Die Kontrollkäschen sind mit Zielzellen verbunden und können dort bei aktiviertem Kästchen den Wert WAHR und bei deaktivierten Kästchen den Wert FALSCH eintragen.

Damit die Verkettung der WAHR- und FALSCH-Werte noch funktioniert, müssen die Wahrwerte mit *1 oder +0 oder voran gestelltem Doppelminus — in 0 und 1 verwandelt werden.

Zusätzlich setze ich den Blattschutz. Beim Blattschutz ist aber darauf zu achten, dass die verknüpften Zielzellen nicht gesperrt werden dürfen, da der Schalter dort neue Werte hinein schreibt.

Die Formularsteuerelemente befinden sich im Register ENTWICKLERTOOLS. Mehr Informationen gibt es natürlich im Video…

Hier ist der Ergebnisdatei aus Excel-Video # 605 (Rechtsklick und Speichern unter):
Excel605_Ergebnisdatei

Hier geht es zum zweiten Video:
Videolink: http://youtu.be/Cud03UrT5_E

Excel – Stundenzettel – Arbeitstage eines Monats anzeigen

Ein Stundenzettel soll für jeweils einen Monat erstellt werden. Dabei gilt es, lediglich die potentiellen Arbeitstage aufzulisten.

Wird an Montagen und Sonntagen nicht gearbeitet, sollen diese Tage deaktiviert werden können. Zudem gibt es eine Liste mit Ausnahmen für generelle Feiertage.

601a

Hier bietet sich die Funktion ARBEITSTAG.INTL an. Diese Funktion ermittelt ausgehend von einem Startdatum ein Zieldatum. Dieses Zieldatum wird aber in reinen Arbeitstagen Abstand berechnet und nicht in Kalendertagen. INTL am Funktionsnamen sagt zudem an, dass die freien Wochentage selbst definiert werden können.

Hier ein Bild der Ergebnisdatei aus Video Excel # 604:

604b

Die folgenden Funktionen und Eingaben wurden hier getätigt:

Zelle B1 – beinhaltet das Datum des anzuzeigenden Monats. Hier kann z.B. jeweils der Monatserste angegeben werden.

Bereich G2:G8 – hier stehen 0-Werte für Arbeitstage und 1-Werte für Nicht-Arbeitstage.

Zelle G9 – hier werden die Zahlen aus G2:G8 verkettet. Das kaufmännische Und-Zeichen fügt die Werte zu einem Textstring zusammen. Formel =G2&G3&G4&G5&G6&G7&G8
Hier hätte man natürlich auch die Funktion =VERKETTEN(G2;G3;G4;G5;G6;G7;G8) nehmen können.

Bereich H2:H9 – hier werden die Feiertage eingegeben. Die Liste müsste eigentlich noch viel länger werden. Tage wie Allerheiligen, Ostern und Weihnachten werden zusätzlich zu Nicht-Arbeitstagen nicht berücksichtigt.

Die folgende Formel wurde in B5 verfasst und bis B35 nach unten kopiert:
=WENN(MONAT(ARBEITSTAG.INTL($B$1-1;ZEILEN($B$5:B5);$G$9;$H$2:$H$9))=MONAT($B$1);ARBEITSTAG.INTL($B$1-1;ZEILEN($B$5:B5);$G$9;$H$2:$H$9);"")
Diese Funktion wird nach unten kopiert. Dabei vergrößert sich der Bereich $B$5:B5 in der Folgezeile zu $B$5:B6 bis schließlich zu $B$5:B35.

Zusammen mit der Funktionen ZEILEN wird z.B. mit =ZEILEN($B$5:B8) ermittelt, wie viele Zeilen der Bereich von Zeile 5 bis Zeile 8 umfasst. Hier also 4 Zeilen. Wir haben somit einen Zähler, der mit jeder Zeile automatisch um den Wert 1 größer wird.

Somit wird ausgehend vom Startdatum in Zelle B1 zunächst ein Zähler abgezogen, um im selben Moment in Zelle B5 sofort wieder einen Zähler darauf zu zählen. Drei Zeilen tiefer werden aber schon 4 Werte dazu gezählt und so weiter.

Die Funktion =ARBEITSTAG.INTL(Ausgangsdatum;Tage;Wochenende;freie_Tage) zählt von einem Startdatum eine gewisse Anzahl von Tagen drauf. Dabei werden aber die unter Wochenende aufgeführten Muster ausgelassen und einfach weiter gezählt. Zudem werden auch die freien Tage einfach ausgelassen, so dass man beim Weiterzählen der Tage nur Arbeitstage und deren Datumswerte bekommt.

Damit nicht über den Monat hinaus gezählt wird, wird mit =Monat(B1) die Monatszahl des Zielmonats ermittelt und mit den Ergebnissen von ARBEITSTAG.INTL verglichen. Sind beide identisch, wird der ARBEITSTAG tatsächlich eingetragen. Ansonsten kommt der WERT “”, somit erfolgt keine Eingabe eines Datums.

Dieses Thema wird in weiteren Videos und Blog-Einträgen fortgesetzt. Jetzt gibt es zumindest schon einmal eine Liste der relevanten Datumswerte.

Hier ist die Datei mit dem finalen Stand zum Abschluss des Videos (Rechtsklick und Ziel speichern unter):
Excel604_Ergebnisdatei

Hier geht es zum 1. Teil der Videoreihe
Videolink: http://youtu.be/uFIXx8RN4qA