Bereken werkuren in Excel: nachtdiensten, pauzes en AI
Zet 3.000 aanwezigheidsregistraties om in een Excel-urenrapport: voer nachtdiensten af, signaleer ontbrekende klappen en stel personeelstotalen op die opnieuw worden berekend.
Echte screenshots van een lokale ontwikkelaarsdemo met gesimuleerde gegevens, geen klantgegevens. De oorspronkelijke interface is behouden. Korte video’s bevatten versnelde verwerking.
Het aftrekken van het inklokken en het uitklokken lijkt eenvoudig totdat een dienst middernacht overschrijdt, een pauze in minuten wordt geregistreerd of een ontbrekende slag in een misleidend getal verandert.
Bereken bij diensten korter dan 24 uur met alleen kloktijden de duur ook over middernacht en trek de pauze af. Markeer ontbrekende in- of uitkloktijden apart. Tel vervolgens op per werknemers-ID. Deze GetSheetAI-demonstratie gebruikt 3.000 gesimuleerde records van 100 werknemers.
Wat de voltooide werkmap bevat
De oorspronkelijke aanwezigheidsvelden blijven behouden, met berekende uren en een statuskolom toegevoegd. Een afzonderlijk medewerkersoverzicht toont het totaal aantal uren, geldige aanwezigheidsgegevens en onvolledige klokregistraties. Het voorbeeld bevat 80 onvolledige klokregistraties, die zichtbaar blijven voor correctie in plaats van stil te worden gevuld.
Bij de controle veranderde een dienst van 7 naar 8 uur. Het urentotaal van de betreffende medewerker veranderde van 210 naar 211 uur. Daarna zijn de oorspronkelijke waarden hersteld.
Bereid de aanwezigheidsgegevens voor
Gebruik werknemer-ID, naam, afdeling, werkdatum, inkloktijd, uitkloktijd en pauzeminuten. Identificeer werknemers op basis van ID in plaats van op naam. Sla kloktijden op als Excel-tijdwaarden en onderscheid een lege cel van middernacht: middernacht is een legitieme tijdwaarde van nul.
Bepaal of één rij één werkdag of één aanwezigheidsinterval betekent. Als een medewerker meerdere rijen op één datum heeft, telt het tellen van rijen geen afzonderlijke werkdagen. De dagelijkse gegevens van het voorbeeld ondersteunen de samenvatting; andere lay-outs hebben een andere telregel nodig.
Geef de plug-in een gewoon verzoek
Open het blad in Excel, open GetSheetAI en start een nieuw gesprek. U kunt dit verzoek aanpassen:
Bereken de werkuren voor elke aanwezigheidsregistratie, inclusief nachtdiensten en aftrek van pauzeminuten. Markeer ontbrekende in- of uitklokwaarden in plaats van te raden. Behoud de originele velden. Maak een werknemersoverzicht met het totaal aantal uren, geldige records en ontbrekende records, met behulp van formules die worden bijgewerkt wanneer ik een tijd corrigeer.
U hoeft IF, MOD of SUMIFS niet in het verzoek te vermelden. Deze functies zijn handig bij het inspecteren hoe de gegenereerde werkmap de resultaten berekent.
Begrijp de urenberekening
Stel dat C2 is ingeklokt, D2 is uitgeklokt en E2 is pauzeminuten. Een illustratieve Engelse Excel-formule is:
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)
De controle op ontbrekende waarden wordt eerst uitgevoerd. MOD verwerkt een tijd die middernacht overschrijdt, vermenigvuldigt met 24 en zet dagen om in uren, en het delen van pauzeminuten door 60 geeft dezelfde eenheid. Voor 22.00 uur tot 06.00 uur met een pauze van 60 minuten is het resultaat 7 uur. Middernacht mag niet worden aangezien voor een ontbrekende klokregistratie.
Gebruik dit patroon alleen voor bekende diensten korter dan 24 uur. Identieke kloktijden kunnen nul uur of een volledige dag betekenen; de formule kan dat niet voor u beslissen. Gebruik voor meerdaagse diensten de volledige begin- en einddatum-tijden. Negatieve resultaten, pauzes die langer zijn dan een dienst en onwaarschijnlijk lange intervallen moeten ter beoordeling worden gemeld.
Samenvatting per medewerker zonder uitzonderingen te verbergen
Voeg geldige uren samen op werknemers-ID, met behulp van een aparte statuskolom om geldige en ontbrekende records te onderscheiden. SUMIFS kan uren optellen met zowel werknemers- als statuscriteria; COUNTIFS kan records tellen die aan dezelfde voorwaarden voldoen.
De geregistreerde verificatie omvatte 3.000 bronrijen, 100 werknemerssamenvattingsrijen, zowel nachtelijke als middernachtgevallen, alle bronwaarden en het filter voor onvolledige klokregistraties van 80 rijen. Correcties moeten de details en het urentotaal per werknemer samen bijwerken.
Controleer voordat u uw eigen rapport opnieuw gebruikt één dagploeg, één nachtploeg, één geldige middernachttijd en één onvolledig paar. Corrigeer één keer en bevestig dat precies het beoogde medewerkerstotaal verandert.
Veelgestelde vragen
Berekent dit de loonsom of de wettelijke overuren?
In deze case worden aanwezigheidsuren berekend volgens de aangegeven regels. Voor overwerkdrempels, betaalde pauzes, vakantiepremies, afrondingen en zomertijdaanpassingen zijn aparte regels nodig. Het geregistreerde voorbeeld verifieert niet dat de loonadministratie wordt nageleefd.
Kan ik de aanwezigheid van volgende maand toevoegen?
Controleer of de formules en samenvattingsbereiken de nieuwe records bevatten. Een Excel-tabel kan helpen bij het uitbreiden van brongegevens, maar verifieert de daadwerkelijke referenties in plaats van aan te nemen dat elke nieuwe rij is opgenomen.
Wat als tijden als tekst worden opgeslagen?
Normaliseer eerst de bron. De gids voor het opschonen van gegevens legt de voorbereiding uit; de AI-formulegids laat zien hoe u een berekening beschrijft en verifieert.
