Comment afficher correctement une durée négative dans Excel ?
Travailler avec des calculs de temps dans Excel peut être difficile lorsque vous soustrayez une heure ultérieure d'une heure antérieure. Par exemple, si vous soustrayez 12:20 de 10:15, Excel renvoie une erreur ###### au lieu d'une valeur de temps négative, comme illustré dans les captures d'écran ci-dessous. Ce problème survient couramment car Excel, par défaut, ne prend en charge que les valeurs de temps positives dans son système de date standard. Résoudre ce problème est important pour un planning précis, la gestion des feuilles de temps et toutes les situations où il est nécessaire de refléter des durées négatives ou des calculs d'heures supplémentaires.
➤ Afficher correctement le temps négatif en changeant le système de dates par défaut d'Excel
➤ Afficher correctement le temps négatif avec des Formules
➤ Afficher le temps négatif en utilisant du code VBA (format de texte personnalisé)
Afficher correctement le temps négatif en changeant le système de dates par défaut d'Excel
Lorsque vous travaillez avec des calculs de temps, une manière simple d'afficher des valeurs de temps négatives est de basculer le classeur vers le système de dates de 1904. Cela permet aux résultats négatifs de s'afficher correctement (par exemple, « -2:05 ») au lieu d'erreurs. C'est pratique lorsque vous devez souvent afficher des durées négatives, comme dans le suivi de projet ou la planification de quarts de travail.
Remarques :
- Le passage au système de dates de 1904 décale toutes les dates existantes dans le classeur d'environ 1 462 jours (~4 ans). Vérifiez les autres champs de date après le changement.
- Ce paramètre est spécifique à chaque classeur (il n'affecte pas d'autres fichiers).
- Pour la collaboration, soyez conscient que les utilisateurs sur le système de dates par défaut de 1900 peuvent voir des résultats différents si des classeurs sont mélangés.
- Vous ne pouvez pas utiliser à la fois les systèmes de dates de 1900 et de 1904 dans le même classeur en même temps.
1. Ouvrez la boîte de dialogue Options Excel. Dans Excel 2010/2013, allez dans Fichier > Options. Dans Excel 2007, cliquez sur le bouton Office et choisissez Options Excel.
2. Dans Options Excel, cliquez sur Avancé. Sous Lors du calcul de ce classeur, cochez Utiliser le système de dates de 1904. Voir la capture d'écran ci-dessous :
3. Cliquez sur OK. Les différences de temps négatives s'afficheront désormais correctement, comme indiqué ici :
Conseils :
- Si la préservation de l'exactitude historique des dates est critique, envisagez des approches alternatives (par exemple, sortie de texte personnalisée via formule ou VBA) au lieu de changer de système de dates.
- Documentez le changement de système de dates dans le classeur (par exemple, sur une feuille « LisezMoi ») pour éviter toute confusion pour les autres utilisateurs.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Afficher correctement le temps négatif avec des Formules
Si votre flux de travail ne permet pas de changer le système de dates (pour des raisons de compatibilité ou de reporting), vous pouvez obtenir un affichage correct des temps négatifs en utilisant des formules. Cette méthode affiche les durées négatives sous forme de texte, permettant des résultats visuels clairs sans déclencher d'erreur dans Excel. Cette approche est largement utilisée dans des scénarios tels que les calculs de déficit d'heures de travail, le suivi des dépassements de projets ou les comparaisons de temps écoulé.
1. Saisissez les heures que vous souhaitez calculer dans deux cellules distinctes (par exemple, A1 et A2). Dans une cellule vide, tapez la formule suivante pour calculer et formater la différence négative :
=TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM")Ici, A1 et A2 représentent les deux valeurs de temps. Ajustez les références de cellules selon vos données. Cette formule fournit un résultat sous forme de texte, afin que vous puissiez voir des signes négatifs si nécessaire, même si Excel retournerait normalement une erreur lors du calcul.
2. Appuyez sur Entrée. Le résultat apparaîtra sous forme d'une chaîne de temps formatée avec un signe moins si elle est négative, comme indiqué ci-dessous :
Formule alternative :
Vous pouvez également utiliser cette formule, qui affiche conditionnellement un signe moins lorsque le résultat est négatif :
=IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1)Dans ce cadre, A2 est l'heure plus petite ou antérieure, et A1 est l'heure plus tardive, mais vous devriez ajuster cela en fonction de la direction de votre calcul. Cette méthode produit un résultat texte pour les valeurs négatives, facilitant ainsi les rapports, mais notez également que les valeurs résultantes ne peuvent pas être totalisées ou sommées comme des heures sans conversion supplémentaire.
Remarques :
- Les résultats sont du texte, pas des valeurs de temps réelles, donc d'autres calculs de temps ne fonctionneront pas sauf si vous convertissez la sortie en format de temps.
- L'approche par formule est indépendante du classeur, utile pour le partage et évite les conflits de paramètres globaux.
- Faites attention à référencer les bonnes directions de cellules pour éviter les erreurs logiques (par exemple, soustraire la fin du début pour obtenir un négatif).
Afficher le temps négatif en utilisant du code VBA (format de texte personnalisé)
Une autre méthode efficace pour gérer les temps négatifs dans Excel consiste à utiliser une macro VBA. Cela est particulièrement utile lorsque vous voulez une manière fluide d'automatiser la détection et l'affichage des calculs de temps négatifs dans votre feuille de calcul, même pour de grands lots, sans changer les paramètres système ni compter sur des formules pour chaque cellule. Cette méthode convient aux utilisateurs avancés gérant des saisies de données horaires en cours, comme les registres de quarts des employés, et empêche l'erreur ###### en convertissant les résultats de temps négatifs en chaînes de texte personnalisées (par exemple, préfixées par un signe moins).
Avantages : Permet le traitement par lot des cellules sélectionnées, conserve les données originales, peut formater les résultats selon les besoins, et n'affecte pas les paramètres du système de dates d'Excel.
Inconvénients : Requiert l'activation des macros, non adapté si vous voulez conserver les valeurs de temps natives d'Excel pour des calculs au-delà de l'affichage.
Précautions et conseils :
- Enregistrez toujours une sauvegarde de votre fichier avant d'exécuter des scripts VBA.
- Cette solution transforme les résultats de temps négatifs en texte. Si des calculs futurs sont nécessaires, utilisez les données numériques originales, pas le résultat d'affichage généré par la macro.
- Les macros peuvent être désactivées par vos politiques IT. Activez les macros dans Excel pour utiliser cette solution.
1. Dans Excel, cliquez sur Développeur > Visual Basic. Dans la fenêtre de l'éditeur VBA, cliquez sur Insérer > Module, puis collez le code suivant dans la fenêtre du module :
Sub DisplayNegativeTimeAsText()
Dim WorkRng As Range
Dim Cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select time calculation result cells", xTitleId, WorkRng.Address, Type:=8)
For Each Cell In WorkRng
If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
If InStr(Cell.NumberFormat, ":") > 0 Then
If Cell.Value < 0 Then
Cell.NumberFormat = "@"
Cell.Value = "-" & Format(Abs(Cell.Value), "h:mm")
End If
End If
End If
Next
End Sub2. Pour exécuter la macro, cliquez sur le 
Exécuter bouton dans l'éditeur VBA ou appuyez sur F5. Une boîte de dialogue apparaîtra, vous permettant de sélectionner la plage de cellules contenant vos résultats de différence de temps (même celles montrant l' ###### erreur).
La macro va automatiquement analyser la plage sélectionnée. Pour toute cellule contenant une valeur de temps négative (qui afficherait normalement ######), elle change la cellule en format texte et convertit la valeur en un format lisible, comme "-2:05". Les résultats de temps positifs restent inchangés. Si vous voulez revenir au format de temps normal, vous devez restaurer les valeurs de cellules d'origine ou utiliser une action d'annulation/restauration.
Dépannage et conseils supplémentaires :
- Si vous sélectionnez une plage sans différences de temps (par exemple, cellules vides ou valeurs non temporelles), la macro les ignore en toute sécurité.
- Si aucune cellule n'est sélectionnée, la macro demandera une plage et n'affectera pas les autres données.
- Revoyez toujours le formatage après exécution ; les cellules affectées sont désormais du texte, donc les calculs de temps originaux ne fonctionneront pas jusqu'à leur réinitialisation.
- Pour étendre ou adapter la macro à d'autres formats d'affichage ou structures plus longues d'heures-minutes-secondes, modifiez la section
Format(Abs(Cell.Value), "h:mm")selon les besoins.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels