Préparez votre certification
Microsoft Office Specialist

Les fonctions de Dates et des Heures

Recevez ce cours au format PDF en envoyant un message ici

Comprendre la sérialisation dans Excel

Les Dates et les Heures sont stockées par Excel sous forme de valeurs numériques. Ainsi, lorsqu'on écrit une Date dans une cellule et que l'on change son Format en Standard, elle se transforme en un nombre entier. Ce nombre représente la valeur réelle de la date et l'écriture qu'on a écrite au début n'est qu'un Format d'affichage, parmi d'autres, comme le montre la capture suivante.

La sérialisation des jours

Pour l'exemple, la date 05/10/1963 correspond au nombre entier 23286 qui représente son numéro de série. Cette valeur sous-jacente représente le numéro du jour représenté par la date depuis une date référentielle qui est le 01/01/1900. Autrement dit, 23289 jours se sont écoulés depuis 01/01/1900 jusqu'au 05/10/1963. Dans la logique de ce raisonnement, le 01/01/1900 désigne le premier jour de la sérialisation des dates dans Excel. La preuve, si on écrit dans une cellule la valeur 1 que l'on met au format Date, on obtient la date de ce premier jour.

Attention ! Si on met la valeur -1 au format Date, on obtient une erreur d'affichage. Ce qui est tout à fait logique puisqu'il n'existe pas de dates négatives, et Excel ne reconnait pas les dates antérieures au 01/01/1900 ! Toutes les dates avant 01/01/1900 et aussi les dates postérieures au 31/12/9999 ne sont pas gérées et sont considérées comme du texte. Excel ne reconnait que les dates qui entrent dans l'intervalle [01/01/1900;31/12/9999].

Avant de continuer regardez d'abord la vidéo

La représentation des heures

Un jour forme donc l'unité de temps dans Excel. Une Heure représente une fraction du jour, elle correspond au 1/24^ème du jour, soit 0,0416666666666667. 12 heures correspondent donc à 12 x 0,0416666666666667 = 0,5 soit la moitié d'un jour. Si dans une cellule, on écrit 0,5 que l'on convertit au format Heure, on obtient 12:00:00 (midi). Le tableau suivant montre quelques exemples.

Explication de ces affichages :

• 0,5 x 24 = 12
• 0,3333333333 x 24 = 8
• 0,75 x 24 = 18
• 0,8333333333 x 24 = 20

Prenons un exemple avec les heures, les minutes et les secondes pour voir comment se fait la conversion d'un nombre décimal de l'affichage format Standard vers le format Heure.

Le nombre décimal 0,392234337 est une fraction du jour. Sachant que le jour est formé de 24 heures, on va donc le multiplier par 24 pour trouver le nombre d'heures contenues dans cette fraction.

24 x 0,392234337 = 9,413624088

La partie entière de ce résultat, 9 indique le nombre d'heures et sa partie décimale, 0,413624088 représente une fraction d'une heure. Étant donné que 1 Heure = 60 minutes, on va multiplier cette partie décimale par 60 pour trouver le nombre de minutes.

60 x 0,413624088 = 24,81744528

On prend encore la partie entière, 24 qui représente le nombre de minutes. La partie décimale, 0,81744528, c'est encore une fraction d'une minute.

Pour trouver le nombre des Secondes, on va procéder de la même manière, sachant que 1 minute = 60 secondes

60 x 0,81744528 = 49,0467168

La partie entière de ce nouveau résultat, 49, est le nombre de secondes.

Récapitulons. Le nombre décimal 0,392234337 saisi dans la cellule A4 représente 9 heures, 24 minutes et 49 secondes et c'est bien ce qui est affiché dans la cellule B4 au format Heure.

Si j'immortalise cet instant même où je suis en train des rédiger ce cours, 26/04/2021 à 09:04:40 GMT, et que je change son affichage au format Standard, j'obtiens le nombre réel 44312,3782416667, qui signifie que depuis 01/01/1900 jusqu'à ce moment, sont écoulés 44312 jours et 0,3782416667 jour qui correspond à 09h 04min 40sec.

Avant de continuer regardez d'abord la vidéo

Les différents formats d'affichage

