Par défaut, Excel n'affiche pas les heures négatives. Mais il existe plusieurs solutions simples pour contourner ce problème. Voici trois méthodes faciles à appliquer pour afficher des heures négatives dans Excel.
- Changer une option dans Excel.
- Utiliser le format nombre Standard.
- Afficher les résultats dans deux colonnes.
Solution 1 : Changer les options d'Excel
Cette solution est simple à appliquer, mais elle a un inconvénient majeur.
- Allez dans le menu Fichier > Options > Options avancées.
- Descendez jusqu'à la section Lors du calcul de ce classeur.
- Cochez l'option Utiliser le calendrier depuis 1904.
Les heures négatives seront visibles. Mais toutes les dates seront avancées de 4 ans immédiatement. 😱
Solution 2 : Passer au format des nombres Standard
Cette astuce est peu connue, mais efficace.
- Excel affiche ######### quand une cellule contient une heure négative.
- Changez le format en Standard pour afficher la valeur numérique réelle.
- Vous verrez une valeur négative, bien que difficile à comprendre directement.
Le problème c'est que les heures ne sont pas facile à interpréter avec cette méthode.
Solution 3 : Découper les résultats sur 2 colonnes
Cette méthode est claire mais demande plus de formules.
- Utilisez une colonne pour les écarts positifs.
- Utilisez une autre colonne pour les écarts négatifs.
- Utilisez la fonction SI pour détecter si l'écart est positif ou négatif.
Formule pour l'écart positif :
Formule pour l'écart négatif :
Faites ensuite la somme des deux colonnes.
Utilisez une fonction SI pour afficher le bon résultat (positif ou négatif).
Cette méthode est claire et ne nécessite pas de manipuler le format des cellules. Mais elle demande plus d'étapes.
Vidéo explicative
Vous pouvez retrouver ces trois méthodes en vidéo ci-dessous :
10/10/2023 @ 22:26
La meilleure méthode c'est celle avec les deux colonnes séparées :
(écart +) et (écart -)
Ensuite ont les réunis sur une autre colonne en faisant une addition de (écart +) et (écart -).
21/03/2023 @ 11:11
Bonjour. Il y a encore une alternative que j'affectionne qui consiste à séparer les besoins en calcul et les besoins en affichage. Donc garder telles quelles les colonnes d'heures parfois négatives qui s'affichent mal mais qui sont faciles à ajouter et soustraire sans exception et qui ne nécessitent aucune précaution de signe. Réduire à quelques pixel leur largeur pour ne pas que leur affichage problématique perturbe nos yeux, mais pour qu'on puisse continuer à cliquer dedans quand on veut les référencer dans les formules d'autres colonnes. Et ajouter à droite de chacune une colonne d'affichage texte correct qui lui correspond, avec une formule qu'on peut copier-coller partout =SI(cellule à gauche<0;"-";"")&TEXTE(ABS(cellule à gauche);"[h]:mm") (en cliquant dans la cellule à gauche pour mettre en place la bonne référence là où j'ai écrit cellule à gauche).
31/12/2022 @ 11:56
Merci beaucoup pour ta 4ieme option.
Je suis routière, et j'ai besoin par moment de travaillé pendant que mon camion soit en mode repos(Chronotachygraphe), mais j'en ai marre de donnée des heures sans les récupérées. Depuis 6 mois je me sert d'un classeur avec une colonne avec les heures que je donne et une autre colonne pour les heures que je récupère. Et je prend une 3ieme colonne pour faire un total des lignes du dessus et je rajoute la ligne actuel. Mais la j'ai eu le probleme des #.
Et je trouve ta méthode bien mieux. Je fais uniquement 2 colonnes, ce que je donne avec un total a la toute fin et une colonne ce que je récupère avec un total a la toute fin.
Et a coté des totaux, je fais de case avec les si pour savoir combien je dois donner ou reprendre des heures.
Merci beaucoup!
Laura
31/12/2022 @ 14:38
Parfait si j'ai pu vous donner une solution qui vous permet de suivre votre activité.
Bon courage sur les routes.
25/05/2022 @ 08:03
Petite proposition d'amélioration :
1- Editeur le fomrat de la cellule en format personnalisée : [hh]:mm;@
2- modifier la formule comme ceci : =SI(B7<E2;"-"&TEXTE(ABS(B7-E2);"[hh]:mm;@");B7-E2)
Avec ça, les heures négatives et positives supérieurs à 24h seront affichées correctement
14/05/2022 @ 17:23
bonjour j'ai pris la formule
=SI(H17<$H$1,"-"&TEXTE(ABS(H17-$H$1),"hh:mm;ss"),H17-$H$1)
pour calculer mes heures à la semaine (H17= 0:00 et $H$1 = 35), mais en résultat j'obtiens -11:00 je ne comprends pas pourquoi sa ne met pas -35:00, car pour les autres cellules qui sont en négatifs cela fonctionne
25/05/2022 @ 09:30
Bonjour,
il faut remplacer la formule par :
=SI(H17<$H$1,"-"&TEXTE(ABS(H17-$H$1),"[h]:mm:ss"),H17-$H$1)
En effet hh bloque jusqu'a 24 et fait la différence ; [h] permet d'avoir l'heure totale
Cordialement
16/11/2020 @ 12:24
Bonjour,
Je viens de créer un tableau horaires en utilisant la solution deux : fonction TEXTE.
Sachant que mon tableau est construit de la sorte :
Jour - Horaires prévus - Horaires réalisés - TOTAL (reprend les horaires réalisés) - Écart
Ça me calcule bien l’écart, Cependant, je n’arrive pas à faire une SOMME comme il y a du + et du -
Comment pourrais-je faire ?
Je vous remercie de votre réponse
Ninie
23/02/2023 @ 11:01
Avez-vous eu votre réponse : j'ai le même problème et n'arrive pas à la résoudre
23/02/2023 @ 12:08
A partir du moment où vous utilisez la fonction TEXTE, le contenu des cellules est du texte et donc il n'est pas possible de faire une SOMME.
La meilleure solution ça reste de faire le tableau avec une colonne pour les heures positives et une autre pour les heures négatives
07/09/2018 @ 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
02/03/2018 @ 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
21/02/2018 @ 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
13/12/2017 @ 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
15/12/2017 @ 17:25
Bonjour,
#NOM signifie un nom de formule inconnu. Difficile pour moi d'en dire plus sans voir le classeur.
12/12/2017 @ 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
11/12/2017 @ 21:23
Bonjour,
dans la methode 4 je ne comprends comment les celulles sont vide
pouvez vous m'orienter
cordialement
12/12/2017 @ 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 "")