Calculs sur les heures et dates dans Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calculs sur les heures et les dates avec Excel

Dans cette formation nous allons passer en revue l'ensemble des techniques qui consistent à réaliser des opérations sur des dates et des heures dans Excel. Nous verrons notamment comment réaliser facilement la différence entre deux dates, pour obtenir l'ancienneté d'un salarié ou l'âge d'une personne par exemple. Nous apprendrons de même à sommer des heures de travail pour calculer les heures supplémentaires et à les convertir en nombres décimaux classiques.



Dans l'extrait du tableau de la capture ci-dessus, des opérations sont réalisées pour calculer le nombre d'heures de travail par salarié, et en déduire éventuellement, les heures supplémentaires pour chacun.

Extraire le mois d'une date en texte
L'objectif de ce premier exercice consiste à afficher dans une cellule, le mois en toutes lettres, à partir d'une date, tapée au format date. Concrètement, de la date 16/08/2017, nous devons être capables d'extraire l'information Août à minima.

• Télécharger le classeur calculs-dates-heures-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,
• Cliquer sur l'onglet de la feuille Extraire mois date si elle n'est pas active,

Ce tableau résume les chiffres réalisés par les vendeurs d'une entreprise, au cours du premier trimestre de l'année 2017. Nous l'avions utilisé dans une formation pour présenter les techniques de filtres sur les tableaux Excel. Et dans cette formation, nous avions utilisé une fonction RechercheV afin de trouver la correspondance textuelle d'un mois en fonction de son numéro, renvoyé par la fonction Mois d'Excel. C'est pourquoi figure un petit tableau en colonnes J et K afin d'opérer cette correspondance. Ici nous allons utiliser des techniques plus simples, dédiées aux cellules de dates. Pour commencer, nous n'allons utiliser aucune formule. Nous allons simplement personnaliser le format de la date.

• Sélectionner la première cellule de la colonne Mois du milieu, soit C6,
• Taper le symbole = pour débuter le calcul,
• Cliquer sur la première cellule de la première colonne Mois, soit A6,
• Valider le calcul par CTRL + Entrée pour garder la cellule active,
• Double cliquer sur la poignée en bas à droite de la cellule pour reproduire le calcul sur toute la colonne,

Cette technique permet de reproduire automatiquement le calcul sur les lignes du dessous. Vous auriez aussi pu tirer cette poignée vers le bas, ce qui est légèrement plus long. A ce stade, le calcul que nous avons réalisé est une simple copie de la date de la première colonne (=A6). C'est la raison pour laquelle, nous obtenons exactement les mêmes informations de date, avec la même présentation. Or pour la première date de la colonne C, 17/01/2017, nous souhaitons afficher simplement le texte Janvier. Pour ce faire, nous allons manipuler le format des cellules.

• Sélectionner les dates de la colonne C, soit C6 à C29,
• Cliquer avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel, choisir Format de cellule,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée dans la liste de gauche,

La partie droite de cette boîte de dialogue présente le format en cours sur les cellules, soit le format jj/mm/aaaa. Deux symboles pour les jours, soit deux chiffres, idem pour les mois et quatre chiffres pour l'année.

• Dans la zone Type, remplacer ce format par mmmm, en le tapant,
• Puis valider par Ok pour appliquerce format de date personnalisé,

Comme vous le constatez, toutes les dates de la colonne C, sont transformées en mois et affichées en textes. Les dates sont transformées en apparence seulement. Un format Excel permet d'afficher une vue différente d'une donnée tout en conservant sa version d'origine. Et c'est le cas ici. La vraie valeur de la cellule reste 17/01/2017. Mais son affichage est trompeur, grâce au format. On pourrait d'ailleurs le confirmer en activant les filtres automatiques notamment.

Nous allons donc mettre en oeuvre une autre solution à l'aide d'une fonction de calcul Excel. La fonction texte(), permet d'afficher sous forme de texte, le contenu d'une cellule. Plus précisément, elle permet de convertir un nombre en texte, selon un format à préciser. Elle requiert donc deux paramètres :=texte(valeur_a_convertir ; format_texte).