Un grand nombre de formats d'affichage est disponible sous Excel, que l'on utilise selon le besoin et selon la précision d'affichage demandée. Ainsi, une même valeur numérique peut être affichée selon différents formats qui se trouvent dans la galerie Format de nombre dans le groupe Nombres, ou bien dans la boite de dialogue Format de cellule accessible en cliquant sur la ligne Autres formats numériques en bas de la galerie, cette boite propose davantage de formats.

Dans la boite de dialogue Format de cellule, on peut utiliser des formats proposés par Excel et on peut aussi en créer d'autres personnalisés.

Pour créer un format personnalisé il suffit d'écrire dans la zone Type, de la boite de dialogue Format de cellule, le modèle du format désiré en respectant la syntaxe suivante :

• jjjj (4 j) : pour écrire le nom complet du jour (lundi, mardi, etc.)
• jjj (3 j) : pour écrire le nom du jour en abrégé (lun, mar, mer, etc.)
• jj (2 j) : pour écrire le numéro du jour en 2 chiffre même s'il est formé d'un seul chiffre (01, 02, etc.)
• j (un seul j) : pour écrire le numéro du jour en un seul chiffre s'il est formé d'un seul chiffre (8, 9, 10, 11, etc.)
• Taper le symbole de séparation : le slash ou bien le tiret ou autre
• mmmm (4 m) : pour écrire le nom du mois complet en toute lettre
• mmm (3 m) : pour écrire le nom du mois en abrégé (3 ou 4 lettres)
• mm (2 m) : pour écrire le numéro du mois en 2 chiffres même s'il est formé d'un seul chiffre (01, 02, etc.)
• m (un seul m) : pour écrire le numéro du mois en un seul chiffre s'il est formé d'un seul chiffre (8, 9, 10, 11, 12)
• aaaa (4 a) : pour écrire le numéro de l'année en 4 chiffres
• aa (2 a) : pour écrire le numéro de l'année en 2 chiffres
• hh (2 h) : pour écrire les heures en 2 chiffres (08, 09, 10, 11, 12, 13, 14, etc.)
• h (1 seul h) : pour écrire les heures inférieures à 10 en 1 chiffre
• Utiliser les deux points (:) comme séparateur
• mm (2 m) : pour écrire les minutes en 2 chiffres (08, 09, 10, 11, 12, 13, 14, etc.)
• m (1 seul m) : pour écrire les minutes inférieures à 10, en 1 chiffre
• ss (2 s) : pour écrire les secondes en 2 chiffres (08, 09, 10, 11, 12, 13, 14, etc.)
• s (1 seul s) : pour écrire les secondes inférieures à 10, en 1 chiffre

Avant de continuer regardez d'abord la vidéo

L'addition et la soustraction des Dates

Comme c'est dit précédemment, les dates et les heures sont considérées comme des nombres particuliers, par conséquent, les opérations arithmétiques qu'on peut leurs appliquer sont limitées. Par exemple, les opérations suivantes sont inadmissibles :

• Additionner une date à une autre
• Multiplier une date par une autre
• Diviser une date par une autre

En revanche, rien n'empêche de faire une opération d'addition ou de soustraction entre un nombre et une date, ou encore soustraire une date d'une autre qui lui est postérieure.

Avant de continuer regardez d'abord la vidéo

Les fonctions de gestion des Dates

Les fonctions de gestion des dates, sont faites pour faciliter les manipulations complexes de ce type de données. Excel dispose de toute une panoplie de fonctions, faites pour cela, qui sont classées dans la catégorie DateHeure dans le groupe Bibliothèque de fonctions de l'onglet FORMULES.

Pour utiliser une de ces fonctions, il suffit de cliquer sur son nom, dans la galerie, puis de la paramétrer, si besoin est, en entrant les arguments requis.

Deux premières fonctions pour commencer

Ces fonctions extraient la date système de l'ordinateur. La première est la fonction AUJOURDHUI() qui affiche la date du jour en cours au format jj/mm/aaaa, elle n'exige aucun paramètre.

Raccourci clavier pour insérer la date du jour

La deuxième est la fonction MAINTENANT(). Elle affiche la date du jour suivi de l'heure du jour au format jj/mm/aaaa hh:mm. Elle aussi ne requière aucun argument.

Raccourci clavier pour insérer l'heure actuelle

Le résultat de ces deux fonctions est dynamique et se met à jour à chaque fois que la feuille est recalculée ou qu'on ouvre le classeur.

Avant de continuer regardez d'abord la vidéo

Les fonctions JOURSEM() et DATE()

La fonction JOURSEM() renvoie le numéro du jour dans la semaine selon que la semaine commence le Dimanche ou bien le Lundi ou autre jour.

JOURSEM() requiert un argument obligatoire qui est une date entre guillemets ou bien la référence d'une cellule contenant une date, et un argument type_retour facultatif qui définit comment les jours de la semaine sont numérotés.

Syntaxe :

=JOURSEM(date;type_retour)

Avant de continuer regardez d'abord la vidéo

La fonction DATE() permet de reconstituer une date à partir de trois nombres reçus séparément et qui forment ses arguments.

1. année : nombre compris entre 1900 et 9999 inclus
2. mois : nombre compris entre 1 et 12 inclus
3. jour : nombre compris entre 1 et 31 inclus

Ces arguments peuvent être des nombres passés en dur, ou bien des références de cellules contenant des nombres. Si l'un des arguments n'est pas valide, une valeur non numérique par exemple, la fonction revoie un message d'erreur.

Syntaxe :

=DATE(année;mois;jour)

Avant de continuer regardez d'abord la vidéo

Fonctions d'extraction

Les fonctions d'extraction permettent d'extraire, à partir d'un numéro de série passé en argument, l'année, le mois, le jour, les heures, les minutes et les secondes.

• ANNEE() pour extraire l'année
• MOIS() pour extraire le mois
• JOUR() pour extraire le jour
• HEURE() pour extraire l'heure
• MINUTE() pour extraire la minute
• SECONDE() pour extraire la seconde

Avant de continuer regardez d'abord la vidéo

Fonctions de calcul

Avant de voir quelques fonctions de calculs, sachez que les opérations sur les dates se basent sur les valeurs numériques de celles-ci, c'est à dire leurs numéros de série. Ces valeurs sont affichées par la fonction DATEVAL().

DATEDIF() pour calculer la différence entre deux dates

Dans les premiers paragraphes, on a vu que l'on peut calculer la différence entre deux dates, en utilisant une simple opération de soustraction. Cette opération donne le résultat en nombre de jours qui séparent les deux dates.

La fonction DATEDIF() est beaucoup plus puissante qu'une soustraction entre les dates. Elle calcule la différence entre deux dates avec une grande variété dans les résultats qu'elle renvoie. La fonction nécessite trois arguments obligatoires qui sont : la date_début, la date_fin et le type_retour.

Syntaxe :

=DATEDIF(date_début;date_fin;type_retour)

Le troisième paramètre, type_retour, définit le type du résultat retourné par la fonction. Il peut prendre six valeurs différentes qu'on doit mettre entre guillemets :

• "y" : La lettre "y", pour Year (Année) en majuscule ou en minuscule, pour afficher la différence entre les deux dates en nombre d'années
• "m" : La lettre "m", pour Month (Mois) en majuscule ou en minuscule, pour afficher la différence entre les deux dates en nombre de mois
• "d" : La lettre "d", pour Day (Jour) en majuscule ou en minuscule, pour afficher la différence entre les deux dates en nombre de jours
• "ym" : Pour afficher le nombre de mois après avoir soustrait le nombre des années
• "md" : Pour afficher le nombre de jours après avoir soustrait le nombre des mois
• "yd" : Pour afficher le nombre de jours après avoir soustrait le nombre des années

La fonction DATEDIF() est utilisée, par exemple, pour calculer les anciennetés des employés comme c'est montré dans la capture suivante.

Dans la cellule D4, on a utilisé la fonction DATEDIF() avec comme premier argument la date d'embauche qui se trouve dans la cellule C4 et le deuxième argument, la cellule D1 qui contient la date du jour encours. Pour le troisième argument, on a utilisé la lettre "y" pour obtenir l'ancienneté en nombre d'années. À la fin on a ajouté, par le biais de l'opérateur de concaténation &, le mot " ans", pour plus de clarté.

