La gestion des heures avec Microsoft Excel ne sera plus un secret après avoir lu cet article.
Généralités
Lorsque vous saisissez une heure dans Excel (par exemple 8:00), votre cellule se formate automatiquement :
Derrière cela il y a un double mécanisme automatique : Excel écrit l’équivalent de 8h sur une journée de 24h (soit 8/24), puis applique un format de cellule de type « hh:mm » :
On s’aperçoit bien que la valeur de la cellule D2 (08:00) est bien égale à 8/24 soit 0,3333333.
Calculs sur les heures
Prenons un exemple simple : Nous souhaitons calculer les heures effectuées au cours d’une semaine. Pour cela, pour chaque jour de la semaine, nous allons saisir :
- L’heure d’arrivée
- L’heure de départ
- Le temps de pause
L’objectif est donc de pouvoir calculer le temps de travail par jour (en heures et en valeur décimale). Il s’agit d’une simple soustraction : « Heure de sortie » – « Heure d’entrée » – « Temps de pause ».
Pour transformer les heures en valeurs numériques, il suffit de multiplier les heures par 24 (le nombre d’heures dans une journée) :
Format de cellule
Si on souhaite faire la somme des heures réalisées, on s’aperçoit que le résultat est de 11h00…
En fait, la somme nous donne 1,458333 en valeur numérique. Donc, il s’agit de 1,45833 journées ; soit 35 heures (24 + 11 heures). En réalité, Excel « omet » l’entier de 1,45833 et n’affiche que les 11 heures restantes.
Pour pallier à cela, il faut donc modifier le format de la cellule en « [h]:mm » pour que la cellule affiche 35:00.
Calculs d’heures sur plusieurs jours
Si dans notre exemple la personne commence à travailler à 23h30 et termine à 6h45 le lendemain, on obtient une cellule avec « ############## » :
En effet, on a fait 6h45 – 23h30 – 01h00, soit 0,28125 jour – 0,9791667 jour – 0,041667 jour = -0,7395833.
Or, Excel ne peut afficher des heures en négatif… Il faut donc modifier la formule de calcul pour prendre en compte ce cas de figure. Il suffit d’ajouter la valeur 1 (correspondant à une journée) si l’heure d’entrée est supérieure à l’heure de sortie.
On obtient donc la formule :
Afficher des heures en négatif
Admettons que nous souhaitions calculer l’écart d’heures effectuées par rapport aux temps théorique en affichant les caractères « + » et « – » :
Il faut effectuer 3 calculs distincts :
- Est-ce que les heures réalisées sont égales aux heures théoriques ?
- Est-ce que les heures réalisées sont supérieures aux heures théoriques ?
- Est-ce que les heures réalisées sont inférieures aux heures théoriques ?
On obtient :
=SI(B3=$C$2; »0:00″;SI(B3-$C$2>0; »+ »&TEXTE(B3-$C$2; »hh:mm »); »-« &TEXTE($C$2-B3; »hh:mm »)))
Ici, nous utilisons la fonction TEXTE car en ajoutant les signes « + » et « – », le résultat du calcul est un texte. Il faut donc formater le résultat directement dans la formule.
A cela on ajoute 2 mises en forme conditionnelles pour que le texte change de couleur :
Conclusion
Une fois que l’on a compris comment fonctionne les heures sous Excel, il est plutôt simple de réaliser des calculs !
Les Experts Excel vous proposent des formations, du développement, de l’expertise et de l’assistance sur Microsoft Excel. N’hésitez plus et contactez-les dès maintenant !