• Sélectionner de nouveau tous les résultats de la colonne C, soit C6 à C29,
• Les supprimer en enfonçant la touche Suppr du clavier,
• Sélectionner la première cellule à calculer, soit C6,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction et ouvrir la parenthèse, soit texte(,
• Cliquer sur la première date de la colonne A pour la désigner, soit A6,
• Taper un point-virgule (;) pour passer au second argument,
• Taper le format mmmm entre guillemets, soit 'mmmm',
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,
• Puis le reproduire en double cliquant sur la poignée de la cellule,

Le format 'mmmm' passé en deuxième paramètre de la fonction texte, indique d'une part que nous souhaitons extraire seulement le mois de la date et d'autre part avec une précision telle, qu'il s'affiche en toutes lettres. Voici les résultats que nous aurions obtenu en spécifiant des formats différents :

• =TEXTE(A6; 'jjjj') : Mardi,
• =TEXTE(A6; 'jjjj jj') : Mardi 17,
• =TEXTE(A6; 'mmmm') : Janvier,
• =TEXTE(A6; 'aaaa') :2017,
• =TEXTE(A6; 'jjjj jj') & ' ' & TEXTE(A6; 'mmmm') & ' ' & TEXTE(A6;'aaaa') : Mardi 17 janvier 2017,

Transformer une date en chiffres
Les dates dans Excel sont des données tout à fait spéciales. Il ne s'agit ni de textes, ni de nombres. C'est pourquoi Excel propose des formats tout à fait spécifiques pour leur affichage. C'est aussi la raison pour laquelle, elles doivent être saisies avec des séparateurs comme des slashs (/) ou des tirets entre les groupes de chiffres (Jour/Mois/Année). Mais il peut néanmoins s'avérer utile de les afficher, ou plutôt de les transformer en nombres.

• Activer la feuille Dates en nombres,

Le tableau de cette feuille présente une liste de salariés d'une entreprise. Dans la colonne E figure la date d'embauche de chacun. Dans la colonne F sur la droite, il s'agit d'afficher cette date, convertie en nombre en fonction de la suite logique des chiffres composant la date.

Excel propose les fonctions Jour(), Mois() et Annee() pour respectivement extraire et afficher, sous forme de nombre, le jour, le mois et l'année d'une date. Il suffit alors de concaténer ces résultats pour assembler les chiffres de la date et l'obtenir sous forme de nombre. Pour ce faire :

• Sélectionner la cellule F6 et taper le symbole = pour débuter le calcul,
• Puis, saisir la formule suivante : JOUR(E6) & MOIS(E6) & ANNEE(E6),
• Valider le calcul par CTRL + Entrée pour conserver la cellule active,
• Double cliquer sur sa poignée pour reproduire le calcul sur toute la colonne,

Le Et Commercial (&) permet d'assembler le résultat retourné par chaque fonction, soit le chiffre correspondant.


Nous obtenons bien la date recomposée en une suite de chiffres. Il s'agit de l'assemblage du chiffre du jour, de celui du mois et de l'année. Le résultat est bien une cellule numérique. Cependant, les zéros en préfixe ont disparu. Excel gère des nombres, ces fonctions renvoient des nombres, donc les 0 en préfixe sont considérés comme inutiles. Pour pallier le problème, nous pourrions intégrer ces calculs dans des fonctions Si afin de rajouter les 0 lorsque le chiffre retourné est inférieur à 10, comme suit :

= SI(JOUR(E6) < 10; '0'&JOUR(E6); JOUR(E6)) & SI(MOIS(E6) < 10; '0' & MOIS(E6); MOIS(E6)) & SI(ANNEE(E6) < ;10;'0' & ANNEE(E6);ANNEE(E6))

Cette option n'est pas satisfaisante étant donnée la longueur de la formule, malgré la puissance de la fonction SI(). C'est pourquoi nous choisissons d'exploiter de nouveau la fonction texte(), afin de convertir la date, en une série de chiffres, concaténés dans une chaîne de caractères. Pour conserver la précision sur tous les chiffres, il suffit de choisir le format adapté de la fonction texte, à concaténer pour chaque extraction (jj pour le jour, mm pour le mois et aaaa pour l'année).

• Supprimer tous les calculs précédents sur la plage F6:F14,
• Sélectionner la cellule F6 et taper le symbole = pour débuter le calcul,
• Taper la formule suivante : =TEXTE(E6; 'jj') & TEXTE(E6; 'mm') & TEXTE(E6;'aaaa'),
• Valider le calcul par CTRL + Entrée et double cliquer sur la poignée de la cellule pour le reproduire,

Grâce à la concaténation des extractions réalisées par la fonction texte(), sur la date, nous réussissons en effet à convertir cette dernière en nombre, tout en conservant les zéros en préfixe.


Ancienneté et âge - Différence entre deux dates
L'objectif maintenant est de savoir réaliser la différence entre deux dates, en affichant le résultat sous forme d'années ou éventuellement de jours et de mois.

• Activer la feuille Ancienneté en cliquant sur son onglet en bas de la fenêtre Excel,

Ce tableau représente une liste des salariés d'une entreprise, pour lesquels il s'agit de calculer la date d'embauche en fonction de la période d'essai préalable, dans un premier temps. Nous verrons ensuite comment calculer l'ancienneté et les primes. L'ancienneté justement évolue en fonction du calendrier. Plus les jours passent, plus l'ancienneté progresse. Il s'agit donc de faire référence à une date dynamique, basée sur le calendrier. La date du jour dynamique s'obtient par une fonction Excel, la fonction aujourdhui().

• En cellule C17, taper la formule suivante et valider : =AUJOURDHUI(),

Vous obtenez la date du jour qui s'actualisera en fonction du calendrier.


La date d'embauche définitive prend effet deux mois après la première date d'embauche, selon une période d'essai. Dans les cellules de la colonnes F, il s'agit donc d'ajouter 2 mois à la date d'embauche des cellules de la colonne E. Les additions et soustractions se font naturellement entre des dates inscrites dans les cellules d'une feuille Excel. Mais par défaut, les sommes ou retranchements se réalisent sur les jours. C'est ce que nous allons prouver.

• En cellule F6, taper le symbole = pour débuter le calcul,
• Taper ensuite la formule : E6+2,
• Puis, valider par CTRL + Entrée et double cliquer sur la poignée de la cellule pour répercuter le calcul sur toutes les cellules concernées, de la colonne F,

Comme vous le constatez, les dates obtenues sont simplement incrémentées de 2 jours, par rapport au début de la période d'essai, et non de 2 mois, comme nous le souhaitons. Pour pouvoir ajouter 2 mois à une date dans Excel, nous devons lui indiquer que cette addition doit s'effectuer sur les mois. Pour ce faire, nous allons exploiter la fonction Date() d'Excel qui permet de construire et d'inscrire une date, en bonne et due forme, en fonction de trois paramètres. Ces trois paramètres sont dans l'ordre l'année de la date, le mois de la date et le jour de la date : =Date(Annee ; Mois ; Jour).

Il suffit donc de lui passer ces informations décomposées de la date en E6 grâce aux fonctions Annee(), Mois() et Jour() qui permettent de décomposer chacune de ces informations. En deuxième paramètre de la fonction Date(), il ne restera plus qu'à ajouter 2 mois à la valeur récupérée. Ces fonctions sont d'ailleurs présentées par le support des opérations sur les dates dans Excel.

• Supprimer les calculs précédents de la colonne F,
• En F6, saisir la formule =DATE(ANNEE(E6); MOIS(E6)+ 2; JOUR(E6)),
• Valider par CTRL + Entrée et reproduire le calcul jusqu'en F14,

L'addition s'effectue au sein de la fonction Date() sur les mois. Nous obtenons bien la date de départ, incrémentée de deux mois correspondant à la période d'essai, et restituée dans un bon format date, dans la cellule du calcul.

Nous devons maintenant calculer l'ancienneté du salarié, en fonction de sa date d'embauche définitive. Son ancienneté est la différence en années, entre sa date d'embauche et la date actuelle, soit la date du jour dynamique, inscrite en C17. La fonction Excel qui permet de réaliser la différence entre deux dates est la fonction Datedif(). Elle calcule le nombre de jours, de mois ou d'années qui séparent deux dates. Elle requiert trois arguments : la date départ, la date d'arrivée et l'unité pour définir sous quelle forme la différence doit être renvoyée. Sa syntaxe est la suivante : = Datedif(date_debut ; date_fin ; 'unite_pour_difference'). Comme nous l'avons dit précédemment, si nous réalisons simplement la différence entre la date du jour en C17 et la date d'embauche définitive en F6, nous obtiendrons bien la différence entre les deux dates mais en nombre de jours. La fonction Datedif() va nous permettre d'afficher l'ancienneté en années.