Pour plus de précision, on aimerait avoir l'ancienneté en nombre d'années, mais aussi en nombre de mois et en nombre de jours. On va donc faire usage de la fonction DATEDIF() trois fois de suite en jouant sur le troisième paramètre de la fonction.

• Une première fois pour obtenir le nombre d'années avec la valeur "y", comme c'est déjà fait
• Une deuxième fois pour calculer le nombre de mois après avoir soustrait le nombre des années, on va utiliser la valeur "ym"
• Pour la troisième fois, on va utiliser la valeur "md" pour avoir le nombre de jours après avoir soustrait le nombre des mois.

Toutes ces expressions vont être concaténées l'une à la suite de l'autre à l'aide de l'opérateur &, sans oublier d'intercaler les mots " ans", " mois" et " jours" pour la clarté du rendu. La capture suivante montre la syntaxe complète de l'expression.

Le résultat est une ancienneté calculée au jour près, pour chaque employé comme le montre l'extrait suivant

Avant de continuer regardez d'abord la vidéo

Deux autres fonctions qui calculent le nombre de jours entre deux dates :

• JOURS() : Renvoie le nombre de jours entre deux dates
• JOURS360() : Renvoie le nombre de jours entre deux dates en considérant que tous les mois de l'année sont formés de 30 jours. C'est une situation qu'on utilise parfois en comptabilité.

Syntaxe :

=JOURS(date_fin;date_début)

=JOURS360(date_début;date_fin)

Avant de continuer regardez d'abord la vidéo

La fonction FIN.MOIS() et la fonction MOIS.DECALER()

Ces deux fonctions permettent de décaler une date vers l'avant ou vers l'arrière, d'un certain nombre de jours ou de mois. Elles sont utilisées pour définir la date de fin d'un délai ou d'une échéance par exemple.

