Excel : appliquer le format numérique maintenant et effectuer des opérations de calcul
Bienvenue dans ce tutoriel Excel.
Travailler avec le format d’heure | Calculer les heures et les minutes dans Excel
Si des valeurs formatées en temps (format TIME) ont été saisies dans la feuille Excel, il peut être nécessaire de mettre en place certaines opérations de calcul telles que la somme (heures de travail); différence (heure de sortie moins heure d’entrée) ou multiplier un certain nombre d’heures par un salaire horaire évidemment exprimé au format monétaire.
Ce didacticiel vidéo présente la manière correcte de saisir le format de l’heure, de le formater et de le transformer en un format de nombre décimal. Les principales opérations de calcul pouvant être effectuées sur les heures sont également illustrées. Le contenu du tutoriel est enfin résumé dans une courte vidéo sur le format horaire d’Excel.
Le format Excel HEURE
Les caractéristiques du format ne rendent désormais pas les opérations de calcul dans Excel particulièrement faciles.
Afin de prendre en charge les opérations de calcul sur le format HEURE, Excel transforme et enregistre les heures dans un nombre allant de 0 à 1 pour la durée de 24 heures. Ce nombre représente le résultat de la division du temps lui-même par le total des heures de la journée soit 24. La valeur numérique d’une heure est donc représentée par la fraction d’un entier qui équivaut à un vingt-quatrième de la journée, soit 1/ 24 = 0 , 041666
Le numéro de “série” du format Excel TIME
En réalité, ce n’est pas un vrai numéro de série comme c’est le cas pour les dates.
Dans Excel, le format de l’heure est stocké dans un nombre, qui varie de 0 à 1 pour une durée de 24 heures d’une journée.
Comme prévu au point précédent, le nombre représente le résultat de la division du temps lui-même par le nombre total d’heures de la journée ou 24.
Par exemple, les heures 9h00 sont stockées avec le nombre 0,375 et l’heure de 21h00 sous la forme 0,875.
Quelques exemples sont présentés dans le tableau ci-dessous.
Saisissez correctement le format TIME
Pour entrer directement des informations numériques dans le format TIME, un deux-points “:” doit être utilisé pour séparer les heures, les minutes et les secondes.
Si vous tapez 6:30:00 les informations saisies seront automatiquement reconnues par Excel avec le formatage de l’heure.
Pour entrer une heure au format 12 heures au lieu du format 24 heures, tapez un espace puis un “a” ou “p” après l’heure, par exemple 7:00 p
Si vous n’ajoutez pas le “a” ou le “p”, l’heure sera saisie au format classique 24h.
Si vous ne voulez pas garder une trace des secondes, tapez simplement 1:30
Si dans l’information horaire il est nécessaire d’insérer des centièmes de seconde, ils doivent être séparés des secondes par une virgule et non par deux-points.
Exemple : en saisissant la valeur de Vingt-huit minutes vingt-cinq secondes et dix centimes, il faut la saisir comme suit : 28 : 25,10
Convertir l’heure en format décimal par une opération arithmétique
Le moyen le plus simple de convertir une valeur exprimée au format horaire en un nombre équivalent sur une base décimale consiste à multiplier la valeur par le nombre d’heures, de minutes ou de secondes d’une journée.
Heures = 24, Minutes = 1440, Secondes = 86400
Dans l’image suivante dans la colonne A de la feuille de calcul, une série d’heures est répertoriée qui, dans les colonnes suivantes B, C et D, sont transformées en leurs nombres respectifs au format décimal.
Remarques. Comme mentionné précédemment, dans Excel, le format de l’heure est stocké dans un nombre, qui varie de 0 à 1 pour une durée de 24 heures par jour.
La transformation des heures 3h30 en nombre 3,5 a cependant transformé le format des minutes 30/60 de sexagésimal à centésimal correspondant à 50/100.
Il ne faut pas oublier que les heures 3h30 correspondent au nombre 0.14583 pour Excel. Alors que le résultat final 3,5 nous donne un nombre au format décimal.
ATTENTION. Si, suite à l’opération de calcul, le résultat est renvoyé au format heure, changez simplement le formatage en Général ou Nombre.
Convertissez l’heure au format décimal avec la fonction CONVERT
Pour convertir l’heure dans un format de nombre décimal, comme alternative à l’opération arithmétique, il est possible d’appliquer la fonction CONVERT.
CONVERT est une fonction Excel qui appartient au groupe d’ingénierie, et bien qu’il s’agisse d’une fonction très intéressante, elle est peu connue et par conséquent peu appliquée dans Worksheets.
:: La fonction CONVERT d’Excel vous permet de convertir un nombre d’un système d’unités à un autre (non seulement pour le temps, mais aussi pour les distances, les poids et autres mesures physiques).
Plus précisément, la fonction a trois arguments : = CONVERT (num ; from_measure ; to_measure)
La fonction CONVERT est appliquée dans l’image ci-dessous.
Le premier argument fait référence à la valeur contenue dans la cellule A, les deux arguments suivants sont la transformation du jour “day” en heures “hr”, ou minutes “mn” selon l’objet du calcul.
Pour afficher les codes que la fonction CONVERT utilise comme arguments relatifs au temps, l’image montre un tableau récapitulatif.
:: Pour plus d’informations sur les fonctions CONVERT, reportez-vous à : Conversion d’un nombre d’un système d’unités de mesure à un autre
Comment ajouter des heures et des minutes dans Excel
Pour compter les heures, vous pouvez utiliser la fonction SOMME. Cependant, il faut faire TRÈS ATTENTION pour appliquer le bon format de nombre au résultat !!
Exemple sur la façon de résumer le format de l’heure dans Excel.
Dans la feuille de calcul ci-dessous, vous souhaitez calculer le nombre total d’heures de travail hebdomadaires.
Dans la cellule C10, la fonction suivante a été définie : = SOMME (C3 : C8)
Le résultat qu’Excel renvoie initialement à partir de la somme présente dans la cellule C10 est : 15:15 et visiblement cela ne correspond pas au bon décompte !
La raison est que le format TIME prévoit un décompte maximum jusqu’au nombre 24, et une fois les 24 premières heures totalisées, Excel repart de 0 (il ne peut donc pas rendre visible un calcul cumulé au-delà du nombre 24).
Pour rendre visible le résultat correct de la totalisation des heures de travail, il faut ouvrir l’onglet Format de cellule et dans la catégorie Heure sélectionner le format 37:30:55.
Ce formatage particulier [37:30:55] permet à Excel d’appliquer un décompte de temps indépendant de la valeur maximale de 24 typique de ce format numérique.
Dans la boîte de dialogue Format de cellule, vous pouvez prendre en compte toute la mise en forme attendue par Excel pour le format numérique MAINTENANT.
:: Conseiller. Pour ouvrir rapidement l’onglet Format de cellule, vous pouvez utiliser la combinaison CTRL + 1
Comment soustraire les HEURES (Exemple : travail entrant calcul sortant)
Une situation de calcul fréquente avec le format horaire est la différence entre les heures d’entrée et de sortie, pour calculer les heures de travail.
Il s’agit d’une opération de type arithmétique, comme la précédente, sans l’aide de Functions et illustrée dans l’image suivante.
Le tableau suppose des heures de travail interrompues le matin et l’après-midi.
La formule suivante = D3-C3 + F3-E3 a été saisie dans la cellule G3, c’est-à-dire la différence entre la sortie et l’entrée du matin plus la sortie moins l’entrée de l’après-midi.
Si les valeurs de la colonne G devaient être additionnées par la suite, comme décrit au point précédent, il est indispensable de modifier la mise en forme du calcul total en appliquant le format horaire : 37:30:55
:: Pour plus d’informations sur la construction d’un tableau avec un calcul hebdomadaire des heures de travail, voir : Calcul des heures de travail ordinaires et extraordinaires (Opérations avec le format numérique de l’heure)
Comment multiplier les HEURES par un nombre (Exemple : calculer une redevance)
Si l’on veut multiplier le nombre total d’heures par un salaire horaire, on se retrouve dans la difficulté de devoir transformer les minutes (exprimées au format sexagésimal) dans un format centésimal correspondant.
::Mise en garde. Si nous devions multiplier 22 heures et 50 minutes par un salaire horaire, il n’est pas possible de considérer l’heure comme 22,5 (50 minutes sont 50/60 et non 50/100).
De plus, comme expliqué au début de ce tutoriel, rappelez-vous qu’Excel a enregistré l’heure de 22h50 dans le nombre 0.951333 et donc l’éventuelle multiplication serait par 0.951333 !
Comme expliqué précédemment, pour transformer les minutes exprimées en soixantièmes en l’équivalent numérique exprimé en centièmes, multipliez par 24. (voir exemple et image ci-dessous)
Le résultat de cette opération rend possible la multiplication ultérieure par un salaire horaire.
Exemple de Multiplication d’un total d’heures par un taux horaire
Prenons l’exemple de 12h50, si ce nombre est multiplié par 24, nous obtenons 12,83.
La décimale 83 représente la transformation des 50 soixantièmes du format horaire en rapport équivalent sur une base centésimale.
Une fois cette opération effectuée, il est possible de multiplier la valeur obtenue par un chiffre numérique, par exemple au format monétaire.
Le calcul d’un salaire en multipliant un total d’heures par un salaire horaire peut être développé en une seule formule. Comme le montre l’image ci-dessous, il est possible d’insérer l’opération suivante dans la cellule F3 = B3 * 24 * D3
En résumé. La difficulté de traiter les opérations sur les heures et les minutes est due au fait que l’heure est divisée en 60 minutes, donc, s’il faut multiplier une valeur d’heure par un nombre, il faut transformer la valeur d’heure exprimée sur une base sexagésimale dans les centimes correspondants.
Combinez le format HEURE avec le format DATE
Excel vous permet de combiner ces deux formats numériques pour permettre des opérations de calcul plus complexes, c’est-à-dire combien d’heures se sont écoulées depuis une heure précise à une date.
L’élément qui supporte ce calcul est lié au numéro d’ordre progressif des dates auquel s’ajoute une partie décimale allant de 0 à 1 relative au format de l’heure.
Exemple. Dans l’image ci-dessous, il est possible de vérifier la combinaison du numéro de série de la date du 22/12/2020 avec le nombre 0,5 correspondant à 12h00.
Le numéro de série 44186 représente la date du 21/12/2020, le numéro 44186,5 représente à la place une heure précise (12:00) toujours du 21/12/2020
:: ATTENTION. Les dates sont stockées dans Excel via la numérotation en série.
Excel utilise le système de date 1900, c’est-à-dire que le chiffre 1 correspond à la date du 01/01/1900 pour Excel et ainsi de suite pour toutes les dates ultérieures, donc le chiffre 2 correspond au 01/02/1900 jusqu’à nos jours où le le numéro 44.186 correspond au 21 décembre 2020.
Autres articles