• En cellule G6, taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction et ouvrir la parenthèse : Datedif(,

Vous remarquez qu'elle n'est pas proposée dans les suggestions de fonctions Excel. Pire, aucune info-bulle ne se déclenche à l'ouverture de la parenthèse pour aider à renseigner les paramètres.

• Cliquer sur la cellule F6 pour désigner la date de début puis taper un point-virgule (;),
• Cliquer sur la cellule de la date du jour en C17 pour la désigner,
• Enfoncer la touche F4 du clavier pour la figer en référence absolue dans le calcul,
• Taper un point-virgule et taper l'unité de sortie pour le calcul de la différence soit 'y',
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,
• Reproduire le calcul sur l'ensemble de la colonne en tirant la poignée par exemple,

Nous obtenons toutes les anciennetés, soit les différences entre les deux dates, calculées en années, grâce au troisième paramètre 'y', y pour Year soit année en anglais. Nous avons figé la cellule C17 pour que chaque calcul de la colonne G y fasse référence, sans la déplacer, bien que nous le reproduisions sur les lignes du dessous. C'est ce qu'enseigne la formation Excel pour savoir reproduire un calcul en toutes circonstances avec une seule formule.


Si nous avions saisi la formule =DATEDIF(F6; $C$17; 'm'), nous aurions obtenu la différence entre les deux dates, en nombre de mois grâce au paramètre 'm', pour Month soit Mois en anglais. En le remplaçant par le paramètre 'd' pour Day en anglais, soit Jour, nous aurions obtenu l'ancienneté, en nombre de jours. En assemblant ces paramètres et en concaténant les résultats des calculs, nous pouvons calculer la différence précise entre deux dates, en nombres d'années, de mois et de jours. Ainsi le paramètre 'ym' permet de retourner la différence supplémentaire en mois, à ajouter à la différence en années déjà calculée. De même, le paramètre 'md' permet de renvoyer la différence supplémentaire en nombre de jours, à ajouter aux nombres d'années et de mois, déjà calculés. Il faut donc commencer par calculer la différence en nombre d'années, comme précédemment, et concaténer la précision sur les mois et jours.

• Sélectionner la cellule G6 et enfoncer la touche F2 du clavier pour activer sa saisie,
• A la suite de la formule, ajouter la concaténation suivante : & ' ans ',
• Puis, valider par CTRL + Entrée.

Nous obtenons par exemple, le résultat 15 ans.

• Enfoncer de nouveau la touche F2 pour poursuivre la modification de la formule,
• A la suite, ajouter la concaténation suivante : & DATEDIF(F6; $C$17; 'ym') & ' mois',
• Valider le calcul par CTRL + Entrée,

Nous obtenons par exemple, le résultat 15 ans 8 mois. Au calcul sur la différence des années, nous ajoutons la différence supplémentaire en nombre de mois, grâce au paramètre 'ym'. Si nous avions simplement désigné le paramètre 'm', nous aurions ajouté la différence totale en nombre de mois, sans considérer que le calcul sur les années, avait déjà été effectué.

• Enfoncer la touche F2 pour reprendre la saisie du calcul,
• Ajouter les concaténations suivantes : & ' et ' & DATEDIF(F6; $C$17; 'md') & ' jours',
• Valider la formule par CTRL + Entrée,
• Double cliquer à l'intersection des étiquettes de colonnes G et H, pour adapter la largeur de la colonne à son contenu,

Cette méthode pour manipuler les colonnes, est enseignée comme beaucoup d'autres, dans le support de Formation sur les trucs et astuces Excel. Nous obtenons par exemple, le résultat 15 ans 8 mois et 23 jours, soit la différence très précise qui sépare deux dates. Notez de même, que cette différence se mettra continuellement à jour, grâce à la fonction aujourdhui() en C17, utilisée comme date de fin de référence.

• Double cliquer sur la poignée de la cellule pour répercuter le calcul sur l'ensemble de la colonne,

Pour les calculs suivants, nous avons besoin que le résultat de la colonne ancienneté, soit numérique. Or les concaténations avec du texte ont transformé la cellule en texte. La différence en années est une précision suffisante pour le calcul de l'ancienneté.

• Sélectionner la cellule G6 et enfoncer la touche F2,
• Réduire la formule à l'expression suivante : =DATEDIF(F6; $C$17; 'y'),
• Valider le calcul par CTRL + Entrée et le reproduire sur la colonne,

Désormais la colonne ancienneté est remplie de nombres entiers. Pour calculer la Prime, attribuée aux salariés, dont le pourcentage est de 15% du salaire, les conditions sont les suivantes. Le salaire doit être inférieur à 1600 Euros et l'ancienneté supérieure à 15 ans. C'est la fonction Si d'Excel qui permet de gérer les critères pour réaliser des actions adaptées. La syntaxe de la fonction Si est la suivante =Si(Critere_a_verifier ; Action_Alors ; Action_Sinon). Deux critères doivent être vérifiés ensemble, sur le montant du salaire et l'ancienneté. C'est la fonction exclusive Et d'Excel, dans la zone de critère qui se chargera de vérifier, que les deux conditions sont respectées. Ce qui donne la syntaxe suivante : =Si(ET(Critere1 ; Critere2) ; Action_Alors ; Action_Sinon). Si l'un des deux critères n'est pas vérifié, l'action du Sinon est entreprise à la place de l'action du Alors.

• Sélectionner la cellule I6 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction SI et ouvrir la parenthèse : SI(,
• Ajouter alors la fonction ET puis ouvrir la parenthèse : ET(,
• Ecrire le premier critère sur le salaire et taper un point-virgule (;) soit : H6<1600;,
• Taper alors le second critère sur l'ancienneté et fermer la parenthèse, soit : G6>15),
• Saisir un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Taper le calcul de la prime sur le salaire, soit : H6*0,15,
• Saisir un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets et fermer la parenthèse, soit : ''),
• Valider la formule par CTRL + Entrée et la reproduire sur toute la colonne du tableau,

Le fait de saisir deux guillemets ('') dans la branche Sinon de la fonction Si, permet de n'entreprendre aucune action lorsque les critères ne sont pas vérifiés, en remplissant la cellule de vide. Comme vous le constatez, seuls deux salariés ont le droit à une prime. La précision sur les décimales n'est pas indispensable.

Nous souhaitons tronquer ces résultats à l'unité. Le bouton réduire les décimales de la section Nombre du ruban Accueil, permet seulement de tronquer cette précision à l'affichage. Pour arrondir réellement le résultat d'un calcul, nous pouvons utiliser une fonction Excel comme Arrondi.Sup(Nombre_a_arrondir ; Precision_decimales) ou Arrondi.Inf(Nombre_a_arrondir ; Precision_decimales). La première arrondit au nombre supérieur selon le nombre de décimales souhaitées et la seconde arrondit au nombre inférieur. Dans la fonction SI, nous souhaitons arrondir le calcul de la prime à l'unité, nous écrirons donc : Arrondi.Sup(H6*0,15; 0).

• Sélectionner la cellule I6 et enfoncer la touche F2 du clavier pour modifier le calcul,
• Intégrer le calcul de la prime dans la fonction Arrondi.Sup par exemple,
• Valider par CTRL + Entrée et reproduire le calcul sur la colonne,

• Sélectionner les cellules de la colonne Prime, soit I6:I14,
• Réaliser le raccourci clavier CTRL + M pour les formater en monétaire,

Cette combinaison, entre autres, est enseignée dans la formation sur les raccourcis claviers du tableur Excel.

Le calcul du total devient trivial. Il s'agit d'ajouter la prime au salaire de base. La fonction Somme d'Excel permet d'ignorer une cellule vide dans un calcul, contrairement à une addition classique, réalisée avec le symbole + du pavé numérique, qui conduirait à une erreur.

• En cellule J6, saisir le calcul suivant : =SOMME(H6:I6),
• Le valider par CTRL + Entrée et le reproduire par double clic sur sa poignée,

Additionner les heures de travail - Calculs des heures supplémentaires
Excel sait additionner des heures entre elles. Les heures sont saisies selon le format suivant dans les cellules : hh:mm. Mais lorsque la somme des heures dépasse 24 heures, Excel recommence à Zéro pour respecter la boucle journalière. Ainsi pour 19:00 + 10:00, Excel affiche 05:00 au lieu de 29:00. Nous allons voir comment palier le problème grâce à une astuce de format personnalisé sur les heures.

• Cliquer sur l'onglet Planning en bas de la fenêtre, pour activer sa feuille,

Ce tableau présente les heures travaillées par semaine, pour certains salariés d'une entreprise, sur une période donnée. Commençons par constater que, dans un contexte normal, Excel sait sommer des heures.

• En K2, saisir 8:15 et valider par entrée,

Grâce aux deux points qui servent de séparateur entre les heures et les minutes, Excel interprète bien la donnée comme une heure et ajoute d'ailleurs le 0 en préfixe.

• En K3, saisir 07:30 et valider par Entrée,
• En K4, réaliser le raccourci clavier Alt + =,

Excel propose de faire la somme automatique des deux cellules du dessus, soit des heures précédemment saisies.

• Valider cette proposition en enfonçant la touche Entrée.

Le résultat fourni est 15:45, soit l'addition correcte des deux horaires saisis. Partant de ce principe, nous allons réaliser la même opération, pour calculer la somme des heures travaillées, par salarié et par semaine.

• Pour la première semaine, sélectionner toutes les heures à sommer ainsi que les cellules vides où placer les résultats par colonne, soit la plage de cellules C2:I7,
• Réaliser le raccourci Alt + = ou cliquer sur le bouton Somme automatique du ruban Accueil,

En indiquant dès la sélection l'emplacement des cellules vides pour le calcul, la somme automatique délivre instantanément les résultats des additions sans étape intermédiaire. Mais comme vous le remarquez en affichant le calcul d'une cellule, la formule est correcte mais pas son résultat. Par exemple, en colonne C, nous devrions obtenir une somme totale de 37:00 heures travaillées. Or la cellule affiche 13:00 qui est le résultat de 37:00 - 24:00. C'est ainsi que fonctionnent les heures par défaut, dans Excel. Elles se basent sur le cadran de l'horloge si bien que, dès qu'elles dépassent les 24 heures, elles sont réinitialisées à 0. C'est ainsi que sont rythmées nos journées.


Il s'agit d'une question de format de cellule que nous avons déjà abordée dans la formation Excel sur les opérations avec des heures et des dates. Nous allons le modifier afin d'indiquer à Excel, de poursuivre l'addition des heures au-delà de 24.

• Sélectionner toutes les cellules de calcul, soit la plage C7:I7,
• Cliquer avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel, choisir Format de cellule,
• Dans la boîte de dialogue, sélectionner la catégorie Personnalisée dans la liste de gauche,
• Dans la zone Type qui illustre le format en cours, remplacer les lettres hh par [h],
• Valider en cliquant sur Ok,

Cette simple modification de format suffit à indiquer à Excel, de poursuivre l'addition des heures au-delà de la limite fournie par une journée. Nous obtenons bien le total des heures travaillées par semaine, pour chaque salarié.

• Sélectionner de nouveau la plage de cellules C7:I7,
• Réaliser le raccourci clavier CTRL + C pour la copier,
• Cliquer sur la cellule C14 pour l'activer,
• Puis réaliser le raccourci clavier CTRL + V pour la coller,
• Faire de même en C21 et C28,

Le copier-coller permet de répliquer toutes les informations de cellule, comme le format. C'est pourquoi, grâce aux références relatives, les calculs sont bien répliqués sur les cellules du dessous avec le format personnalisé pour les heures.

Il s'agit maintenant de calculer les heures supplémentaires lorsque le total dépasse les 35 heures. Les heures sont des données particulières, elles ne peuvent pas être considérées et calculées comme des nombres. Pour savoir si le total de la semaine est supérieur à 35, il faut convertir le total des heures en donnée décimale. Pour convertir cette donnée, nous devons la diviser par '1:00'. Faisons un essai dans une cellule arbitraire.

• Sélectionner la cellule L7,
• Taper le calcul suivant : =F7/'1:00' et valider par CTRL +Entrée,
• Appliquer le format standard à cette cellule,

Nous obtenons le total des heures converti en nombre décimal, soit 35,167 environ. En effet en F7, nous avons le résultat 35:10, soit 35 heures et 10 minutes. Nous retrouvons bien le 35 dans notre nombre décimal. 10 minutes correspondent à 1/6 ème d'une heure (60mn/6). Or 1/6 = 0,167 environ. Le résultat est correct. Donc le critère à poser dans une fonction Si pour calculer les heures supplémentaires en C8 est le suivant : (C7/'1:00')>35. Il suffira alors de retrancher 35 heures au résultat du dessus pour obtenir le nombre d'heures supplémentaires. Et comme vous le savez désormais, les heures doivent s'écrire entre guillemets, dans un calcul.

• Sélectionner la cellule C8 et taper le symbole = pour débuter le calcul,
• Saisir le début de la fonction Si avec son critère : =SI((C7/'1:00') > 35,
• Puis, saisir un point-virgule (;) pour passer à la branche Alors du calcul à réaliser,
• Saisir C7-'35:00', puis taper un nouveau point-virgule (;),
• Taper deux guillemets ('') pour indiquer de ne rien faire dans le cas contraire,
• Fermer la parenthèse et valider la formule par CTRL + Entrée,
• Tirer la poignée du calcul sur la droite jusqu'à I8, pour le reproduire,

La soustraction des heures a parfaitement fonctionné puisque nous obtenons bien les heures supplémentaires travaillées pour chaque salarié, à l'issue de la semaine.

• Sélectionner les résultats du calcul, soit la plage de cellules C8:I8,
• Les copier (CTRL + C),
• Les coller tour à tour (CTRL + V) en C15, C22 et C29,

Comme précédemment, les calculs sont répercutés avec leur format. Nous obtenons la synthèse des heures supplémentaires par salarié, pour toute la période. Comme vous le constatez, lorsque le total de la semaine n'excède pas les 35 heures, aucun résultat ne s'affiche, grâce à l'instruction '' saisie dans la branche Sinon de la fonction SI.


Echéances de paiement en tenant compte des jours ouvrés

• Activer la feuille échéances de paiements en cliquant sur son onglet,

Le tableau de gauche propose de calculer des dates d'échéance de paiement, selon des critères différents, énoncés en colonne D et en fonction des dates d'émission des factures. Le tableau situé en colonnes G et H, propose les fonctions Excel à utiliser pour calculer les dates limites de paiement, avec les résultats à obtenir. Enfin le dernier tableau situé sur la droite, propose de décomposer l'information de date ainsi retournée. Comme nous l'avons déjà vu, les unités qui s'ajoutent par défaut sur une date sont les jours. Le premier calcul peut donc être réalisé par une simple addition.

• Sélectionner la cellule E4 et taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule de la date d'émission de la facture, soit E4,
• Puis taper + 15 et valider par Entrée,

Nous obtenons bien la date initiale incrémentée de 15 jours, soit un résultat identique à celui proposé par le tableau du milieu. Le calcul suivant consiste à définir la date d'échéance de paiement au dernier jour du mois correspondant à la date de facturation. Selon le cas, il peut donc s'agir du 30, du 31 ou encore du 28 et du 29 pour le mois de Février. Excel connaît le calendrier et ses subtilités. Il propose à ce titre la fonction Fin.Mois() qui retourne la date du dernier jour du mois souhaité. La syntaxe de cette fonction est la suivante : =Fin.Mois(date ; nb_mois). Le premier paramètre doit être la date à partir de laquelle il faut calculer l'échéance, soit la date de facturation ici. Le second paramètre est l'échéance en nombre de mois. Par exemple, pour une échéance fin de mois suivant, nous saisirons 1. Pour fin de mois en cours, nous saisirons 0.

• En cellule E5, taper la formule suivante : =FIN.MOIS(B5; 0),

Nous obtenons bien une échéance fixée au dernier jour du mois en cours, puisque la formule retourne le 31/03/2014. Le calcul suivant propose de fixer la date limite de paiement de la facture à 30 jours fin de mois. En d'autres termes, il s'agit du dernier jour du mois suivant. Il suffit donc de passer 1 en deuxième paramètre de la fonction Fin.Mois().

• En E6, saisir la formule suivante : =FIN.MOIS(B6; 1),

Le résultat retourné est le 30/04/2014, soit le dernier jour du mois après le mois de Mars.

Ensuite, il s'agit de fixer la date d'échéance de paiement à 40 jours ouvrés après la date d'émission de la facture. Comme l'indique le tableau du milieu, Excel propose la fonction Serie.Jour.Ouvre(). Cette fonction demande trois paramètres : =SERIE.JOUR.OUVRE(date_depart; nb_jours_ajouter; [liste_jours_feries]). Le premier paramètre est forcément la cellule de la date d'émission de la facture. Le deuxime paramètre permet d'indiquer avec une valeur entière, le nombre de jours ouvrés à ajouter à la date de départ, pour calculer la date d'échéance. Enfin le dernier paramètre est facultatif. Il consiste à indiquer une plage de cellules sur laquelle sont référencés les jours fériés potentiels, sur la période. C'est pourquoi une petite liste recense les jours fériés à partir de la cellule B13.

• En E7, initialiser le calcul avec le symbole =,
• Saisir le nom de la fonction et ouvrir la parenthèse, soit : SERIE.JOUR.OUVRE(,
• Cliquer sur la cellule de la date de facturation pour la désigner soit B7,
• Puis, taper un point-virgule (;) et saisir 40 pour l'intervalle en jours ouvrés,
• Taper un nouveau point-virgule (;) pour accéder au dernier argument facultatif,
• Sélectionner la plage de cellules des jours fériés, soit B14:B23,
• Fermer la parenthèse et valider le calcul,

Nous obtenons bien le 14/05/2014 proposé par le tableau des résultats, soit plus de 60 jours après la date de facturation. En effet, seuls les jours ouvrés ont été comptabilisés en excluant de plus, les jours fériés de la période. Pour calculer une date limite de paiement en fonction d'un délai fixé cette fois, en nombre de jours ouvrables, Excel propose la fonction =SERIE.JOUR.OUVRE.INTL(). Elle est quasiment identique à la précédente à un paramètre près, celui des jours vaqués. Sa syntaxe est la suivante : = SERIE.JOUR.OUVRE.INTL (date_depart; nb_jours_ajouter; num_jours_vaques ; [liste_jours_feries])

• En E8, taper la formule suivante : =SERIE.JOUR.OUVRE.INTL(B8; 40; 11; B14:B23),

Le calcul à 60 jours fin de mois consiste à utiliser de nouveau la fonction Excel Fin.Mois() avec le paramètre 2 pour deux mois en plus, afin de considérer les 60 jours de délais et de retourner le dernier jour du mois, ainsi trouvé.

• En E9, saisir donc le calcul suivant : =FIN.MOIS(B9; 2),

Pour fixer la date d'échéance de paiement 30 jours fin de mois, le 10, il suffit d'ajouter 10 jours au dernier jour du mois après le mois de facturation, soit Fin.Mois(date_facture ; 1) + 10. En effet, comme nous l'avons vu à plusieurs reprises, l'addition sur les dates se fait par défaut, sur le nombre de jours.

• Donc, saisir la formule suivante en E10 : =FIN.MOIS(B10; 1) + 10,

En revanche, pour fixer une date d'échéance à 3 mois de plus que celle de la facture, une simple addition ne peut pas être réalisée. Il faut être capable de faire cette somme sur la partie des mois de la date d'émission. C'est pourquoi, nous devons de nouveau exploiter la fonction date() d'Excel, avec les fonctions annee(), mois() et jour() pour décomposer chaque partie.

• En E11, réaliser le calcul de l'échéance à 3 mois comme suit : =DATE(ANNEE(B11); MOIS(B11) + 3; JOUR(B11)),

Le dernier tableau sur la droite permet d'extraire chaque partie de la date d'échéance en nombre ou en texte. Pour les trois premières colonnes, c'est très simple, il suffit d'utiliser les fonctions annee(), mois() et jour() qui retournent respectivement en valeur numérique, l'année d'une date, le mois d'une date et le jour d'une date.

Les colonnes M et N proposent d'afficher respectivement le jour de la semaine de la date d'échéance en chiffre et en texte. La fonction Excel Joursem() retourne le jour de la semaine en chiffre. Pour que le Lundi soit considéré comme le premier jour de la semaine, pour débuter le calcul, il faut lui passer la valeur 2 en second paramètre. Sinon par défaut, le calcul débute en considérant le Dimanche comme le premier jour, comme c'est le cas chez les américains.

Pour retourner le jour de la semaine en texte, nous allons utiliser la fonction texte() comme nous l'avons fait dans le premier exercice. Mais comme il ne s'agit pas de retourner le mois en texte, nous lui passerons cette fois l'argument 'jjjj'.

• En M4, taper la formule suivante : =JOURSEM(E4; 2),
• Puis reproduire le calcul sur les cellules du dessous,
• En N4, saisir le calcul suivant : =TEXTE(E4; 'jjjj'),
• Là aussi, reproduire le calcul sur les cellules du dessous,

Comme vous le constatez, la correspondance est correcte. Sur la première ligne, pour le jeudi, la fonction JourSem retourne 4 pour indiquer qu'il s'agit du quatrième jour de la semaine. La dernière colonne consiste à récupérer la date d'échéance et à personnaliser son format de cellule, afin de ne conserver que l'affichage du jour en texte (jjjj). Les résultats sont proposés par la figure ci-dessous.


Différences entre deux dates en nombres de jours ouvrés et ouvrables
Nous le savons, la différence entre deux dates par défaut, se calcule en nombres de jours. Les fonctions NB.JOURS.OUVRES() et NB.JOURS.OUVRES.INTL() fonctionnent selon le même principe que les fonctions SERIE.JOUR.OUVRE, mais pour calculer l'écart en jours entre deux dates.

• Cliquer sur l'onglet Nombres de jours dates, en bas de la fenêtre pour activer sa feuille,
• En E4, réaliser le calcul de la soustraction entre la date de paiement et la date d'émission, soit =D4-C4,
• Puis le reproduire sur l'ensemble de la colonne, soit jusqu'à E10,

Nous obtenons bien la différence entre les deux dates, calculée en nombre de jours. Il s'agit maintenant de calculer la différence entre ces deux dates, en nombre de jours ouvrés, en colonne F. Comme précédemment, il convient de considérer les potentiels jours fériés sur la période. C'est la raison pour laquelle figure une liste de référence à partir de B12.

• En F4, débuter le calcul avec le nom de la fonction suivi d'une parenthèse ouvrante, comme suit : =NB.JOURS.OUVRES(,
• Sélectionner la date de départ, soit C4, puis taper un point-virgule (;),
• Sélectionner la date de fin, soit D4 et taper un point-virgule (;),
• Sélectionner ensuite la plage de cellules des jours fériés, soit B13:B16,
• Enfoncer la touche F4 du clavier car il s'agit ensuite de reproduire le calcul sans déplacer les bornes de cette plage,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,
• Le reproduire sur l'ensemble de la colonne,

Nous obtenons bien la différence entre les deux dates, en nombre de jours ouvrés et en excluant les jours fériés éventuels sur la période. Fort logiquement cette différence est inférieure à la précédente puisque les jours ouvrés ne comptabilisent pas tous les jours de la semaine. En colonne G, le principe est le même avec la fonction NB.JOURS.OUVRES.INTL(). La seule différence réside dans le troisième paramètre qu'il convient de fixer sur le ou les jours considérés comme le Week End afin de les exclure du décompte.

• En G4, saisir la formule suivante : =NB.JOURS.OUVRES.INTL(C4; D4; 11; $B$13:$B$16),
• Puis reproduire le calcul sur la colonne.

Nous obtenons la différence en jours ouvrables entre les deux dates. Le troisième paramètre réglé à 11 indique que seul le dimanche est considéré comme un jour vaqué. Si bien que le nombre de jours ouvrables séparant les deux dates, est supérieur au nombre de jours ouvrés. Comme précédemment, le quatrième paramètre désigne la plage de cellules des jours fériés que nous figeons, pour pouvoir répliquer le calcul sur les cellules du dessous.


Convertir les heures de nuit et de jour en nombres décimaux
Dans un dernier exercice, nous allons calculer la durée de travail de deux salariés, l'un travaillant de jour et l'autre de nuit. Nous allons voir que la différence est fondamentale. Et nous apprendrons à convertir cette durée au format décimal. Par exemple 07:30, soit 7 heures et 30 minutes au format Heure, correspond à 7,5, soit 7 heures et demi en version décimale.

• Activer la feuille Calcul heures travail en cliquant sur son onglet,
• Cliquer sur la cellule E5 pour calculer la durée de travail par matinée, pour le premier salarié,
• Taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule de l'heure de fin, soit D5,
• Taper le symbole - pour réaliser la soustraction,
• Puis cliquer sur la cellule de l'heure de début, soit C5,
• Valider le calcul par CTRL + Entrée et le reproduire sur l'ensemble de la colonne,

Comme vous le remarquez, la soustraction sur les heures se fait naturellement, comme l'addition d'ailleurs, lorsque le total ne dépasse pas 24. Le calcul de la durée pour les heures de l'après-midi est exactement le même. Nous pourrions le refaire ou encore le recopier.

• Sélectionner la cellule du premier résultat, soit E5,
• La copier par le raccourci CTRL + C par exemple,
• Sélectionner la plage de cellules des durées pour l'après-midi, soit H5:H11,
• Coller le calcul par le raccourci CTRL + V,

Le calcul est non seulement répercuté en adaptant les références des cellules mais aussi reproduit sur l'ensemble de la colonne. Pour calculer le total des heures de la journée, il suffit de faire l'addition de ces deux parties.

• Sélectionner la cellule I5 et taper le symbole = pour débuter le calcul,
• Cliquer sur la durée du matin pour l'inclure dans l'addition, soit E5,
• Taper le symbole + pour réaliser une addition,
• Cliquer sur la cellule de la durée de l'après-midi, soit H5,
• Valider le calcul par CTRL + Entrée et le reproduire sur l'ensemble de la colonne,

L'addition sur les heures fonctionne parfaitement, au même titre que la soustraction, comme nous l'avons constaté précédemment. Il s'agit maintenant de convertir ce résultat en nombre décimal. Les heures ne sont pas des nombres. C'est pourquoi, comme nous l'avions vu dans le critère de l'exercice sur les heures supplémentaires, nous l'avions indiqué entre guillemets, au format Heure. C'est exactement ce que nous allons faire ici, en divisant la durée par une unité horaire, afin de la convertir en décimale.

• Sélectionner la cellule J5 et taper le symbole = pour débuter le calcul,
• Cliquer la cellule de la durée totale pour la désigner, soit I5,
• Taper le slash du pavé numérique (/), pour la division,
• Ouvrir les guillemets en enfonçant la touche 3 en haut du clavier,
• Saisir 1:00 et fermer les guillemets,
• Valider le calcul par CTRL + Entrée et le reproduire sur toute la colonne,

Nous avons en effet réussi à convertir les heures en valeurs numériques décimales. Par exemple, pour 05:45, nous obtenons la valeur décimale 5,75. Les heures sont des valeurs entières. On les retrouve donc à l'identique dans les deux versions. 45 minutes correspondent à 3/4 d'heure et 3/4 vaut en effet 0,75. Donc la conversion est parfaitement réussie.


Pour le deuxième salarié, la problématique est différente. Comme il réalise des heures de nuit, la différence entre l'heure de fin et l'heure de départ, conduirait à un résultat négatif. Sauf que, comme nous l'avons dit, les heures ne sont pas des nombres. Et les heures négatives n'existent pas. Si en E18 vous réalisez la différence entre l'heure de fin et l'heure de début (=D18-C18), vous obtiendrez une erreur.

C'est la fonction Mod() d'Excel, originalement prévue pour retourner le reste d'une division, qui va nous permettre de renvoyer le reste des heures, en considérant une journée de 24 heures.

• Sélectionner la cellule E18 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit Mod(,
• Cliquer sur l'heure de fin, soit D18,
• Taper le symbole - pour la soustraction,
• Cliquer sur la cellule de l'heure de début, soit C18,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,
• Le reproduire sur l'ensemble de la colonne,

Cette méthode nous permet d'obtenir non pas le reste de la division, mais le reste des heures si l'on considère une horloge de 24 heures, Soit 24 - 22 + 5 = 7. Et nous obtenons bien la durée des heures de nuit effectuées par ce deuxième salarié. Le calcul de la dernière colonne est similaire au précédent. Il s'agit de la convertir au format décimal en la divisant par une unité horaire.