La fonction FIN.MOIS() renvoie, selon la valeur de son deuxième paramètre, le numéro de série (qu'il faudra, si nécessaire, afficher sous forme de date) du dernier jour du mois de la date référence passée en paramètre date_début, ou bien le numéro de série du dernier jour du mois, quelques mois après la date référence, ou bien avant la date référence.

Syntaxe :

=FIN.MOIS(date_début;mois)

Le premier paramètre date_début, représente la date référence. Il peut être une date écrite entre guillemets, ou bien la référence d'une cellule contenant une date.

Le deuxième paramètre mois, indique le nombre de mois de décalage par rapport à la date référence. Il prend une valeur entière qui peut être négative, positive ou nulle.

• Si le paramètre mois est égal à 0, la fonction renvoie la date du dernier jour du mois de la date référence
• Si le paramètre mois est négatif (-n), la fonction renvoie la date du dernier jour du n^ième mois avant la date référence
• Si le paramètre mois est positif (n), la fonction renvoie la date du dernier jour du n^ième mois après la date référence

Avant de continuer regardez d'abord la vidéo

La fonction MOIS.DECALER(), comme son nom l'indique, décale une date d'un certain nombre de mois en avant ou en arrière.

Syntaxe :

=MOIS.DECALER(date_début;mois)

Le premier paramètre date_début, représente la date référence. Il peut être une date écrite entre guillemets, ou bien la référence d'une cellule contenant une date.

Le deuxième paramètre mois, indique le nombre de mois de décalage par rapport à la date référence. Il prend une valeur entière qui peut être positive pour faire avancer la date, ou bien négative pour la reculer.

Avant de continuer regardez d'abord la vidéo

Calculer le nombre de jours ouvrés entre deux Dates

Les jours ouvrés sont les jours de la semaine qui sont effectivement travaillés, généralement du Lundi au vendredi, Samedi et Dimanche sont des jours de repos, ce qui fait 5 jours ouvrés dans une semaine normale. En plus de ces jours du Week end, il peut y avoir des jours fériés non ouvrés qu'on doit prendre en considération.

La fonction NB.JOURS.OUVRES() permet de compter les jours ouvrés entre deux dates passées en paramètre. Sa syntaxe est formée de deux arguments obligatoires et un troisième facultatif.

=NB.JOURS.OUVRES(date_début;date_fin;jours_fériés)

Le troisième argument forme la plage de cellules contenant la liste des jours fériés. Si cet argument est omis, la fonction renvoie le nombre des jours ouvrés (Lundi, Mardi, Mercredi, Jeudi et Vendredi) compris dans l'intervalle de temps. Si ce troisième argument est défini, la fonction exclut en plus des jours du Week end, les jours fériés contenus dans la liste.

Généralement, les jours du Week end sont le Samedi et le Dimanche. Selon la nature du travail, il y a des entreprises qui ferment d'autres jours de la semaine. Pour s'adapter à ce mode de travail, Excel propose une autre fonction NB.JOURS.OUVRES.INTL() qui donne la possibilité de définir ces jours de repos.

=NB.JOURS.OUVRES.INTL(date_début;date_fin;week_end;jours_fériés)

Dans le troisième argument, week_end, on choisit dans la liste qui s'ouvre, le jour ou bien les jours qui correspondent aux jours de repos hebdomadaire de l'entreprise, et dans le quatrième argument on définit la liste des jours fériés.

Prenons l'exemple d'une entreprise qui travaille tous les jours de la semaine sauf le Lundi, et qui voudrait compter les jours ouvrés pendant une période afin de calculer les salaires de ses employés. Après avoir renseigné les deux premiers paramètres, on arrive à l'argument week_end pour lequel on va choisir dans la liste, la ligne 12-Lundi uniquement, puis on continue le paramétrage de la fonction en définissant la liste des jours fériés. Après validation, on obtient 15 jours comme résultat, après exclusion des 3 Lundis (jours de repos) et le Vendredi 01 mai 2020 (jour férié).

Calculer une date après un certain nombre de jours ouvrés

Dans le monde des affaires, il est très utile de savoir à quelle date un projet sera achevé ou une tâche sera finie. Excel met à disposition deux fonctions à cet égard qui sont SERIE.JOUR.OUVRE() et SERIE.JOUR.OUVRE.INTL(). Ces deux fonctions, en sachant le nombre de jours nécessaires pour l'accomplissement du projet, renvoient le numéro de série de la date de son achèvement en prenant en considération les jours de repos et les jours fériés.

Syntaxe

=SERIE.JOUR.OUVRE(date_début;nombre_jours;jours_fériés)

=SERIE.JOUR.OUVRE.INTL(date_début;nombre_jours;week_end;jours_fériés)

La fonction SERIE.JOUR.OUVRE est à utiliser si les jours de repos sont les jours du week end normal (Samedi et Dimanche).

La fonction SERIE.JOUR.OUVRE.INTL est à utiliser si les jours de repos sont autres que le Samedi et Dimanche.

Opérations sur les Heures

Rappelons que dans Excel l'unité de calculer le temps c'est 1 jour (24 heures), ce qui fait que 1 heure est une fraction du jour. Par conséquence, une heure est représentée par un nombre réel compris entre 0 et 1. Exemple 06:00 (un quart du jour) est représenté par le nombre réel 0,25. Les opérations de calcul sur les heures sont donc basées sur ce fait avec les restrictions suivantes :

• Veiller à ne pas obtenir un résultat négatif. Excel n'affiche pas les heures négatives
• Pas d'opération de multiplication ni de division entre les heures. Les opérations du genre 05:32 x 12:15 ou bien 12:15 / 05:32 n'ont aucun sens.

Les opérations de conversion

Soustraire les heures

La soustraction entre les heures se fait généralement pour calculer le nombre d'heures écoulées entre une Heure de début et une Heure de fin. Une première méthode de faire serait d'effectuer une simple soustraction entre l'heur de début et l'heure de fin puis de multiplier par 24. Cette façon de faire marche bien dans le cas où l'heure de fin est supérieure à l'heure de début, mais si un employé commence son travail à 23:00 et finit à 06:00 du matin, on obtient un résultat erroné comme le montre la ligne 4 de la capture suivante.

Pour pallier à ce problème, on utilise le fonction MOD() de la manière suivante :

=MOD(date_fin - date_début;1) x 24

Pour la fonction MOD(), on passe deux arguments : le premier est la différence entre la date de début et la date de fin et le deuxième argument est la valeur 1. La multiplication par 24 (x 24) est ajoutée pour convertir le rendu de la fonction MOD() en nombre réel (on obtient 4,5 au lieu de 04:30). Cette expression calcule à la fois le nombre d'heures travaillées pour les horaires de jour comme pour les horaires de nuit.

Additionner les heures

L'addition des heures peut se faire en utilisant l'opérateur d'addition (+) ou bien en faisant appel à la fonction SOMME().

Mettons en œuvre cette opération pour calculer le cumul des heures travaillées par un employé au cours d'une semaine. Dans le tableau ci-dessous on a, dans les lignes Heure début et Heure fin, les horaires d'entrée et de sortie pour cet employé pour chaque jour de la semaine, et puis dans la ligne Nombre d'heures, le nombre d'heures travaillées au cours de la journée. Dans la cellule E7, se trouve le cumul des heures travaillées pendant toute la semaine, calculé à l'aide de la fonctions SOMME().

Pour l'instant, le résultat est correct puisque 06:30 + 06:00 + 01:00 + 03:30 + 06:00 font bien 23:00. Mais si on modifie un des horaires, par exemple Heure fin de Mercredi, on met 17:00 au lieu de 11:00, le cumul affiché est de 05:00 comme le montre la capture suivante.

5 heures pour toute une semaine !!, alors que, rien que pour Lundi et Mardi, on a déjà 12:30 de travail. Certainement, il y a une erreur quelque part.

En fait, il ne s'agit pas ici d'une erreur de calcul, Excel a bien calculé le total des heures travaillées ; il s'agit plutôt d'une erreur de format d'affichage. Excel réinitialise l'affichage à 0, à chaque fois que la valeur dépasse 24:00. Pour corriger ce comportement, on doit modifier le format de la cellule E7 en choisissant le format [hh]:mm, les crochets signifient : Ne pas réinitialiser les heures une fois la barre des 24 heures dépassée. Si ce format n'est pas proposé dans la liste, on peut le saisir à la main dans la zone Type.

Suite à cette rectification du format d'affichage, le cumul est maintenant affiché correctement, on a bien le total des heures travaillées 06:30 + 06:00 + 07:00 + 03:30 + 06:00 = 29:00.

Les fonctions de gestion des Heures

En plus des fonctions d'extraction HEURE(), MINUTE() et SECONDE() vues au début de ce cours, Excel dispose de deux autres fonctions qui sont TEMPS() et TEMPSVAL().

La fonction TEMPS()

La fonction TEMPS() reconstitue l'heure à partir de trois nombres entiers représentant les nombres des heures, des minutes et des secondes, qui lui sont passés comme arguments.

=TEMPS(nombre1;nombre2;nombre3)

• L'argument nombre1 représente le nombre des heures, compris entre 0 (zéro) et 32767
• L'argument nombre2 représente le nombre des minutes, compris entre 0 (zéro) et 32767
• L'argument nombre3 représente le nombre des secondes, compris entre 0 (zéro) et 32767

La fonction renvoie un numéro de série (compris entre 0 et 0,99988426) formaté au format Heure personnalisé AM/PM.

Si l'un des arguments n'est pas disponible, sa place doit rester vide, par exemple, si on dispose uniquement du nombre de secondes 3562 que l'on désire mettre au format heure, l'expression sera :

La fonction TEMPSVAL()

Lors de l'importation des données à partir d'un autre logiciel ou bien à partir du Web, les heures sont importées parfois au format texte, ce qui empêche de les exploiter sous Excel pour effectuer des tris, des opérations de calcul, des comparaisons, etc. Pour se permettre les opérations de traitement des heures, on doit d'abord les transformer en numéro de série horaire (valeurs numériques) à l'aide de la fonction TEMPSVAL(). Cette fonction convertit les données heures sous forme de texte, en un numéro de série compris entre 0 et 0,99988426, auquel on donnera par la suite le format horaire désiré.

Recevez ce cours au format PDF en envoyant un message ici