Exceller

Calculer le nombre d'heures entre deux fois

Calculate Number Hours Between Two Times

Formule Excel: calculer le nombre d'heures entre deux heuresFormule générique
= IF (end>start, end-start, 1-start+end)
Résumé

Pour calculer le nombre d'heures entre deux heures, vous pouvez utiliser une formule qui soustrait l'heure de début de l'heure de fin. Si les heures de début et de fin s'étendent sur minuit, vous devrez ajuster la formule comme expliqué ci-dessous. Dans l'exemple illustré, la formule en D5 est:

 
= IF (C5>B5,C5-B5,1-B5+C5)

Remarque: la complexité des formules de cette page est le résultat d'heures qui traversent minuit. Vous pouvez considérablement simplifier les choses en utilisant des valeurs de date contenant des heures ('datetimes'), comme expliqué ci-dessous .



Explication

La complexité du calcul du nombre d'heures entre deux heures provient des heures qui traversent minuit. En effet, les heures qui traversent minuit ont souvent une heure de début postérieure à l'heure de fin (c'est-à-dire début à 21h00, fin à 6h00). Cet article propose plusieurs solutions de formule, en fonction de la situation.



Calcul de durée simple

Lorsque l'heure de début et l'heure de fin surviennent le même jour, le calcul de la durée en heures est simple. Par exemple, avec une heure de début à 9h00 et une heure de fin à 17h00, vous pouvez simplement utiliser cette formule:

 
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8 hours

Pour voir le résultat en heures et minutes, appliquez une heure Format de nombre comme ça:



nombre aléatoire entre 1 et 7
 
h:mm

Cependant, lorsque les heures franchissent la limite d'une journée (minuit), les choses peuvent devenir délicates.

Comment Excel suit le temps

Dans Excel, un jour équivaut à 1 , ce qui représente 24 heures. Cela signifie que les heures et les heures sont des valeurs fractionnaires de 1, comme indiqué dans le tableau ci-dessous:

Les heures Temps Fraction Évaluer
3 03H00 3/24 0,125
6 06H00 6/24 0,25
4 4H00 DU MATIN 4/24 0,167
8 8 h 00 8/24 0,333
12 12h00 12/24 0,5
18 18h00 18/24 0,75
vingt-et-un 21H00 21/24 0,875

Quand les temps traversent minuit

Le calcul du temps écoulé est plus délicat si les heures traversent une limite de jour (minuit). Par exemple, si le Heure de début est 22h00 un jour, et le heure de fin est 5 h 00 le lendemain, l'heure de fin est en fait inférieure à l'heure de début et la formule ci-dessus renverra une valeur négative, et Excel affichera une chaîne de caractères de hachage (########).



Pour corriger ce problème, vous pouvez utiliser cette formule pour les heures qui traversent une limite de jour:

 
=1-start+end

En soustrayant le Heure de début à partir de 1, vous obtenez la durée du premier jour, que vous pouvez simplement ajouter à la durée du 2ème jour, qui est la même que la heure de fin . Cette formule ne fonctionnera pas pour les heures qui se produisent le même jour, nous devons donc utiliser Fonction IF comme ça:

comment calculer les quintiles dans Excel
 
= IF (end>start, end-start, 1-start+end)

Lorsque les deux heures sont dans la même journée, finir est supérieur à Heure de début , la formule simple est utilisée. Mais lorsque les heures à travers une limite de jour, la deuxième formule est utilisée.

Alternative à la fonction MOD

Le Fonction MOD fournit une manière élégante de simplement la formule ci-dessus. En utilisant la fonction MOD avec un diviseur de 1, nous pouvons créer une formule pour gérer les deux situations:

 
= MOD (end-start,1)

Cette formule prend en charge le temps négatif en utilisant la fonction MOD pour «retourner» les valeurs négatives à la valeur positive requise. Étant donné que cette formule gérera les heures du même jour et les heures qui s'étendent sur minuit, nous n'avons pas besoin d'une instruction IF conditionnelle.

Remarque: aucune des formules ci-dessus ne prendra en charge les durées supérieures à 24 heures. Si vous en avez besoin, consultez l'option date + heure ci-dessous.

Pour en savoir plus sur modulo, voici un bon lien sur Khan Academy .

Simplifier avec la date et l'heure

Vous pouvez simplement le problème du calcul du temps écoulé de manière drastique en utilisant des valeurs qui contiennent tous les deux date et heure, parfois appelées «datetimes». Pour entrer une date et une heure ensemble, utilisez un seul espace entre l'heure et la date comme ceci: 01/09/2016 10:00. Si vous mettez en forme cette date avec le format Général, vous verrez une valeur comme celle-ci:

 
42614.4166666667 // date + time

Les nombres à droite de la décimale représentent la composante heure de la date. Une fois que vous avez des valeurs datetime, vous pouvez utiliser une formule de base pour calculer le temps écoulé. Dans l'écran ci-dessous, les valeurs de début et de fin contiennent à la fois des dates et des heures, et la formule est simplement:

 
=C5-B5 // end-start

Temps écoulé en heures avec date et heure ensemble

Le résultat est formaté avec le format de nombre personnalisé :

 
[h]:mm

pour afficher les heures écoulées. Cette formule calculera correctement les heures entre deux fois dans une seule journée ou sur plusieurs jours.

comment frapper f1 sur mac

Formater les durées de temps

Par défaut, Excel peut afficher l'heure, même l'heure qui représente une durée, en utilisant AM / PM. Par exemple, si vous avez un temps calculé de 6 heures, Excel peut l'afficher comme 6h00. Pour supprimer AM / PM, appliquez un format de nombre personnalisé aimer:

 
h:mm

Dans les cas où le temps calculé peut dépasser 24 heures, vous devez utiliser un format personnalisé tel que [h]: mm. La syntaxe entre crochets [h] indique à Excel d'afficher des durées d'heures supérieures à 24 heures. Si vous n'utilisez pas les crochets, Excel «retournera» simplement lorsque la durée atteint 24 heures (comme une horloge).

Auteur Dave Bruns


^