Berechnen Sie die Arbeitsstunden in Excel: Nachtschichten, Pausen und KI
Verwandeln Sie 3.000 Anwesenheitsdatensätze in einen Excel-Stundenbericht: Verwalten Sie Nachtschichten, kennzeichnen Sie fehlende Stempel und erstellen Sie Stundensummen der Mitarbeiter, die neu berechnet werden.
Echte Screenshots einer lokalen Entwicklerdemo mit simulierten Daten, nicht mit Kundendaten. Die ursprüngliche Oberfläche bleibt erhalten. Die Kurzvideos enthalten beschleunigte Verarbeitung.
Das Subtrahieren des Einstempelns vom Ausstempeln scheint einfach zu sein, bis eine Schicht Mitternacht überschreitet, eine Pause in Minuten aufgezeichnet wird oder ein fehlender Stempel zu einer irreführenden Zahl wird.
Bei Schichten unter 24 Stunden, die nur mit Uhrzeiten erfasst sind, berechnen Sie die Dauer auch über Mitternacht und ziehen die Pause ab. Fehlende Zeitbuchungen bleiben separat markiert. Anschließend summieren Sie nach Mitarbeiter-ID. Diese GetSheetAI-Demonstration nutzt 3.000 simulierte Datensätze für 100 Mitarbeiter.
Was die fertige Arbeitsmappe enthält
Die ursprünglichen Anwesenheitsfelder bleiben erhalten, mit berechneten Stunden und einer hinzugefügten Statusspalte. In einer separaten Mitarbeiterübersicht werden die Gesamtstundenzahl, die gültigen Anwesenheitslisten und die fehlenden Stempelaufzeichnungen angezeigt. Der Fall enthält 80 Zeilen mit fehlenden Stempeln, die zur Korrektur sichtbar bleiben und nicht stillschweigend gefüllt werden.
Im Änderungstest stieg eine Schicht von 7 auf 8 Stunden. Die Stundensumme des betreffenden Mitarbeiters stieg von 210 auf 211 Stunden. Anschließend wurden die Ausgangswerte wiederhergestellt.
Bereiten Sie die Anwesenheitsdaten vor
Verwenden Sie die Mitarbeiter-ID, den Namen, die Abteilung, das Arbeitsdatum, die Einstempelzeit, die Ausstempelzeit und die Pausenminuten. Identifizieren Sie Mitarbeiter anhand ihrer ID und nicht anhand ihres Namens. Speichern Sie Uhrzeiten als Excel-Zeitwerte und unterscheiden Sie eine leere Zelle von Mitternacht: Mitternacht ist ein legitimer Zeitwert von Null.
Entscheiden Sie, ob eine Zeile einen Arbeitstag oder ein Anwesenheitsintervall bedeutet. Wenn ein Mitarbeiter mehrere Zeilen an einem Datum hat, werden beim Zählen der Zeilen keine unterschiedlichen Arbeitstage gezählt. Die täglichen Aufzeichnungen des Beispiels stützen seine Zusammenfassung; andere Layouts benötigen eine andere Zählregel.
Geben Sie dem Plugin eine normale Anfrage
Öffnen Sie das Blatt in Excel, öffnen Sie GetSheetAI und starten Sie eine neue Konversation. Sie können diese Anfrage anpassen:
Berechnen Sie die Arbeitszeiten für jeden Anwesenheitsdatensatz, einschließlich Nachtschichten und abzüglich der Pausenminuten. Markieren Sie fehlende Ein- oder Ausstempelwerte, anstatt zu raten. Behalten Sie die ursprünglichen Felder bei. Erstellen Sie eine Mitarbeiterübersicht mit Gesamtstunden, gültigen Datensätzen und Datensätzen mit fehlenden Stempeln und verwenden Sie dabei Formeln, die aktualisiert werden, wenn ich eine Zeit korrigiere.
Sie müssen in der Anfrage weder IF, MOD noch SUMIFS angeben. Diese Funktionen sind hilfreich, wenn Sie überprüfen möchten, wie die generierte Arbeitsmappe ihre Ergebnisse berechnet.
Verstehen Sie die Stundenberechnung
Angenommen, C2 ist das Einstempeln, D2 das Ausstempeln und E2 die Pausenminuten. Eine veranschaulichende englische Excel-Formel lautet:
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)
Zuerst wird die Prüfung auf fehlende Werte ausgeführt. MOD verarbeitet eine Zeit, die Mitternacht überschreitet, indem es mit 24 multipliziert, um Tage in Stunden umzuwandeln, und wenn man Pausenminuten durch 60 dividiert, erhält man dieselbe Einheit. Für 22:00 bis 06:00 Uhr mit einer 60-minütigen Pause ergibt sich ein Ergebnis von 7 Stunden. Mitternacht darf nicht mit einem fehlenden Zeiteintrag verwechselt werden.
Verwenden Sie dieses Muster nur für bekannte Schichten, die kürzer als 24 Stunden sind. Identische Uhrzeiten könnten null Stunden oder einen ganzen Tag bedeuten; Die Formel kann das nicht für dich entscheiden. Verwenden Sie für mehrtägige Schichten vollständige Start- und Enddatums-/Zeitangaben. Negative Ergebnisse, Pausen, die länger als eine Schicht sind, und unplausibel lange Intervalle sollten zur Überprüfung gemeldet werden.
Nach Mitarbeiter zusammenfassen, ohne Ausnahmen auszublenden
Aggregieren Sie gültige Stunden nach Mitarbeiter-ID und verwenden Sie eine separate Statusspalte, um gültige Datensätze und fehlende Datensätze zu unterscheiden. SUMIFS kann Stunden sowohl nach Mitarbeiter- als auch nach Statuskriterien summieren; COUNTIFS kann Datensätze zählen, die dieselben Bedingungen erfüllen.
Die aufgezeichnete Überprüfung umfasste 3.000 Quellzeilen, 100 Mitarbeiterzusammenfassungszeilen, sowohl Nacht- als auch Mitternachtsfälle, alle Quellwerte und den 80-Zeilen-Missing-Punch-Filter. Bei Korrekturen sollten die Details und die Stundensummen der Mitarbeiter aktualisiert werden.
Bevor Sie Ihren eigenen Bericht wiederverwenden, überprüfen Sie eine Tagesschicht, eine Nachtschicht, eine gültige Mitternachtszeit und ein unvollständiges Paar. Korrigieren Sie einmal und bestätigen Sie, dass sich genau die beabsichtigte Mitarbeitersumme ändert.
Häufige Fragen
Berechnet dies die Gehaltsabrechnung oder gesetzliche Überstunden?
In diesem Fall werden die Anwesenheitsstunden nach den angegebenen Regeln berechnet. Überstundengrenzen, bezahlte Pausen, Urlaubszuschläge, Rundungen und Sommerzeitanpassungen erfordern gesonderte Regelungen. Das aufgezeichnete Beispiel überprüft nicht die Einhaltung der Lohn- und Gehaltsabrechnung.
Kann ich die Anwesenheit des nächsten Monats anhängen?
Überprüfen Sie, ob die Formeln und Zusammenfassungsbereiche die neuen Datensätze enthalten. Eine Excel-Tabelle kann bei der Erweiterung der Quelldaten hilfreich sein. Überprüfen Sie jedoch die tatsächlichen Referenzen, anstatt davon auszugehen, dass jede neue Zeile enthalten ist.
Was passiert, wenn Zeiten als Text gespeichert werden?
Normalisieren Sie zuerst die Quelle. Der Anleitung zur Datenbereinigung erklärt die Vorbereitung; Leitfaden zur KI-Formel zeigt, wie eine Berechnung beschrieben und überprüft wird.
