Comment afficher correctement une durée négative dans Excel ?
Travailler avec des calculs horaires dans Excel peut s’avérer délicat lorsqu’on soustrait une heure postérieure d’une heure antérieure. Par exemple, si vous soustrayez 12 h 20 de 10 h 15, Excel affiche une erreur ###### au lieu d’une valeur horaire négative, comme illustré dans les captures d’écran ci-dessous. Ce problème survient fréquemment, car Excel ne prend pas en charge par défaut les valeurs horaires négatives dans son système de dates standard. Le résoudre est essentiel pour assurer une planification précise, gérer efficacement les feuilles de temps et traiter toute situation exigeant l’expression de durées négatives ou le calcul d’heures supplémentaires.
➤ Affichez correctement les durées négatives en modifiant le système de dates par défaut d’Excel
➤ Affichez correctement les durées négatives à l’aide de formules
➤ Affichez les durées négatives à l’aide d’un code VBA (personnalisé Format de texte)
Afficher correctement les durées négatives en modifiant le système de dates par défaut d’Excel
Lorsque vous travaillez avec des calculs horaires, une méthode simple pour afficher des valeurs négatives consiste à basculer le classeur vers le système de dates 1904. Cela permet d’afficher correctement les résultats négatifs (par exemple, « -2:05 ») au lieu d’erreurs. Cette solution est particulièrement utile si vous devez fréquemment gérer des durées négatives, notamment pour le suivi de projets ou la planification d’équipes.
Remarques :
- Le passage au système de dates 1904 décale toutes les dates existantes du classeur d’environ 1 462 jours (~4 ans). Vérifiez les autres champs de date après cette modification.
- Ce paramètre est spécifique au classeur (il n’affecte pas les autres fichiers).
- Pour la collaboration, sachez que les utilisateurs du système de dates 1900 par défaut peuvent obtenir des résultats différents si des classeurs sont mélangés.
- Vous ne pouvez pas utiliser simultanément les systèmes de dates 1900 et 1904 dans un même classeur.
1. Ouvrez la boîte de dialogue Options Excel. Dans Excel 2010/2013, accédez à 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 1904. Voir la capture d’écran ci-dessous :
3. Cliquez sur OK. Les différences horaires négatives s’afficheront désormais correctement, comme illustré ici :
Conseils :
- Si la préservation de l’exactitude historique des dates est essentielle, privilégiez d’autres approches — comme une sortie texte personnalisée via formule ou VBA — plutôt que de modifier le système de dates.
- Documentez le changement de système de dates dans le classeur (par exemple, sur une feuille « Lisez-moi ») pour éviter toute confusion chez les autres utilisateurs.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Afficher correctement les durées négatives à l’aide de formules
Si votre flux de travail ne permet pas de modifier le système de dates — par souci de compatibilité ou d’obligations de reporting —, vous pouvez afficher correctement les durées négatives à l’aide de formules. Cette méthode présente les durées négatives sous forme de texte, garantissant une visualisation claire sans provoquer d’erreur dans Excel. Elle est couramment utilisée pour calculer des déficits d’heures travaillées, suivre les dépassements de délais de projet ou comparer des durées écoulées.
1. Saisissez les heures à calculer dans deux cellules distinctes (par exemple, A1 et A2). Dans une cellule vide, saisissez 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 horaires. Ajustez les références de cellules en fonction de vos données. Cette formule renvoie un résultat au format texte, ce qui permet d’afficher un signe moins si nécessaire — même dans les cas où Excel générerait normalement une erreur lors du calcul.
2. Appuyez sur Entrée. Le résultat s’affiche sous forme d’une chaîne horaire formatée, précédée d’un signe moins si elle est négative, comme illustré 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 cette configuration, A2 correspond à l’heure la plus ancienne (ou la plus petite), et A1 à l’heure la plus récente. Toutefois, veillez à adapter ces références en fonction de votre calcul. Cette méthode renvoie un résultat au format texte pour les valeurs négatives, ce qui facilite le reporting ; notez toutefois que ces résultats ne peuvent pas être additionnés comme des durées sans une conversion supplémentaire.
Remarques :
- Les résultats sont du texte et non de véritables valeurs horaires ; ainsi, les calculs temporels ultérieurs ne fonctionneront pas, sauf si vous reconvertissiez le résultat au format horaire.
- L’approche par formule est indépendante du classeur, idéale pour le partage et exempte de conflits liés aux paramètres globaux.
- Veillez à bien référencer les cellules dans le bon ordre pour éviter les erreurs logiques (par exemple, soustraire la valeur de fin de celle de début et obtenir un résultat négatif).
Afficher les durées négatives à l’aide d’un code VBA (complément personnalisé Format de texte)
Une autre méthode efficace pour gérer les durées négatives dans Excel consiste à utiliser une macro VBA. Elle est particulièrement utile lorsque vous souhaitez détecter et afficher automatiquement les calculs horaires négatifs dans votre feuille de calcul — même sur de grands volumes de données — sans modifier les paramètres système ni insérer des formules dans chaque cellule. Cette approche convient aux utilisateurs avancés qui traitent régulièrement des saisies horaires, comme les registres de service du personnel, et évite l’erreur ###### en convertissant les résultats horaires négatifs en chaînes de texte personnalisées (par exemple, préfixées d’un signe moins).
Avantages : Permet de traiter par lots les cellules sélectionnées, préserve les données d’origine, autorise une mise en forme personnalisée des résultats et n’affecte pas les paramètres du système de dates d’Excel.
Inconvénients : Nécessite l’activation des macros et ne convient pas si vous souhaitez conserver les valeurs horaires natives d’Excel pour des calculs au-delà de l’affichage.
Précautions et conseils :
- Sauvegardez toujours une copie de votre fichier avant d’exécuter des scripts VBA.
- Cette solution transforme les résultats horaires négatifs en texte. Si des calculs ultérieurs sont nécessaires, utilisez les données numériques d’origine, et non le résultat affiché généré par la macro.
- Les macros peuvent être désactivées par les politiques informatiques de votre entreprise. Activez-les dans Excel pour profiter pleinement de cette solution.
1. Dans Excel, cliquez sur Développeur > Visual Basic. Dans la fenêtre de l’éditeur VBA, cliquez sur Insertion > 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 bouton 
Exécuter dans l’éditeur VBA ou appuyez sur F5. Une boîte de dialogue s’ouvrira alors, vous permettant de sélectionner la plage de cellules contenant vos résultats de différences horaires (y compris celles affichant l’erreur)######).
La macro analysera automatiquement la plage sélectionnée. Pour toute cellule contenant une valeur horaire négative (qui afficherait normalement)######), elle applique le format Texte et convertit la valeur en un format lisible, tel que « -2:05 ». Les valeurs horaires positives restent inchangées. Pour revenir au format horaire normal, vous devez restaurer les valeurs d’origine des cellules ou utiliser une action d’annulation/restauration.
Dépannage et conseils supplémentaires :
- Si vous sélectionnez une plage ne contenant pas de données horaires (par exemple, des cellules vides ou des valeurs non horaires), la macro les ignore en toute sécurité.
- Si aucune cellule n’est sélectionnée, la macro vous invitera à définir une plage sans modifier les autres données.
- Vérifiez toujours la mise en forme après exécution : les cellules concernées contiennent désormais du texte, donc les calculs horaires d’origine ne fonctionneront plus tant qu’elles n’auront pas été réinitialisées.
- Pour étendre ou adapter la macro à d’autres formats d’affichage ou à des structures heures-minutes-secondes plus longues, modifiez la section
Format(Abs(Cell.Value), "h:mm")selon vos besoins.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels