Calculer les heures de travail dans Excel : équipes de nuit, pauses et IA
Transformez 3 000 enregistrements de présence en un rapport d'heures Excel : gérez les quarts de nuit, signalez les pointages manquants et créez des totaux d'employés qui sont recalculés.
Captures réelles d’une démonstration locale du développeur avec des données simulées, et non des données clients. L’interface originale est conservée. Les vidéos courtes comprennent des séquences accélérées.
Soustraire le pointage d'arrivée du pointage de départ semble facile jusqu'à ce qu'un quart de travail dépasse minuit, qu'une pause soit enregistrée en minutes ou qu'un pointage manquant se transforme en un nombre trompeur.
Pour les périodes de travail de moins de 24 heures enregistrées uniquement par leurs heures de début et de fin, calculez la durée même si elle traverse minuit, puis déduisez la pause. Signalez séparément les pointages incomplets. Regroupez ensuite par identifiant salarié. Cette démonstration GetSheetAI utilise 3 000 enregistrements simulés pour 100 salariés.
Ce que contient le classeur terminé
Les champs de présence d'origine restent, avec les heures calculées et une colonne de statut ajoutée. Un résumé distinct des employés indique le nombre total d'heures, les relevés de présence valides et les relevés de pointages manquants. L’exemple contient 80 lignes de pointage manquant, qui restent visibles pour correction plutôt que d'être remplies silencieusement.
Lors du contrôle, une durée de travail est passée de 7 à 8 heures. Le total du salarié concerné est passé de 210 à 211 heures. Les données initiales ont ensuite été rétablies.
Préparer les données de présence
Utilisez le numéro d'identification de l'employé, le nom, le service, la date de travail, l'heure d'arrivée, l'heure de départ et les minutes de pause. Identifiez les employés par leur pièce d'identité plutôt que par leur nom. Stockez les heures d'horloge sous forme de valeurs horaires Excel et distinguez une cellule vide de minuit : minuit est une valeur horaire légitime de zéro.
Décidez si une ligne signifie un jour de travail ou un intervalle de présence. Si un employé possède plusieurs lignes à une même date, le décompte des lignes ne compte pas les jours ouvrés distincts. Les enregistrements quotidiens de l'exemple soutiennent son résumé ; d'autres mises en page nécessitent une règle de comptage différente.
Donner au plugin une requête ordinaire
Ouvrez la feuille dans Excel, ouvrez GetSheetAI et démarrez une nouvelle conversation. Vous pouvez adapter cette demande :
Calculez les heures de travail pour chaque enregistrement de présence, y compris les quarts de nuit et en soustrayant les minutes de pause. Signalez les valeurs d’arrivée ou de départ manquantes au lieu de deviner. Conservez les champs d'origine. Créez un résumé des employés avec le total des heures, les enregistrements valides et les enregistrements de pointage manquant, à l'aide de formules qui se mettent à jour lorsque je corrige une heure.
Vous n'avez pas besoin de nommer IF, MOD ou SUMIFS dans la requête. Ces fonctions sont utiles pour inspecter la manière dont le classeur généré calcule ses résultats.
Comprendre le calcul des heures
Supposons que C2 soit le pointage d'arrivée, D2 le pointage de départ et E2 les minutes de pause. Une formule anglaise illustrative Excel est :
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)
La vérification des valeurs manquantes s'exécute en premier. MOD gère une heure dépassant minuit, en multipliant par 24, convertit les jours en heures, et en divisant les minutes de pause par 60, on obtient la même unité. De 22h00 à 06h00 avec une pause de 60 minutes, le résultat est de 7 heures. Minuit ne doit pas être confondu avec un pointage manquant.
Utilisez ce modèle uniquement pour les périodes de travail connues de moins de 24 heures. Des heures d'horloge identiques pourraient signifier zéro heure ou une journée complète ; la formule ne peut pas décider cela à votre place. Pour les périodes de travail de plusieurs jours, utilisez les dates et heures complètes de début et de fin. Les résultats négatifs, les pauses plus longues qu'un quart de travail et les intervalles invraisemblablement longs doivent être signalés pour examen.
Résumer par employé sans masquer les exceptions
Regroupez les heures valides par ID d'employé, en utilisant une colonne de statut distincte pour distinguer les enregistrements valides et manquants. SUMIFS peut totaliser les heures avec des critères d'employé et de statut ; COUNTIFS peut compter les enregistrements répondant à ces mêmes conditions.
La vérification enregistrée couvrait 3 000 lignes sources, 100 lignes récapitulatives des employés, les cas de nuit et de minuit, toutes les valeurs sources et le filtre de pointage manquant de 80 lignes. Les corrections doivent mettre à jour les détails et les totaux horaires des salariés ensemble.
Avant de réutiliser votre propre rapport, cochez une équipe de jour, une équipe de nuit, une heure de minuit valide et une paire incomplète. Corrigez une fois et confirmez que le total prévu pour l'employé change exactement.
Questions courantes
Est-ce que cela calcule la masse salariale ou les heures supplémentaires légales ?
Ce cas calcule les heures de présence selon les règles énoncées. Les seuils d'heures supplémentaires, les pauses payées, les primes de congés, les arrondis et les ajustements à l'heure d'été nécessitent des règles distinctes. L'exemple enregistré ne vérifie pas la conformité de la paie.
Puis-je ajouter la fréquentation du mois prochain ?
Vérifiez que les formules et les plages récapitulatives incluent les nouveaux enregistrements. Une table Excel peut aider à développer les données source, mais vérifiez les références réelles plutôt que de supposer que chaque nouvelle ligne est incluse.
Et si les heures sont stockées sous forme de texte ?
Normalisez d'abord la source. Le guide de nettoyage des données explique la préparation ; le Guide de formule IA montre comment décrire un calcul et le vérifier.
