Bienvenue sur Exceller avec la Bureautique ! Si vous êtes nouveau ici, vous voudrez sans doute lire mon livre PDF qui vous aide à gagner du temps avec Excel cliquez ici pour télécharger le Livre en PDF gratuitement !.
Comme mentionné dans mon article expliquant comment calculer un âge avec Excel, la gestion du temps sur cette application peut sembler peu compréhensible. Or, une fois qu’on SAIT, additionner et soustraire des heures ou des minutes avec Excel devient un jeu d’enfants !
Excel ne sait pas additionner les heures !
Si vous êtes ici, c’est que vous en avez vous-même fait l’expérience, et dans le doute, faites le test suivant :
- Saisissez 12:00 dans une cellule
- Saisissez 12:00 dans une autre cellule
- Réalisez l’addition des deux cellules dans une troisième
- Observez le résultat : 00:00 !
Premier pas pour résoudre cette étrangeté : je vous invite à changer le format de présentation des données de votre cellule.
- Dans le ruban Accueil, bloc Nombre, sélectionnez le menu déroulant « Autres formats numériques… »
- Sur l’onglet « Nombre », sélectionnez « Personnalisée »
- Dans la zone de saisie « Type » saisissez [h]:mm
Avec cette configuration, on retrouve le résultat attendu soit : 24:00.
Sur le principe, on pourrait s’arrêter là, mais je ne trouve pas cela très convaincant… Et vous ?
Comment Excel gère les heures ? Comprendre pour additionner, soustraire des heures, des minutes à souhait !
Je vous l’ai déjà dit et j’insiste, Excel ne comprend pas les heures ! Tout est nombre ou texte pour Excel. Le reste n’est que mise en forme pour vos yeux.
D’ailleurs, vous allez découvrir que le 0 janvier 1900 existe chez Microsoft !
Je vous propose de commencer par un petit exercice pratique.
Comprendre et apprendre en pratiquant – Ajouter des heures sur Excel
Préparer votre classeur
- Ouvrez un nouveau classeur vide
- Saisissez dans la cellule de votre choix (D2 dans mon cas) une heure, 13:22 par exemple
- Dans la cellule de droite, saisissez =votre_référence (=D2 dans mon cas)
- Dans la cellule encore à droite, répétez la même chose avec la même référence (=D2 dans mon cas)
- Sélectionnez ces trois cellules et étendez-les sur trois cellules vers le bas (cf. Le curseur Excel : Sous toutes ses formes et fonctions)
Vous devriez vous retrouver avec cet affichage :
Ensuite, dans la cellule sous chaque colonne, calculons la somme des lignes du dessus.
=SOMME(D2:D5) pour la première colonne
Il ne vous reste qu’à étendre cette formule sur la droite pour que celle-ci s’adapte aux colonnes d’à côté. Regardez cette partie de l’article pour apprendre à étendre une fonction avec le curseur.
Étudier les données
Je vous propose maintenant de changer le format des données de la 2e colonne (Colonne E pour moi) ainsi que de celui de la 3e colonne (Colonne F pour moi).
Vous vous souvenez, je vous ai proposé de changer le format de la cellule au début de cet article. Vous allez faire la même opération, mais cette fois, voici le format que vous allez saisir pour la 2e colonne :
jj.mm.aaaa hh:mm:ss
Et pour la dernière colonne, mettez simplement « Standard » (c’est le premier de la liste).
Vous devriez avoir l’affichage suivant :
Avez-vous remarqué la dernière ligne, celle des totaux ?
| 11:28 | 02.01.1900 11:28:00 | 2,47777778 |
La même donnée sous trois formes différentes !
En effet, Excel ne fait que présenter les données sous forme de date ou d’heure. Lorsque vous avez saisi votre première valeur 13:22, vous n’avez pas renseigné de date particulière, celle-ci a été fixée automatiquement au « 0 janvier 1900 ».
Vous avez bien lu : Le jour zéro existe pour Excel et c’est le 0 janvier 1900 !
Comprendre pour maîtriser la manipulation des heures, des minutes ou des dates avec Excel
Le véritable zéro a été inventé par les indiens vers le IIIe siècle après J.-C.
https://www.lumni.fr/article/mathematiques-histoire-du-zero
Ici, Microsoft a fait le choix technique d’implémenter les dates de cette manière. Je ne me permettrai pas de commentaires tant ce choix a des impacts que je ne peux évaluer.
Une date n’est finalement qu’une apparence visuelle. Au passage, je vous informe que dans le monde Excel, les dates, en plus d’avoir un début, ont une fin !
Eh oui, la fin du monde datée se situe au 31 décembre 9999 à 23:59:59.
Maintenant, que faut-il savoir pour VOUS et MAINTENANT pour vos opérations avec les heures ?
Même si vous ne saisissez qu’une donnée sous la forme d’une heure, Excel va considérer cette information comme étant une date. Et, en plus, Excel traitera cette information comme étant une valeur numérique.
Les nombres entiers correspondent aux jours.
Une heure vaut numériquement 1 divisé par 24.
Une minute vaut 1 divisé par 24 divisé par 60. Pour la seconde, il faudra diviser encore par 60 soit 1 divisé par (24*60*60).
Lorsque vous faites n’importe quelle opération (additionner des heures, soustraire des minutes, etc.), Excel réalise l’opération sur les valeurs numériques puis convertit le résultat en la date correspondante.
Comme Excel va respecter au maximum le format des cellules qui composent votre opération, vous aurez des résultats comme l’exemple du début :
La somme des différentes heures donne le résultat de 11:28 ! Vous l’avez peut-être déjà repéré… Lorsque vous adaptez le format de la cellule du total au format jj.mm.aaaa hh:mm:ss vous obtenez : 02.01.1900 11:32.
C’est à dire, 2×24 + 11:32 = 59:32.
Pour obtenir cet affichage, il faut savoir gérer les options de mise en forme des cellules. Le meilleur format pour le cas présent est [h]:mm.
Quelques dernières précautions pour vos additions ou soustraction d’heures avec Excel
Vous y êtes presque, vous avez déjà fait un grand pas dans votre connaissance d’Excel. Maintenant, c’est la dernière ligne droite, c’est à partir d’ICI que vous allez plus loin. Restez bien jusqu’au bout !
Gérer les erreurs d’affichage
Excel va tant qu’il peut essayer d’afficher les données dans le format de votre choix. Or, si les données ne sont pas affichables, Excel remplira la cellule avec des #.
Cela ne veut pas dire que le calcul ne sera pas fait, ni même que cette cellule ne peut pas être utilisée comme référence pour un autre calcul…
Voyons cela à l’aide d’une illustration !
Repartons de notre heure de 13:22 en D2.
Si en C2, vous saisissez =D2-1, savez-vous quelle opération vous venez de faire ?
Vous ne venez pas de soustraire une heure à 13:22, mais un jour ! Soit 24h !
Ainsi, arrive ce qui devait arriver :
13:22 est interprété par Excel comme étant le 0 janvier 1900, et maintenant vous essayer de soustraire un jour à cette date ! Excel ne sait pas afficher cette information sous forme de date… Il faudrait normalement soustraire (1/24) pour que cela représente une heure.
Cependant, Excel a en mémoire la valeur numérique de 13:22 (allez voir en F2 de votre tableau) et peut heureusement encore soustraire 1 à cette valeur.
En B2, vous pouvez maintenant saisir =C2+2. Arrivez-vous à deviner le résultat affiché ?
Malgré l’erreur d’affichage, Excel a bien la valeur en mémoire. En ajoutant 2, vous revenez dans une plage de valeur qu’Excel sait afficher sous forme de date. Et comme vous n’avez pas affiché le jour, chaque unité que vous ajouterez fera avancer le jour, mais pas l’heure !
Astuce pour réaliser sereinement toutes vos opérations mathématiques avec les heures
Êtes-vous condamné à ajouter ou soustraire des multiples de 1/24 dans tous vos calculs ? Eh bien non ! Vous pouvez tout simplement utiliser l’écriture AVEC LES GUILLEMETS « 1:00 » dans vos calculs.
= « 1:00 » * 5
Faites l’essai par vous-même !
Excel comprend alors que vous manipulez des heures et adaptera la valeur numérique correspondante !
Il ne vous restera qu’à adapter le format d’affichage de la cellule en heure ou date selon votre préférence.
Évidemment, cela fonctionne également pour les additions ou les soustractions directement dans les formules.
Et les minutes dans tout cela ?
Maintenant que vous connaissez l’astuce pour ne jamais vous tromper dans les opérations avec les heures, sachez que vous pouvez tout à fait l’étendre pour les minutes ou les secondes !
Dans vos calculs, vous pouvez maintenant saisir = »00:30:15″ * 8 et obtenir exactement le résultat escompté ! Le seul point d’attention à conserver en tête est le format d’affichage de votre cellule, soit « Heure », soit « Personnalisé » avec le type [h]:mm:ss.
Sinon, il faut saisir 1/(24*60) dans toutes vos formules, et pour les secondes 1/(24*60*60). C’est plus lourd et plus compliqué que « 00:01:00 » ou « 00:00:01 ».
Pour aller plus loin, avec les dates cette fois-ci !
Excel est plein de secrets, l’un d’entre eux est cette fonction cachée, oui CACHÉE, qui se nomme DATEDIF(). Cette fonction permet de calculer la différence entre deux dates en nombre de jours, mois ou années.
Le plus simple est d’aller voir par vous-même sur comment calculer un âge avec Excel, vous en apprendrez beaucoup sur la gestion des dates sur Excel.
Vous reste-t-il des questions pour additionner ou soustraire des heures avec Excel ? Avez-vous apprécié cet article ?
Dans tous les cas, utilisez la partie commentaire de cet article pour tous vos échanges avec moi. Votre question et ma réponse pourront servir les autres visiteurs !
Et si vous avez apprécié cet article, alors c’est moi que cela servira de savoir qu’il vous a plu !
Je compte sur vous !
Merci d'avoir lu cet article
Prenez quelques secondes pour gagner quelques heures !
Si vous êtes arrivé ici, c'est que vous apprécierez forcément de recevoir gratuitement le livre PDF des "12 fonctions Excel à maîtriser absolument !". Il vous permettra d'atteindre des sommets dans l'utilisation efficace d'Excel
- Devenez l'expert Excel dont on a toujours besoin
- Gagnez en efficacité en sachant choisir la bonne fonction
- Découvrez les dernières fonctions d'Excel
super, merci
C’est une excellente idée ! Je vais mettre cela dans ma liste des bonnes idées à mettre en place !
Merci pour ces explications.
Si vous pouviez faire aussi des vidéos ça serait très intéressant.
L’essentiel est de savoir que dans Excel, tout est nombre ou texte. Les dates ne sont qu’un affichage personnalisé et idem pour les heures. Et c’est effectivement le genre d’information qu’il faut avoir avant… car sinon, c’est à s’arracher les cheveux !
Si j’étais taquin… je dirais que 5% c’est déjà pas mal… 🙂
Simple et clair pour une astuce qui nous servira forcément un jour !
Encore une astuce que je ne connaissais pas ! Je savais que c’était possible mais je ne l’utilisais pas (préférant bricoler une formule maison). Ahah ! plus j’apprends sur Excel et plus je l’aime. Pourtant c’est un outil vraiment « simpliste » en apparence et pourtant je ne dois l’utiliser qu’à 5% de son potentiel. Merci Charles