Calculer l'addition et la soustraction de temps (décalage horaire, temps de travail) dans Excel

en raison deLes dates et les heures sont stockées sous forme de nombres dans le backend d'Excel, afin que vous puissiez facilement utiliser des opérations arithmétiques simples et des formules sur les valeurs de date et d'heure.

Par exemple, vous pouvez ajouter et soustraire deux valeurs de temps ou de date différentes, ou vous pouvez calculer la différence de temps entre deux dates/heures données.

Dans ce tutoriel, je vais vous montrer comment Effectuer des calculs en utilisant le temps dans Excelplusieurs méthodes (telles que le calcul du décalage horaire, l'ajout et la soustraction de temps, l'affichage de l'heure dans différents formats et le calcul de la somme des valeurs temporelles).

Comment Excel gère-t-il les dates et les heures ?

Comme je l'ai mentionné, les dates et les heures sont stockées sous forme de nombres dans des cellules dans Excel.L'entier représente la journée entière, la partie fractionnaire du nombre représente la partie de la journée (peut être convertie en heures, minutes et secondes)

Par exemple, une valeur de 1 représente le 1900er janvier 1 dans Excel, qui est le point de départ pour qu'Excel commence à considérer les dates.

Donc 2 signifie le 1990 janvier 1, 2 signifie le 3 janvier 1900, et ainsi de suite, 1 signifie le 3er janvier 44197.

Remarque : Excel pour Windows et Excel pour Mac suivent des dates de début différentes.1 dans Excel pour Windows signifie le 1900er janvier 1, 1 dans Excel pour Mac signifie le 1er janvier 1904

Si ces nombres comportent des chiffres après la virgule décimale, Excel traite ces nombres comme faisant partie d'un jour et peut les convertir en heures, minutes et secondes.

Par exemple, 44197.5 signifie le 2021er janvier 1 à 1:12:00.

Donc, si vous travaillez avec des valeurs temporelles dans Excel, vous travaillez essentiellement avec la partie fractionnaire du nombre.

Excel vous offre la possibilité de convertir des parties fractionnaires en différents formats, tels que des heures uniquement, des minutes uniquement, des secondes uniquement ou une combinaison d'heures, de minutes et de secondes.

Maintenant que vous comprenez comment l'heure est stockée dans Excel, examinons quelques exemples de calcul de la différence de temps entre deux dates ou heures différentes dans Excel.

La formule pour calculer le décalage horaire entre deux heures

Dans de nombreux cas, il suffit de connaître le temps total écoulé entre deux valeurs de temps (par exemple, dans le cas d'un horaire avec In-time et Out-time).

La méthode que vous choisissez dépend de la façon dont l'heure est mentionnée dans la cellule et de la façon dont vous souhaitez que le résultat soit formaté.

Regardons quelques exemples

Soustraction simple pour calculer la différence de temps dans Excel

Étant donné que les heures sont stockées sous forme de nombres dans Excel, pour trouver la différence entre 2 valeurs d'heure, vous pouvez facilement soustraire l'heure de début de l'heure de fin.

heure de fin - heure de début

Le résultat de la soustraction sera également une valeur décimale représentant le temps écoulé entre les deux valeurs de temps.

Voici un exemple où j'ai l'heure de début et l'heure de fin et j'ai calculé la différence de temps par simple soustraction.

Il est possible que vos résultats soient affichés au format horaire (plutôt que des valeurs décimales ou heures/minutes).Dans l'exemple ci-dessus, le résultat dans la cellule C2 indique 09h30 au lieu de 9.5.

C'est bien parce qu'Excel essaiera de copier la mise en forme des colonnes adjacentes.

Pour le convertir en décimal, changez le format de la cellule en "Général" (cette option se trouve dans l'onglet "Accueil" du groupe "Nombres")

Après avoir obtenu le résultat, vous pouvez le formater de différentes manières.Par exemple, vous pouvez afficher des valeurs en heures uniquement, en minutes uniquement ou en une combinaison d'heures, de minutes et de secondes.

Voici les différents formats que vous pouvez utiliser :

format	ce qu'il peut faire
hh	Afficher uniquement le nombre d'heures écoulées entre deux dates
mm	Afficher les heures sous forme de deux chiffres (par exemple 04 ou 12)
hh: mm	Affiche les heures et les minutes écoulées entre deux dates, par exemple 10:20
hh: mm: ss	Affiche les heures, les minutes et les secondes écoulées entre deux dates, par exemple 10:20:36

Si vous vous demandez où et comment appliquer ces formats de date personnalisés, procédez comme suit :

1. Sélectionnez les cellules auxquelles appliquer le format de date
2. Maintenez Control et appuyez sur 1 (Commande + 1 si vous utilisez un Mac)
3. Dans la boîte de dialogue Format de cellule qui s'ouvre, cliquez sur l'onglet Nombres (s'il n'est pas déjà sélectionné)
4. Dans le volet de gauche, cliquez sur Personnaliser
5. Entrez le code de format souhaité dans le champ Type (j'utilise hh:mm:ss dans cet exemple)
6. Cliquez sur OK

Les étapes ci-dessus changeront le format et vous montreront la valeur en fonction du format.

Notez que la mise en forme personnalisée des nombres ne modifie pas la valeur dans la cellule.Cela change simplement la façon dont la valeur est affichée.Je peux donc choisir de n'afficher que la valeur de l'heure dans la cellule et elle a toujours la valeur d'origine.

CONSEIL DE PRO : Si le nombre total d'heures dépasse 24 heures, utilisez plutôt le format numérique personnalisé suivant : [hh]:mm:ss

Calculer le décalage horaire en heures, minutes ou secondes

Lorsque vous soustrayez des valeurs de temps, Excel renvoie un nombre décimal qui représente la différence de temps résultante.

Étant donné que chaque nombre entier représente un jour, la partie fractionnaire du nombre représentera la majeure partie de la journée et peut être facilement convertie en heures, minutes ou secondes.

Calculer le décalage horaire en heures

Supposons que vous disposiez d'un jeu de données comme indiqué ci-dessous et que vous souhaitiez calculer le nombre d'heures entre deux valeurs temporelles

La formule ci-dessous vous donnera le décalage horaire en heures :

= (B2-A2) * 24

La formule ci-dessus vous donnera le nombre total d'heures écoulées entre deux valeurs de temps.

Parfois, Excel essaiera de vous aider et vous donnera également les résultats dans un format horaire (illustré ci-dessous).

Vous pouvez facilement le convertir au format numérique en cliquant sur l'onglet Accueil et en sélectionnant Nombre comme format dans le groupe Nombre.

Si vous souhaitez simplement renvoyer le nombre total d'heures écoulées entre deux temps (sans partie fractionnaire), utilisez cette formule :

=INT((B2-A2)*24)

Remarque : Cette formule ne fonctionne que si les deux heures correspondent au même jour.Cette formule donnera des résultats erronés si les dates changent (l'une des valeurs de temps est une autre date et la seconde est une autre date).Lorsque la date change plus tard dans ce didacticiel, consultez la section où j'introduis la formule de calcul du décalage horaire.

Calculer le décalage horaire en minutes

Pour calculer le décalage horaire en minutes, vous devez multiplier la valeur obtenue par le nombre total de minutes d'une journée (c'est-à-dire 1440 ou 24*60).

Supposons que vous disposiez d'un ensemble de données comme indiqué ci-dessous et que vous souhaitiez calculer le nombre total de minutes écoulées entre la date de début et la date de fin.

Voici la formule qui peut faire cela :

=(B2-A2)*24*60

Calculer la différence de temps en secondes

Pour calculer le décalage horaire en secondes, vous devez multiplier la valeur résultante par le nombre total de secondes dans une journée (c'est-à-dire 24*60*60 ou 86400).

Supposons que vous disposiez d'un ensemble de données comme ci-dessous et que vous souhaitiez compter le nombre total de secondes écoulées entre la date de début et la date de fin.

Voici la formule qui peut faire cela :

=(B2-A2)*24*60*60

Calculer le décalage horaire à l'aide de la fonction TEXT

Un autre moyen simple d'obtenir rapidement le décalage horaire sans se soucier du changement de format consiste à utiliser la fonction TEXTE.

La fonction TEXTE vous permet de spécifier le formatage dans les formules.

=TEXTE(date_fin-date_début, format)

Le premier paramètre est le calcul que vous souhaitez effectuer et le second paramètre est le format dans lequel vous souhaitez afficher les résultats du calcul.

Supposons que vous disposiez d'un ensemble de données comme indiqué ci-dessous et que vous souhaitiez calculer la différence de temps entre deux heures.

Voici quelques formules qui vous donneront les résultats sous différents formats

Afficher uniquement les horaires :

=TEXTE(B2-A2,"hh")

La formule ci-dessus ne vous donnera que le résultat du nombre d'heures écoulées entre deux valeurs de temps.Si votre résultat est de 9 heures 30 minutes, il n'affichera toujours que 9.

Afficher le nombre total de minutes

=TEXTE(B2-A2,"[mm]")

Afficher le nombre total de secondes

=TEXTE(B2-A2,"[ss]")

Afficher les heures et les minutes

=TEXTE(B2-A2,"[hh]:mm")

Affichage heures, minutes et secondes

=TEXTE(B2-A2,"hh:mm:ss")

Si vous vous demandez quelle est la différence entre hh et [hh] dans le format (ou mm et [mm]), lorsque vous utilisez des crochets cela vous donne le nombre total d'heures entre les deux dates, même si l'heure Valeurs supérieur à 24.Donc, si vous soustrayez deux valeurs de date qui diffèrent de plus de 24 heures, l'utilisation de [hh] vous donnera le nombre total d'heures, tandis que hh ne vous donnera que le nombre d'heures écoulées à la date de fin.

Obtenir le décalage horaire dans une unité (heures/minutes), en ignorant les autres

Si vous souhaitez simplement calculer le décalage horaire entre deux valeurs horaires en heures ou en minutes ou en secondes, vous pouvez utiliser les fonctions dédiées HEURE, MINUTE ou SECONDE.

Chacune de ces fonctions prend un paramètre, la valeur de temps, et renvoie l'unité de temps spécifiée.

Supposons que vous disposiez d'un ensemble de données comme indiqué ci-dessous et que vous souhaitiez calculer le nombre total d'heures et de secondes qui se sont écoulées entre ces deux heures.

Voici la formule pour le faire :

Calculer le nombre d'heures écoulées entre deux temps

=HEURE(B2-A2)

Calculer les minutes en fonction du résultat de la valeur de temps (hors heures complétées)

=MINUTES(B2-A2)

Calculer les secondes à partir du résultat de la valeur temporelle (à l'exclusion des heures et des minutes terminées)

=DEUXIÈME(B2-A2)

Quelques éléments à savoir lors de l'utilisation de ces formules HEURES, MINUTES et SECONDES :

• La différence entre l'heure de fin et l'heure de début ne peut pas être négative (c'est généralement le cas lorsque la date change).Dans ce cas, ces formules renverront #NUM !Erreur
• Ces formules utilisent uniquement la partie heure (et ignorent la partie jours) de la valeur de temps résultante.Donc, si la différence entre l'heure de fin et l'heure de début est de 2 jours, 10 heures, 32 minutes et 44 secondes, la formule HEURE donnera 10, la formule MINUTE donnera 32 et la DEUXIÈME formule donnera 44

Calculer le temps écoulé maintenant (depuis l'heure de début)

Si vous souhaitez calculer le temps total écoulé entre l'heure de début et l'heure actuelle, vous pouvez utiliser la formule MAINTENANT au lieu de l'heure de fin.

La fonction MAINTENANT renvoie la date et l'heure actuelles dans la cellule dans laquelle elle est utilisée.C'est l'une des fonctions qui n'accepte aucun argument d'entrée.

Ainsi, si vous souhaitez calculer le temps total écoulé entre l'heure de début et l'heure actuelle, vous pouvez utiliser la formule suivante :

=MAINTENANT() - heure de début

Vous trouverez ci-dessous un exemple où j'ai l'heure de début dans la colonne A et l'heure jusqu'à présent dans la colonne B.

Si la différence de temps entre la date et l'heure de début et l'heure actuelle est supérieure à 24 heures, vous pouvez formater le résultat pour afficher la partie date et heure.

Vous pouvez le faire avec la formule TEXT suivante :

=TEXTE(MAINTENANT()-A2,"jj hh:ss:mm")

Vous pouvez également obtenir le même résultat en modifiant le format personnalisé de la cellule (comme décrit précédemment dans ce didacticiel) afin qu'il affiche la partie date et heure.

Si votre heure de début n'a que la partie heure, Excel la traite comme une heure du 1990er janvier 1.

Dans ce cas, si vous utilisez la fonction MAINTENANT pour calculer le temps écoulé jusqu'à maintenant, cela vous donnera un résultat erroné (car la valeur résultante contiendra également le nombre total de jours écoulés depuis le 1990er janvier 1).

Dans ce cas, vous pouvez utiliser la formule suivante :

=MAINTENANT()-INT(MAINTENANT())-A2

La formule ci-dessus utilise la fonction INT pour supprimer la partie jours de la valeur renvoyée par la fonction now, qui est ensuite utilisée pour calculer le décalage horaire.

Notez que NOW est une fonction volatile qui se met à jour lorsque la feuille change, mais pas en temps réel

Calculer l'heure lorsque la date change (calculer et afficher l'heure négative dans Excel)

Les méthodes décrites jusqu'à présent fonctionnent bien si votre heure de fin est postérieure à l'heure de début.

Toutefois, le problème se produit lorsque votre heure de fin est inférieure à votre heure de début.Cela se produit généralement lorsque vous remplissez une feuille de temps et que vous n'entrez que l'heure et non la date et l'heure complètes.

Dans ce cas, si vous travaillez de nuit et que la date change, votre heure de fin peut être antérieure à votre heure de début.

Par exemple, si vous commencez à travailler à 6h00 et terminez le travail et faites une pause à 9h00.

Si vous utilisiez uniquement des valeurs de temps, la soustraction de l'heure de début de l'heure de fin donnerait une valeur négative de 9 heures (9 - 18).

Excel ne peut pas gérer les valeurs de temps négatives (et, d'ailleurs, les humains, à moins que vous ne puissiez voyager dans le temps)

Dans ce cas, vous avez besoin d'un moyen de déterminer que la date a changé et de calculer en conséquence.

Heureusement, il existe une solution de contournement très simple.

Supposons que vous ayez un ensemble de données comme ci-dessous, où j'ai l'heure de début et l'heure de fin.

Comme vous le remarquerez, parfois l'heure de début est le soir et l'heure de fin est le matin (ce qui indique qu'il s'agissait d'un quart de nuit et que le jour avait changé).

Si j'utilise la formule ci-dessous pour calculer le décalage horaire, elle affiche un signe dièse (surligné en jaune dans l'image ci-dessous) dans les cellules où le résultat est une valeur négative.

= B2-A2

Il s'agit d'une formule IF, si la valeur de la différence de temps est négative, si elle est négative, elle renvoie le résultat correct

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

Bien que cela fonctionne bien dans la plupart des cas, cela ne suffit toujours pas si les heures de début et de fin sont séparées de plus de 24 heures.Par exemple, quelqu'un se connecte à 1h9 le jour 00 et se déconnecte à 2h11 le jour 00.

Comme il s'agit de plus de 24 heures, il n'y a aucun moyen de savoir si la personne arrête après 2 heures ou 26 heures.

Bien que la meilleure façon de résoudre ce problème soit de s'assurer que l'entrée inclut la date et l'heure, si c'est juste l'heure avec laquelle vous travaillez, la formule ci-dessus devrait suffire la plupart du temps (étant donné qu'il est peu probable que quelqu'un travaille plus de 24 heures)

Ajouter et soustraire du temps dans Excel

Jusqu'à présent, nous avons vu des exemples d'heures de début et de fin, nous devons trouver le décalage horaire.

Excel vous permet également d'ajouter ou de soustraire facilement des valeurs d'heure fixes à partir de valeurs de date et d'heure existantes.

Par exemple, supposons que vous ayez une liste de tâches en file d'attente, où chaque tâche prend un temps spécifié, et que vous vouliez savoir quand chaque tâche se termine.

Dans ce cas, vous pouvez facilement ajouter le temps que prendra chaque tâche à l'heure de début pour voir quand la tâche devrait être terminée.

Étant donné qu'Excel stocke les valeurs de date et d'heure sous forme de nombres, vous devez vous assurer que l'heure que vous essayez d'ajouter est dans un format qu'Excel suit déjà.

Par exemple, si vous ajoutez 1 à une date dans Excel, cela vous donnera la prochaine date.En effet, 1 représente une journée complète (équivalent à 24 heures) dans Excel.

Donc, si vous voulez ajouter 1 heure à une valeur de temps existante, vous ne pouvez pas continuer et simplement lui ajouter 1.Vous devez vous assurer de convertir la valeur de l'heure en une partie fractionnaire représentant une heure.Il en va de même pour l'ajout de minutes et de secondes.

Utiliser la fonction heure

Les fonctions de temps dans Excel prennent des valeurs d'heure, de minute et de seconde et les convertissent en un nombre décimal représentant l'heure.

Par exemple, si je voulais ajouter 4 heures au temps existant, je pourrais utiliser la formule suivante :

= heure de début + HEURE(4,0,0)

Ceci est utile si vous connaissez les heures, les minutes et les secondes à ajouter à une heure existante et que vous pouvez simplement utiliser la fonction TIME sans vous soucier de convertir correctement l'heure en valeur décimale.

Notez également que la fonction TIME ne prend en compte que la partie entière des valeurs d'heures, de minutes et de secondes que vous entrez.Par exemple, si j'utilise 5.5 heures dans la fonction TIME, cela ajoute simplement 5 heures et ignore la partie fractionnaire.

Notez également que la fonction TIME ne peut ajouter que des valeurs datant de moins de 24 heures.Cela vous donnera un résultat incorrect si votre valeur d'heure dépasse 24.

Il en va de même pour les parties minutes et secondes, la fonction ne prendra en compte que les valeurs inférieures à 60 minutes et 60 secondes

Tout comme j'ai utilisé la fonction TIME pour ajouter du temps, vous pouvez également soustraire du temps.Changez simplement le signe + dans la formule ci-dessus en signe moins

Utiliser l'arithmétique de base

Bien que la fonction de temps soit facile à utiliser, elle présente certaines limites (comme mentionné ci-dessus).

Si vous voulez plus de contrôle, vous pouvez utiliser la méthode arithmétique que je vais couvrir ici.

Le concept est simple - convertir une valeur de temps en une valeur décimale représentant la partie de la journée, que vous pouvez ensuite ajouter à n'importe quelle valeur de temps dans Excel.

Par exemple, si vous souhaitez ajouter 24 heures à une valeur de temps existante, vous pouvez utiliser la formule suivante :

= heure de début + 24/24

Cela signifie simplement que j'ajoute un jour à la valeur de temps existante.

Maintenant, en prenant le même concept, disons que vous voulez ajouter 30 heures à la valeur du temps, vous pouvez utiliser la formule suivante :

= heure de début + 30/24

La formule ci-dessus fait la même chose, où la partie entière de (30/24) représente le nombre total de jours de l'heure que vous souhaitez ajouter et la partie fractionnaire représente les heures/minutes/secondes

De même, si vous souhaitez ajouter un nombre spécifique de minutes à la valeur de temps, vous pouvez utiliser la formule suivante :

=Heure de début +(Ajouter minutes)/24*60

Si vous avez des secondes à ajouter, vous pouvez utiliser cette formule :

= heure de début + (minutes supplémentaires)/24*60*60

Bien que cette méthode ne soit pas aussi simple que d'utiliser la fonction de temps, je la trouve bien meilleure car elle fonctionne dans tous les cas et suit les mêmes concepts.Contrairement à la fonction de temps, vous n'avez pas à vous soucier d'ajouter du temps inférieur ou supérieur à 24 heures

Vous pouvez également suivre le même concept en soustrayant le temps.Changez simplement + en signe négatif dans la formule ci-dessus

Comment additionner le temps dans Excel

Parfois, vous voudrez peut-être ajouter rapidement toutes les valeurs de temps dans Excel.L'ajout de plusieurs valeurs de temps dans Excel est facile (juste une simple formule SOMME)

Cependant, lorsque vous ajoutez du temps dans Excel, vous devez connaître certaines choses, en particulier le format de cellule qui vous montrera les résultats.

Prenons un exemple.

Ci-dessous, j'ai une liste de tâches dans la colonne B et le temps que prendra chaque tâche, et je veux ajouter rapidement ces temps et connaître le temps total que toutes ces tâches prendront.

Dans la cellule B9, j'ai utilisé une simple formule SOMME pour calculer le temps total que prendront toutes ces tâches et cela me donne une valeur de 18h30 (ce qui signifie qu'il faut 18 heures et 20 minutes pour effectuer toutes ces tâches)

Jusqu'ici tout va bien!

Comment additionner plus de 24 heures dans Excel

Maintenant, voyez ce qui se passe lorsque je change le temps d'exécution de la tâche 2 de 1 heure à 10 heures.

Le résultat affiche maintenant 03:20, ce qui signifie qu'il faudra 3 heures et 20 minutes pour accomplir toutes ces tâches.

C'est faux (évidemment)

Le problème ici n'est pas qu'Excel est foutu.Le problème ici est que les cellules sont formatées pour afficher uniquement la partie heure de la valeur résultante.

Et comme la valeur résultante ici dépasse 24 heures, Excel décide de convertir la partie 24 heures en un jour, de la supprimer de la valeur affichée à l'utilisateur et de n'afficher que les heures, minutes et secondes restantes.

Heureusement, cela est facile à résoudre.

Il suffit de changer le format de la cellule pour forcer l'affichage des heures même si elle est supérieure à 24h.

Voici quelques formats que vous pouvez utiliser :

format	Résultats attendus
[h]: mm	28:30
[m]: ss	1710:00
d "D" hh:mm	Jour 04 30:XNUMX
d "D" hh "Min" ss "Sec"	1 jour 04 minutes 00 secondes
d "Jour" hh "Minute" ss "Secondes"	1 jour 04 minutes 00 secondes

Vous pouvez modifier le format en accédant à la boîte de dialogue Format de cellule et en appliquant un format personnalisé, ou utiliser la fonction TEXTE et utiliser l'un des formats ci-dessus dans la formule elle-même

Vous pouvez utiliser la formule TEXTE suivante pour afficher l'heure même si elle dépasse 24 heures :

=TEXTE(SOMME(B2:B7),"[h]:mm:ss")

Ou si vous souhaitez convertir des heures sur 24 heures en jours, utilisez la formule suivante :

=TEXTE(SOMME(B2:B7),"jj hh:mm:ss")

Les résultats affichent le hachage (###) au lieu de la date/heure (cause + solution)

Dans certains cas, vous pouvez constater qu'au lieu d'afficher des valeurs de temps, Excel affiche des signes dièse dans les cellules.

Voici quelques causes possibles et comment les résoudre :

Les colonnes ne sont pas assez larges

Lorsqu'une cellule n'a pas assez d'espace pour afficher la date complète, elle peut afficher un signe dièse.

Il a une solution simple - changez la largeur de la colonne et rendez-la plus large.

valeur de date négative

Les valeurs de date ou d'heure dans Excel ne peuvent pas être négatives.Si vous calculez le décalage horaire et que le résultat est négatif, Excel affichera le signe dièse.

Correction de la méthode pour changer la formule pour donner un résultat correct.Par exemple, si vous calculez le décalage horaire entre deux heures et que la date change, vous devrez ajuster la formule pour en tenir compte.

Dans d'autres cas, vous pouvez utiliser la fonction ABS pour convertir les valeurs de temps négatives en nombres positifs pour un affichage correct.Vous pouvez également utiliser une formule IF pour vérifier si le résultat est négatif et renvoyer une valeur plus significative.

Dans ce tutoriel, j'ai couvert des sujets sur le calcul du temps dans Excel (où vous pouvez calculer les différences de temps, ajouter et soustraire du temps, afficher le temps dans différents formats et additionner les valeurs de temps)

J'espère que vous avez trouvé ce tutoriel utile.

Foire aux questions sur le calcul de l'addition et de la soustraction de temps dans Excel