Pourquoi un export de pointage résiste aux calculs
Un logiciel de pointage produit un fichier fait pour être lu, pas pour être calculé. Les dates y sont écrites comme des chaînes de caractères, parfois avec des séparateurs inhabituels, et les durées exprimées en centièmes d'heure : sept heures et quart apparaissent sous la forme 7,25. Excel ne peut alors ni trier, ni filtrer, ni additionner ces valeurs.
Le symptôme est net : le tri chronologique range les lignes par ordre alphabétique et la somme des durées donne un décimal à retraduire mentalement. Le repère le plus simple reste l'alignement, la documentation Microsoft indiquant que les dates mises en forme comme du texte s'alignent à gauche au lieu de s'aligner à droite.
La méthode en cinq passes
On n'écrase jamais la colonne reçue : les versions propres se construisent dans des colonnes d'appoint, et l'original n'est remplacé qu'après vérification. Chaque passe traite un seul problème, ce qui permet d'isoler la ligne fautive quand une formule renvoie une erreur.
La division par 24 s'explique simplement : Excel compte une journée comme l'unité 1, donc une heure vaut un vingt-quatrième. Pour continuer à calculer, mieux vaut conserver la valeur numérique et n'agir que sur le format d'affichage.
- Passe 1, inventaire : repérer les colonnes alignées à gauche, compter les lignes, noter les cellules vides.
- Passe 2, nettoyage : SUPPRESPACE pour les espaces en trop, EPURAGE pour les caractères non imprimables, SUBSTITUE pour remplacer un séparateur inattendu, par exemple le point par une barre oblique.
- Passe 3, dates : =DATEVAL(cellule) en colonne d'appoint, copie, collage spécial en valeurs, puis format de date.
- Passe 4, durées : =cellule/24, puis format personnalisé hh:mm pour une durée unitaire, [h]:mm pour un cumul.
- Passe 5, contrôle : les dates s'alignent à droite, le filtre propose un regroupement par année et par mois, et un total recalculé à la main sur trois lignes concorde.
Centièmes ou format horaire : que conserver
Les centièmes se prêtent aux multiplications et aux moyennes ; le format horaire se lit sans effort et se compare à un planning. L'arbitrage courant garde la durée Excel pour les calculs et affiche les deux colonnes côte à côte lors d'une relecture.
Les correspondances ci-dessous se recalculent à la main, de quoi contrôler ses propres formules sur quelques lignes avant de les étendre à tout l'export.
| Centièmes | Durée Excel après division par 24 | Affichage hh:mm | Usage |
|---|---|---|---|
| 0,75 | 0,03125 | 00:45 | Vacation courte |
| 7,25 | 0,302083 | 07:15 | Comparaison avec un planning |
| 8,10 | 0,3375 | 08:06 | Détection d'un dépassement |
| 21,85 | 0,910417 | 21:51 | Cumul hebdomadaire, format [h]:mm |
Mise en situation fictive : l'export d'un prestataire de nettoyage
Les données qui suivent sont entièrement inventées : aucun fichier, aucune entreprise et aucun salarié réel. Reconstituez le tableau dans un classeur vierge, en mettant la colonne B au format Texte avant la saisie pour reproduire volontairement le défaut. N'y saisissez aucune information confidentielle ni donnée personnelle.
Colonne A, agent : AGENT-01, AGENT-01, AGENT-02, AGENT-02, AGENT-03. Colonne B, date en texte : 02.03.2026, 03.03.2026, 02.03.2026, 31.02.2026, 03.03.2026. Colonne C, durée en centièmes : 7,25, 6,50, 8,10, 4,00, 0,75. Travail demandé : en D2, écrivez =DATEVAL(SUBSTITUE(B2;".";"/")) et étirez vers le bas ; constatez l'erreur de valeur sur le 31.02.2026, et isolez cette ligne pour vérification auprès de l'émetteur plutôt que de deviner la date ; en E2, écrivez =C2/24 avec le format hh:mm ; totalisez les quatre durées exploitables au format [h]:mm. Vérification à la main : 7,25 + 6,50 + 8,10 + 0,75 = 22,60 centièmes, soit 22:36. Si l'affichage diffère, le format est en cause avant la formule.
Les erreurs qui reviennent le plus souvent
La plupart des blocages viennent d'un diagnostic trop rapide : une valeur qui ressemble à une date n'en est pas une, et un nombre qui ressemble à une durée n'en est pas une tant que le format n'a pas été appliqué.
Avant de conclure qu'une colonne est propre, lancez un tri croissant puis un tri décroissant et regardez les valeurs extrêmes : les anomalies se rangent presque toujours aux deux bouts.
| Situation observée | Erreur fréquente | Action |
|---|---|---|
| Colonne de dates encore alignée à gauche après conversion | Format de date appliqué sans convertir la valeur | Reprendre avec DATEVAL en colonne d'appoint, coller en valeurs, puis formater |
| DATEVAL échoue sur quelques lignes seulement | Soupçonner la formule alors que la date n'existe pas ou contient un espace invisible | Isoler ces lignes, appliquer SUPPRESPACE, faire confirmer les dates impossibles par l'émetteur |
| Le total affiche 03:15 au lieu de 27:15 | Format hh:mm utilisé pour un cumul | Passer au format personnalisé [h]:mm, prévu pour le temps écoulé |
| CNUM échoue sur les durées | Séparateur décimal en point quand le classeur attend la virgule | Remplacer le point par une virgule avec SUBSTITUE avant la conversion |
| Les heures converties s'affichent en dates | Format hérité d'une colonne voisine | Réappliquer un format horaire sur la seule colonne d'appoint |
Préparer ce travail avant une formation
L'exercice enchaîne lecture d'un fichier reçu, fonctions de texte, fonctions de date, formats de nombre personnalisés et contrôle du résultat. Ce sont les briques travaillées dans la formation Maîtriser Microsoft Excel du catalogue.
Pour préparer une session, rejouez l'exercice fictif sur un classeur vierge et notez les manipulations que vous refaites à la main, ainsi que les points restés sans réponse.
Questions fréquentes
Comment savoir si une date est réellement reconnue par Excel ?
Regardez l'alignement par défaut de la cellule : à droite, la date est reconnue ; à gauche, elle est restée du texte. Le filtre de colonne confirme le diagnostic en proposant un regroupement par année et par mois.
Que fait exactement la fonction DATEVAL ?
Elle convertit une date écrite en texte en numéro de série, la documentation donnant 39448 pour le 1er janvier 2008. L'heure éventuellement présente est ignorée et la date doit se situer entre le 1er janvier 1900 et le 31 décembre 9999.
Pourquoi faut-il diviser les centièmes par 24 ?
Excel compte les durées en fraction de journée, l'unité 1 valant vingt-quatre heures. La documentation décrit cette conversion avec l'opérateur de division, complété au besoin par la fonction TEXTE.
Mon total d'heures semble faux alors que les formules sont bonnes, pourquoi ?
C'est généralement l'affichage : hh:mm repart à zéro après 24 heures et montre 03:15 au lieu de 27:15. Le format personnalisé [h]:mm, avec les crochets, conserve le cumul réel.
Repères et limites
Les données de l'exercice sont fictives et ne correspondent à aucun fichier réel. L'écriture des formules et le comportement de DATEVAL dépendent des paramètres régionaux du poste, notamment du séparateur décimal et de l'ordre jour-mois, ainsi que de la version d'Excel utilisée ; vérifiez vos résultats sur quelques lignes avant de généraliser. Cet article ne traite ni du décompte du temps de travail ni des règles applicables à la paie.
Sources et ressources
Donnez une suite concrète à votre projet.
Consultez le parcours « Maîtriser Microsoft Excel » et ses modalités pour préparer votre échange avec notre équipe.
Demander le programme par e-mailVotre messagerie s’ouvre avec la formation et l’article préremplis. Envoyez ensuite votre demande à romaingilles@hotmail.com.
Consulter le programme →