Calcola le ore di lavoro in Excel: turni notturni, pause e intelligenza artificiale
Trasforma 3.000 registrazioni di presenze in un rapporto sulle ore Excel: gestisci i turni notturni, segnala le timbrature mancanti e crea i totali dei dipendenti da ricalcolare.
Schermate reali di una demo locale dello sviluppatore con dati simulati, non di clienti. L’interfaccia originale è conservata. I video brevi includono elaborazioni accelerate.
Sottrarre la timbratura in entrata dalla timbratura in uscita sembra facile fino a quando un turno non supera la mezzanotte, una pausa viene registrata in minuti o una timbratura mancante si trasforma in un numero fuorviante.
Per turni inferiori a 24 ore registrati solo con gli orari, calcola la durata anche oltre la mezzanotte e sottrai la pausa. Segnala separatamente le timbrature incomplete. Poi somma per ID dipendente. Questa dimostrazione GetSheetAI usa 3.000 registrazioni simulate per 100 dipendenti.
Cosa contiene la cartella di lavoro finita
Rimangono i campi di partecipazione originali, con l'aggiunta delle ore calcolate e di una colonna di stato. Un riepilogo separato dei dipendenti mostra le ore totali, i record di presenza validi e i record di timbrature mancanti. L’esempio contiene 80 registrazioni con timbrature incomplete, che rimangono visibili per la correzione anziché essere riempite silenziosamente.
Nella verifica, un turno è passato da 7 a 8 ore. Il totale delle ore del dipendente interessato è passato da 210 a 211. I valori originali sono stati poi ripristinati.
Preparare i dati delle presenze
Utilizza ID dipendente, nome, reparto, data di lavoro, ora di entrata, ora di uscita e minuti di pausa. Identificare i dipendenti tramite ID anziché nome. Memorizza gli orari dell'orologio come valori temporali Excel e distingui una cella vuota dalla mezzanotte: mezzanotte è un valore temporale legittimo pari a zero.
Decidere se una riga indica un giorno lavorativo o un intervallo di presenza. Se un dipendente ha più righe in una data, il conteggio delle righe non conta i giorni lavorativi distinti. I record giornalieri dell'esempio ne supportano il riepilogo; altri layout necessitano di una regola di conteggio diversa.
Fornisci al plugin una richiesta ordinaria
Apri il foglio in Excel, apri GetSheetAI e inizia una nuova conversazione. Puoi adattare questa richiesta:
Calcola l'orario di lavoro per ogni record di presenza, compresi i turni notturni e la sottrazione dei minuti di pausa. Contrassegna i valori di entrata o uscita mancanti invece di indovinare. Mantieni i campi originali. Crea un riepilogo dei dipendenti con ore totali, record validi e record mancanti, utilizzando formule che si aggiornano quando correggo un orario.
Non è necessario nominare IF, MOD o SUMIFS nella richiesta. Queste funzioni sono utili quando si controlla il modo in cui la cartella di lavoro generata calcola i suoi risultati.
Comprendere il calcolo delle ore
Supponiamo che C2 sia la timbratura d'ingresso, D2 sia la timbratura di uscita ed E2 siano i minuti di pausa. Una formula inglese illustrativa Excel è:
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)
Il controllo dei valori mancanti viene eseguito per primo. MOD gestisce un tempo che attraversa la mezzanotte, moltiplicando per 24 converte i giorni in ore e dividendo i minuti di pausa per 60 si ottiene la stessa unità. Dalle 22:00 alle 06:00 con una pausa di 60 minuti, il risultato è di 7 ore. La mezzanotte non deve essere confusa con un timbratura mancante.
Utilizza questo schema solo per turni noti di durata inferiore a 24 ore. Tempi di orologio identici potrebbero significare zero ore o un giorno intero; la formula non può deciderlo per te. Per turni di più giorni, utilizzare le date e gli orari di inizio e fine completi. Risultati negativi, pause più lunghe di un turno e intervalli non plausibili dovrebbero essere contrassegnati per la revisione.
Riepiloga per dipendente senza nascondere le eccezioni
Aggrega le ore valide per ID dipendente, utilizzando una colonna di stato separata per distinguere i record validi da quelli con timbrature mancanti. SUMIFS può totalizzare le ore con criteri sia di dipendente che di stato; CONTA.PIÙ.SE può contare i record che soddisfano le stesse condizioni.
La verifica registrata ha riguardato 3.000 righe di origine, 100 righe di riepilogo dei dipendenti, casi sia notturni che di mezzanotte, tutti i valori di origine e il filtro dei timbrature mancanti di 80 righe. Le correzioni dovrebbero aggiornare insieme il dettaglio e il totale del dipendente.
Prima di riutilizzare il proprio report, verificare un turno diurno, un turno notturno, un'ora di mezzanotte valida e una coppia incompleta. Correggere una volta e confermare che cambi esattamente il totale del dipendente previsto.
Domande comuni
Vengono calcolati i salari o gli straordinari previsti dalla legge?
In questo caso vengono calcolate le ore di presenza secondo le regole indicate. Le soglie per gli straordinari, le pause retribuite, i premi per le ferie, gli arrotondamenti e gli adeguamenti all'ora legale richiedono regole separate. L'esempio registrato non verifica la conformità delle buste paga.
Posso aggiungere le presenze del mese prossimo?
Verifica che le formule e gli intervalli di riepilogo includano i nuovi record. Una tabella Excel può aiutare ad espandere i dati di origine, ma verifica i riferimenti effettivi anziché dare per scontato che ogni nuova riga sia inclusa.
Cosa succede se gli orari vengono memorizzati come testo?
Normalizzare prima la sorgente. Lo guida alla pulizia dei dati spiega la preparazione; lo Guida alle formule di intelligenza artificiale mostra come descrivere un calcolo e verificarlo.
