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 ############.
Voici 4 techniques pour contourner le problème mais chacune a ses avantages et ses inconvénients. À vous de choisir celle qui correspond le mieux à votre travail
Sommaire
Changer les paramètres 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.
Aller dans le menu Fichier>Options>Options avancées et dans la rubrique Lors du calcul de ce classeur (vers la fin de la liste des options) 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.
Seulement 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.
Bien sûr, après coup, vous pouvez changer toutes les dates de votre classeur mais il y a de fortes chances que vous en oubliez et le résultat serait dramatique pour vos analyses de données.
Utiliser la fonction TEXTE
L’autre solution consiste à retourner les dates négatives grâce à l’instruction 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, une écriture qui va forcer la mise en forme grâce à l’instruction TEXTE.
=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 et donc il n’est plus possible d’inclure ces données dans un calcul comme une somme par exemple
Passer au format Standard
Si vous devez créer un classeur dans lequel vous avez des heures positives et négatives et que vous devez réaliser le cumul des heures positives et négatives ensemble, la meilleure solution consiste à changer le format de la colonne contenant vos heures au format standard.
Rien qu’en changeant de format les heures négatives matérialisées par des #####, s’affichent maintenant avec un nombre décimal négatif. Preuve que le problème des heures négatives dans Excel est juste un problème d’affichage.
Dès lors il vous est possible d’effectuer toute sorte d’opération avec vos heures positives et 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 mais 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 dans chacune des colonnes effectuer une fonction SI pour savoir si une heure est plus ou moins grande que l’autre et selon le résultat affiché 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éthode.
