Comment afficher les heures négatives dans Excel

Le constat

Excel ne peut afficher des heures négatives !
Lorsqu’une heure est négative, Excel renvoie une suite de croisillon #################

Méthodes

Voici cinq méthodes pour pallier ce problème. Chacune d’elles ayant des avantages et inconvénients.

1. Utiliser la fonction TEXTE
2. Modifier une option d’excel
3. Utiliser deux lignes ou deux colonnes
4. Utiliser le format Nombre
5. Utiliser le format personnalisé

Utiliser la fonction TEXTE

La fonction TEXTE converti un nombre en chaîne de caractères.
Ainsi nous allons utiliser comme formule la combinaison des fonctions SI, ABS et TEXTE (Voir illustration ci-dessous).

Avantage : Toutes les heures négatives seront bien affichées sous forme de texte tandis que les heures positives seront affichées en format heure (HH:MM)
Inconvénient : Il sera impossible d’effectuer un calcul sur les cellules négatives concernées par le renvoi d’un texte.

Modifier une option d’excel

[Fichier] – Options – Options avancées et dans la rubrique Lors du calcul de ce classeur, cocher l’option Utiliser le calendrier depuis 1904.

Avantage : Toutes les heures négatives seront bien affichées
Inconvénient : Les dates encodées précédemment dans ce classeur auront quatre ans de plus.

Utiliser deux lignes ou deux colonnes

Cette méthode qui nécessite l’emploi de deux lignes ou deux colonnes, l’une affichant les heures positives, l’autre les heures négatives permet d’afficher les heures correctement dans ces deux colonnes.
L’astuce consiste à transformer les heures négatives en valeur positives en utilisant la fonction ABS

Avantage : On peut effectuer des calculs sur toutes les cellules.
Inconvénient : L’emploi d’une ligne ou d’une colonne supplémentaires

Utiliser le format nombre

Le but ici est d’afficher le format nombre et pas le format Heure (hh:mm)

Aller dans le Format Cellule (Clic droit) et choisir Nombre avec deux décimales en lieu et place de Heure

Avantage : Le calcul des heures tant positives que négatives est possible.
Inconvénient : Les heures positives et négatives s’afficheront en nombre (décimal) comme illustré ci-dessous.

Utiliser le format personnalisé

Le format personnalisé permet entre-autres de définir un format distinct pour les valeurs positives, négatives et nulles. De plus on peut appliquer une couleur différentes à celles-ci.
Dans cette astuces, les heures positives apparaissent au format Heure alors que les négatives apparaissent au format nombre
Aller dans le Format Cellule (Clic droit) et choisir Format personnalisé et ensuite appliquer ce format hh:mm;(-0) ou hh:mm;[Rouge](-0) si l’on souhaite appliquer une couleur rouge pour les valeurs négatives. (Voir illustration)Résultat

Avantage : Les heures positives apparaissent au format Heure et le calcul des heures tant positives que négatives est possible.
Inconvénient : Les heures négatives s’afficheront en nombre (décimal).

Conclusion

A vous de faire votre propre opinion.
Personnellement j’opte pour la fonction TEXTE si je ne dois pas effectuer des calculs directement sinon l’utilisation des deux lignes ou du format personnalisé ont ma préférence.
La méthode qui a ma cote la plus basse est l’option Date à partir de 1904.