Heures négatives dans Excel

Excel ne sait pas afficher les heures négatives ! Quand vous faites une opération sur les heures et que le résultat doit retourner un horaire négatif, Excel vous afficher des ############.

Vidéo de présentation

4 solutions différentes vous sont expliquées pour afficher les heures négatives dans Excel. A vous de trouver la solution qui correspond le mieux à vos besoins.

Changer les options d'Excel

La technique la plus connue pour corriger le problème d'affichage des heures négatives dans Excel, consiste à changer un paramètre dans les options.

1. Aller dans le menu Fichier>Options>Options avancées
2. Dans la rubrique Lors du calcul de ce classeur (vers la fin de la liste des options)
3. Cocher l'option Utiliser le calendrier depuis 1904.

En cochant cette option toutes vos heures négatives seront maintenant visibles dans les cellules au format numérique.

Le très gros problème liés à cette méthode se situe au niveau de toutes les dates de votre classeur. En effet en activant cette option toutes les dates de votre classeur sont augmentées de 4 années instantanément.

Utiliser la fonction TEXTE

L'autre solution consiste à retourner les dates négatives grâce à la fonction TEXTE.

En effectuant un test entre 2 dates dans une fonction SI, il est possible de construire dans la partie qui va retourner la valeur négative.

Si la différence entre les heures retournent un écart négatif, alors on utilise la fonction TEXTE, sinon, on a juste à faire la différence.

=SI(D3<$G$2;"-"&TEXTE(ABS(D3-$G$2);"hh:mm:ss");D3-$G$2)

Dans l'exemple ci-dessous, la fonction va nous afficher les résultats corrects quand le test détecte que la valeur de la colonne D est plus grand que la cellule.

Le problème avec cette solution c'est que toutes les heures négatives sont au format texte. Donc il n'est plus possible de faire des calculs par la suite (comme une somme par exemple)

Passer au format Standard

Une astuce méconnue consiste à transformer votre résultat ###### au format Standard.

Là, vous voyez que les données négatives s'affichent normalement. Preuve que c'est juste le format Heure qui n'est pas capable d'afficher les heures négatives.

 

Le point négatif de cette méthode concerne vos utilisateurs . Seront-ils prêt à accepter de voir les heures écrite sous forme décimale ? De plus, si le résultat renvoie un nombre négatif, la seule façon d'afficher ce résultat serait de repasser par la fonction TEXTE vu au point précédent.

Utiliser la fonction SI sur 2 colonnes

Cette dernière méthode consiste à écrire les heures positive dans une colonne heure négative dans une autre colonne. Toutes les données sont écrites positivement un petit peu comme sur un relevé banque dans les colonnes débit et crédit.

Pour réaliser cette méthode nous allons effectuer une fonction SI pour savoir si une heure est plus ou moins grande que l'autre. Selon le résultat, nous allons afficher uniquement les cas positif.

Formule pour l'écart positif

=SI($D2>$G$2;$D2-$G$2;"")

Formule pour l'écart négatif

=SI($D2<$G$2;$G$2-$D2;"")

Ensuite il ne reste plus qu'à faire la somme de chacune des colonnes.

Et de comparer le résultat par une fonction SI, pour savoir si nous devons afficher un horaire positif pour un horaire négatif.

=SI(E174>F174;E174-F174;"")

Cette méthode permet d'afficher clairement les horaires positifs et négatifs sans avoir à créer des formules complexes. Par contre, cela nécessite d'écrire le résultat sur 2 colonnes et aussi d'avoir beaucoup plus de formules à écrire par rapport aux autres méthodes.

Articles complémentaires

Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.