Date et Heure

Heures négatives dans Excel

Temps de lecture 3 minutes

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.

heures_negatives_2
Après activation de l'option les heures négatives sont visibles mais les dates ont changé

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.

heures_negatives_3

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.

heures_negatives_4

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.

heures_negatives_5

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.

heures_negatives_6

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;"")

heures_negatives_7

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.

Related posts

Créer un calendrier par semaine

Frédéric LE GUEN

Numéro de semaine à partir d’une date

Frédéric LE GUEN

Format des dates dans Excel

Frédéric LE GUEN

8 commentaires

Christine ELSER 07/09/2018 at 19:04

Géniale pour moi la soluce "Changer les paramètres d'Excel".
Après remplacer l'année x4 par la bonne sur toute la colonne et ça marche ! Merci

Répondre
valerie 02/03/2018 at 13:01

Bonjour,
Je viens de tester votre formule qui marche très bien mais dans mon cas, le total des heures va donc être négatif puisque la personne à fait 24 heures en moins et 12 heures en plus. Quand j'applique la dernière fomule ça m'affiche une cellule vide.
Pouvez-vous m'aider,
merci, Valérie

Date heure debut heure fin TOTAL ECART + ECART -
10/11/2017 04:00:00 23:50:00 19:50:00 12:50:00
10/11/2017 09:00:00 15:00:00 06:00:00 01:00:00
06/12/2017 09:00:00 15:00:00 06:00:00 01:00:00
07/12/2017 09:00:00 15:00:00 06:00:00 01:00:00
26/01/2018 09:00:00 14:30:00 05:30:00 01:30:00
31/01/2018 09:00:00 09:00:00 00:00:00 07:00:00
28/02/2018 09:00:00 10:00:00 01:00:00 06:00:00
01/03/2018 09:00:00 09:00:00 00:00:00 07:00:00
12:50:00 24:30:00

Répondre
Aline périer 21/02/2018 at 13:44

Bonjour,

Merci pour votre information sur les heures négatives, toutes les propositions ont un intérêt et votre explicatif très clair.

Je recherchais une méthode pour travailler sur des comparaisons entre des résultats sur heures positives et négatives, j'ai utilisée la formule avec TEXTE pour arriver à ressortir un résultat positif ou négatif entre deux horaires, mais ensuite il me faut comparer ces résultat à une fourchette de minutes à -3 et +8.
je m'explique: c'est un fichier de contrôle de passage des bus, il me faut ressortir par rapport à une colonne que j'ai déjà bien calculée, qui me donne le temps entre l'arrivée prévue et réelle du véhicule. il faut ensuite que je puisse compter el nombre de fois ou mon résultat, heure d'arrivée mois heures théorique d'arrivée est supérieur à -3 et supérieur à plus 8.
impossible de trouver une formule qui me fasse cela.
vous pensez pouvoir m'aider?
Aline

Répondre
TALMANT 13/12/2017 at 18:36

Bonsoir,

je m'excuse de revenir vers vous, mais je n'arrive pas a ne pas afficher la cellue vide elle met #NOM?
pouvez-vous m'expliquer pourquoi ?

merci d'avance
biens cordialement

Répondre
Frédéric LE GUEN 15/12/2017 at 17:25

Bonjour,
#NOM signifie un nom de formule inconnu. Difficile pour moi d'en dire plus sans voir le classeur.

Répondre
TALMANT 12/12/2017 at 12:10

Bonjour,

Oula je viens de de me griller le cerveau ^____^

pouvez-vous me donner les formules ?
car sur votre vidéo je n'arrive pas a bien suivre

dans mon tableau je souhaite faire donc une colonne en + un autre en - et un total qui déduira le - du temps horaire fixe
je ne suis pas très claire !

Merci bien

Répondre
TALMANT 11/12/2017 at 21:23

Bonjour,

dans la methode 4 je ne comprends comment les celulles sont vide
pouvez vous m'orienter

cordialement

Répondre
Frédéric LE GUEN 12/12/2017 at 11:31

Il suffit de faire un test pour savoir quelle est la valeur la plus grande. Ensuite, on met le test dans une fonction SI et selon le résultat on affiche la différence ou rien du tout (ça s'écrit "")

Répondre

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Ce site utilise des cookies pour améliorer votre expérience et vos recherches. Nous pensons que vous êtes dʼaccord sur ce principe mais vous pouvez refuser cette option. Accepter Continuer

Privacy & Cookies Policy