Saisie de temps négatifs (Microsoft Excel)

Guido doit entrer des temps négatifs dans les cellules. S’il entre une heure positive (2h00), Excel le reconnaît parfaitement. S’il essaie de taper -2: 00, Excel lui dit qu’il y a une erreur dans sa formule. Guido s’interroge sur le moyen le plus simple de saisir une valeur de temps négative dans une cellule et de faire en sorte qu’Excel la reconnaisse comme une valeur de temps.

Avant de fournir quelques réponses possibles à la question, il est bon de comprendre la façon dont Excel affiche les dates et les heures. En interne, les dates et heures sont stockées sous forme de numéros de série, qui ne sont que des nombres réels. Supposons que vous saisissiez une date dans la cellule B4, comme « 17 mars 2020 » (sans les guillemets. Excel comprend que vous saisissez une date et il la convertit en numéro de série. Vous ne pouvez pas dire qu’il s’agit d’un numéro de série, cependant , car Excel vous montre utilement la date représentée par le numéro de série. (Voir Figure 1.)

Figure 1. Saisie d’une date dans Excel.

Notez que la date apparaît dans la cellule comme 17-mars-20 et dans la barre de formule comme 3/17/2020. (La façon exacte dont Excel affiche la date dépend de la façon dont vous l’avez entrée et de vos paramètres régionaux Windows.) La date affichée dans la cellule (17 mars 20) est le résultat du formatage de la cellule. Lorsque vous avez entré la date dans la cellule et que la cellule avait auparavant un format « Général », Excel a modifié la mise en forme de la cellule pour afficher la date comme bon vous semble.

En raison de la date affichée dans la barre de formule (17/03/2020), vous pourriez penser que c’est t La façon dont Excel stocke les dates en interne. Pas ainsi; vous pouvez le voir en entrant une formule dans une cellule adjacente (= B4), puis en formatant cette cellule en « Général ». (Voir la figure 2.)

Figure 2. Voir comment Excel stocke une date.

La barre de formule affiche la formule (= B4), comme il se doit, mais la cellule affiche la valeur 43907. Si vous avez mis en forme la date dans la cellule B4 comme générale, elle afficherait également la même valeur. Ceci est le numéro de série; c’est la façon dont Excel stocke les dates en interne.

Mais que signifie le numéro de série? C’est un « décompte » du nombre de jours depuis une date de base. En général, cette date de base est le 1er janvier 1900, ce jour étant le jour 1. Le 2 janvier 1900 serait le jour 2, et ainsi de suite. Ainsi, 43907 est le 43 907 jour commençant le 1er janvier 1900.

Cependant, il est possible que la date de base d’un classeur soit différente. Excel, à des fins historiques, comprend deux dates de base possibles. L’autre est appelé le système de date de 1904. Il commence à compter avec le 1er janvier 1904. Contrairement au système de date 1900, le système de date 1904 commence à compter avec 0. Ainsi, le 1er janvier 1904, a un numéro de série de 0, 2 janvier 1904, a un numéro de série de 1, etc.

Vous pouvez voir quel système de date est utilisé dans un classeur en suivant ces étapes:

(Voir la figure 3.)

Si la case à cocher est activée à l’étape 4, votre classeur utilise le système de date 1904. S’il n’est pas sélectionné, c’est que vous utilisez le système de date 1900.

Qu’est-ce que cela a à voir avec la question initiale de Guido sur la saisie de temps négatifs? Beaucoup, en fait. Vous voyez, une valeur de temps, pour Excel, n’est rien de plus qu’une partie d’une journée. Notez que les exemples fournis jusqu’à présent impliquent des dates stockées sous forme de numéros de série entiers, dans ce cas 43907.

Si vous entrez une date avec une heure dans une cellule, telle que « 17/03/20 18h00 »

(encore une fois sans les guillemets, il est stocké en interne sous la forme de la partie du numéro de série à droite de la virgule décimale. (Voir Figure 4.)

Figure 4 . Voir comment Excel stocke une date et une heure.

La partie fractionnaire stockée avec le numéro de série (0,75) signifie que l’heure que vous avez entrée (18 h 00) est de 75% au cours de la journée . Les heures — la partie décimale — peuvent aller de 0,0 (minuit) à .999988426 (23 h 59 min 59 s).

En raison de ce système de stockage des dates fois en interne sous forme de numéros de série, il n’y a pas de « temps négatif » dans le monde Excel.

Il n’y a aucun moyen d’analyser et de stocker une telle valeur, donc Excel donne un message d’erreur lorsque vous essayez. Vous peut faire des « calculs de date » et soustraire une heure d’une valeur d’heure déjà stockée dans une cellule, mais tout ce que vous faites est d’ajuster le numéro de série de la date et de l’heure. Par exemple, si vous avez le numéro de série 43907.75 stocké dans une cellule et que vous s Retirez-en un « temps » qui donnerait un résultat négatif, tout ce que vous avez fait est de passer à la veille. (Voir la figure 5.)

Figure 5. Soustraction d’une date et d’une heure.

Notez que dans la cellule B6, une heure, sans date d’accompagnement, a été entrée (22h00). Cela a été analysé par Excel comme un numéro de série avec une «date zéro» comme indiqué dans la cellule C6. Si vous essayez de soustraire une heure de ce numéro de série qui donne un résultat négatif, Excel ne peut pas afficher le résultat sous forme d’heure. (Voir la figure 6.)

Figure 6. Vous ne pouvez pas soustraire trop loin avec les temps.

Si vous passez le pointeur de la souris sur les marques de hachage affichées dans la cellule B9, vous verrez le message d’erreur: «Les dates et heures négatives ou trop grandes s’affichent sous la forme .» (Le libellé exact du message d’erreur peut varier d’une version à l’autre d’Excel.)

Vous obtiendrez le même résultat si vous essayez d’arriver à des moments négatifs par d’autres moyens. Par exemple, si vous entrez une valeur de temps dans une cellule, puis, dans une autre cellule, utilisez une formule pour multiplier le temps par -1.

Lorsque vous essayez de mettre en forme le résultat sous forme d’heure, vous obtenez à nouveau les marques de hachage. Excel ne peut tout simplement pas afficher des temps négatifs car ils ne peuvent pas exister en ce qui concerne le programme.

Alors, que doit faire Guido? S’il a réellement besoin de travailler avec des temps négatifs, il ne peut pas faire grand-chose pour les raisons déjà expliquées.

(Plus à ce sujet dans un instant, cependant.) S’il veut simplement soustraire des temps, c’est une autre histoire. Le fait que Guido essaie d’entrer quelque chose comme -02: 00 indique qu’il travaille avec des durées d’événements, pas avec des « heures d’horloge » réelles. (N’oubliez pas qu’Excel fonctionne avec les heures d’horloge pour effectuer son analyse, et -02: 00 n’est pas une heure d’horloge – vous ne pouvez pas dire à quelqu’un que vous les rencontrerez à -02: 00.)

Pour gérer la durée des événements, vous pouvez simplement laisser Excel faire le calcul pour vous. Par exemple, une heure de début pourrait être placée dans la cellule B4 et un certain nombre d’heures que Guido souhaite en soustraire (2) pourrait être placé dans la cellule C4. Dans la cellule D4, il pourrait alors utiliser une formule pour calculer le résultat:

La division du nombre d’heures par 24 donne la valeur décimale correcte qui est utilisée en interne par Excel dans ses numéros de série. Bien sûr, si le résultat de la formule aboutit à un numéro de série négatif, vous obtiendrez les marques de hachage lorsque vous essayez de formater le résultat en une heure. La seule solution à cela est de s’assurer que ce qui est stocké dans la cellule B4 inclut une date. Vous pouvez formater la cellule B4 pour afficher uniquement l’heure, mais l’inclusion de la date empêchera le résultat d’être une erreur. Vous pouvez en savoir plus sur l’utilisation des temps écoulés négatifs en jetant un œil à ce ExcelTip:

Une fois que tout est dit et fait, il existe une façon de travailler avec des temps négatifs réels: passez au système de date 1904, comme indiqué précédemment.

Par exemple, regardez les résultats des soustractions lorsque vous utilisez le système de date 1904. (Voir la figure 7.)

Figure 7. Le système de date 1904 traitera les heures négatives.

Cette approche fonctionnera très bien pour les classeurs individuels sur votre propre système, mais peut ne pas être aussi bonne lorsque d’autres utilisent votre classeur. S’appuyant sur des classeurs utilisant des systèmes de date différents (certains 1900 et d’autres 1904)

peut être une cause de problèmes potentiels lorsque vous (ou d’autres) copiez des informations d’un classeur à un autre. Cette question a été discutée dans un autre ExcelTip:

Lorsque vous utilisez le système de date 1904, vous rencontrerez toujours des problèmes lors de la saisie d’heures négatives, mais les résultats des formules peuvent afficher des heures négatives sans problème.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13356) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.