MC - Excel Calculs Sur Les Dates Et Les Heures

Excel : calculs sur les dates 1/8

Microsoft Excel enregistre les dates sous la forme de nombres séquentiels appelés numéros de série et les heures sous la forme de fractions décimales parce que l'heure est considérée comme une partie de la journée. Les jours et les heures sont des valeurs qui peuvent être ajoutées, soustraites et incluses dans d'autres calculs. Par exemple, pour calculer la différence entre deux dates, vous pouvez soustraire une date d'une autre date. Vous pouvez afficher une date ou une heure sous la forme d'un numéro de série ou d'une fraction décimale en affectant le format Standard à la cellule contenant la date ou l'heure. Note : pour appliquer rapidement le format de nombre Standard à une cellule,

utilisez le raccourci-clavier CTRL+R Microsoft Excel 97 gère deux systèmes de date : le calendrier depuis 1900 et le calendrier depuis 1904. Le calendrier par défaut de Microsoft Excel 97 est le calendrier depuis 1900. Le tableau suivant affiche la première et la dernière date de chaque calendrier et le numéro de série associé à chaque date. Calendrier Première date Dernière date 1900 1er Janvier 1900

(numéro de série 1) 31 Décembre 9999 (numéro de série 2958525)

1904 2 Janvier 1904 (numéro de série 1)

31 Décembre 9999 (numéro de série 2957063)

Remarque : Lorsque vous tapez une date dans Microsoft Excel 97 en n'indiquant

que deux chiffres pour l'année, Microsoft Excel 97 l'interprète de la façon suivante :

• Si vous tapez une paire de chiffres comprise entre 00 et 29, vous faites référence à une année comprise entre 2000 et 2029. Par exemple, si vous tapez 28/05/19, Microsoft Excel prend en compte le 28 Mai 2019.

• Si vous tapez une paire de chiffres comprise entre 30 et 99, vous faites référence à une année comprise entre 1930 et 1999. Par exemple, si vous tapez 28/05/91, Microsoft Excel prend en compte le 28 Mai 1991.

Calculs arithmétiques sur les dates Nombre de jours écoulés entre deux dates d1 et d2 Formule générique : =d1-d2 Nouvelle date obtenue en ajoutant ou en retirant un nombre de jours donnés N à une date d1 Formule générique : =d1+N la nouvelle date obtenue sera située N jours en avant dans le futur =d1-N la nouvelle date obtenue sera située N jours en arrière dans le passé Pour les calculs arithmétiques sur les dates, les valeurs d1 et d2 peuvent être : • des références à des cellules contenant une date • des dates fixées (dans ce cas, elles doivent être encadrées par des guillemets) • des expressions de calculs renvoyant une date


Microsoft Excel met à votre disposition plusieurs fonctions permettant de gérer des dates et des heures: AUJOURDHUI() Retourne la date du jour gérée par la machine (cette date est d'ailleurs appelée la date système). Cette fonction ne nécessite aucun argument. Exemple : =AUJOURDHUI() renvoie le 30/09/99 si la date du jour est le 30 septembre 1999 MAINTENANT() Retourne la date et l'heure en cours gérées par la machine. Cette fonction ne nécessite aucun argument. Exemple :

=MAINTENANT() renvoie 30/09/99 15:00 si la date du jour est le 30 septembre 1999 et si l'heure en cours est 15h00

Pour n'afficher que la date ou l'heure, appliquer un format d'affichage de type date ou heure, via la commande Cellules… du menu Forma t, onglet Nombre. Excel vous permet de décomposer une date en ses trois parties élémentaires : le jour, le mois et l'année, à l'aide des fonctions suivantes : JOUR(N° de série) MOIS(N° de série) ANNEE(N° de série) Ces trois fonctions ne nécessitent qu'un seul argument appelé de façon générique N° de série . Ce N° de série n'est ni plus ni moins que le nombre de jours écoulés depuis le 1er janvier 1900. Mais vous n'aurez pratiquement jamais à utiliser cette notation, il vous suffira seulement d'indiquer le N° de série sous la forme, plus compréhensible, d'une date écrite dans le format traditionnel JJ/MM/AA, charge à Excel de convertir cette date en nombre de jours écoulés depuis le 1er janvier 1900. Ainsi, l'argument N° de série peut être : • une référence de cellule contenant la date à utiliser • une date fixe au format JJ/MM/AA, mais dans ce cas, elle doit être encadrée par

des guillemets • un nombre entier (qui sera donc considéré comme un nombre de jours écoulés

depuis le 1er janvier 1900) • une expression de calcul renvoyant l'une ou l'autre des valeurs ci-dessus (dans

ce cas, vous ne devez pas placer le signe = devant cette expression) Exemple : Soit la cellule A1 contenant une date quelconque (soit cette date a été saisie, soit cette date a été calculée par une formule appropriée), par exemple le 30/09/1999 : Cellule Formule A2 =JOUR(A1) renvoie le n° du jour de la date stockée en A1, soi t ici 30


A3 =MOIS(A1) renvoie le n° du mois de la date stockée en A1, soi t ici 9 A4 =ANNEE(A1) renvoie le n° de l'année de la date stockée en A1, soit ici 1999 On pourrait également écrire les formules comme ci-dessous : Cellule Formule A2 =JOUR("30/9/99") A3 =MOIS("30/9/99") A4 =ANNEE("30/9/99") Si la date du jour est le 30/9/99, on aurait pu également utiliser les formules suivantes : Cellule Formule A2 =JOUR(AUJOURDHUI()) A3 =MOIS(AUJOURDHUI()) A4 =ANNEE(AUJOURDHUI()) DATE(Année;Mois;Jour) Permet de construire une date au format JJ/MM/AA à l'aide de ses trois composantes. Cette fonction nécessite trois arguments Année, Mois et Jour qui représentent respectivement l'année, le mois et le jour à utiliser pour construire la date correspondante. Ils peuvent être des valeurs fixes numériques, des références de cellules contenant ces valeurs fixes numériques ou bien des expressions de calcul (sans le signe égal) renvoyant une valeur numérique. Exemples : =DATE(1999;9;30) renvoie la date 30/09/99

=DATE(A1;A2;A3) renvoie la date 14/05/89 si les valeurs 1989, 5 et 14 sont respectivement contenues dans les cellules A1, A2 et A3.

JOURSEM(N° de série;Type) Retourne le n° du jour dans la semaine correspondan t au jour de la date fournit en argument. Cette fonction nécessite deux arguments : N° de série qui représente une valeur de type Date (valeur fixe, référence de cellule contenant une date ou expression de calcul renvoyant une valeur de type date) et Type , qui représente un code numérique à choisir parmi les valeurs suivantes : Si Type = 1 : Retourne un n° de jour compris entre 1 et 7, dimanc he étant considéré

comme le premier jour de la semaine et samedi le dernier jour. Si Type = 2 : Retourne un n° de jour compris entre 1 et 7, lundi étant considéré

comme le premier jour de la semaine et dimanche le dernier jour. Si Type = 3 : Retourne un n° de jour compris entre 0 et 6, lundi étant considéré

comme le premier jour de la semaine et dimanche le dernier jour. Exemples (le 30/9/1999 est un jeudi) :


=JOURSEM("30/9/99";1) retourne la valeur 5 car jeudi est considéré comme étant le 5ème jour de la semaine avec la valeur de Type égale à 1.

=JOURSEM("30/9/99";2) retourne la valeur 4 car jeudi est considéré comme étant le 4ème jour de la semaine avec la valeur de Type égale à 2.

=JOURSEM("30/9/99";3) retourne la valeur 3.


Exercices sur les dates : Connaître la date d'échéance d'une facture selon le délai de paiement accordé : Soit d1 une date de facture quelconque. Paiement à 15 jours date de facture : Signification : la facture sera réglée 15 jours après la date de facture Formule : =d1+15 D'une façon générique, paiement à x jours date de facture : Formule : =d1+x Paiement fin de mois : Signification : la facture sera réglée à la fin du mois en cours La fin du mois est difficile à déterminer directement par calculs (il peut s'agir d'un mois à 30 ou 31 jours, voire à 28 ou 29 jours si la facture est émise pendant le mois de février). En revanche, il est très facile de connaître le début d'un mois puisque celui-ci commence toujours à 1. Partant de cette constatation, il est alors facile de déterminer la fin du mois en cours en se positionnant au début du mois suivant et en enlevant ensuite 1 jour à la date obtenue : ainsi, vous êtes sûr et certain d'être positionné à la fin du mois en cours. Toute la difficulté consiste donc à créer la date correspondant au début du mois suivant. Solution : utiliser la fonction DATE() Formule générique : =DATE(ANNEE(d1);MOIS(d1)+1;1)-1 Paiement à 30 jours fin de mois : Signification : on compte 30 jours de plus par rapport à la date de facture et on se reporte ensuite à la fin du mois obtenu. Formule : =DATE(ANNEE(d1+30);MOIS(d1+30)+1;1)-1 D'une façon générique, paiement à x jours fin de mois Formule générique: =DATE(ANNEE(d1+x);MOIS(d1+x)+1;1)-1


Paiement à 30 jours le 10 : Signification : on compte 30 jours de plus par rapport à la date de facture et on se reporte ensuite au 10 du mois en cours ou au 10 du mois suivant si la nouvelle date obtenue est située au-delà du 10 du mois en cours. Formule : =SI(JOUR(d1+30)>10;DATE(ANNEE(d1+30);MOIS(d1+30)+1; 10);DATE(ANNEE(d1+30);MOIS(d1+30);10))

OU =SI(JOUR(d1+30)<=10;DATE(ANNEE(d1+30);MOIS(d1+30);1 0);DATE(ANNEE(d1+30);MOIS(d1+30)+1;10))

OU =DATE(ANNEE(d1+30);SI(JOUR(d1+30)>10;MOIS(d1+30)+1; MOIS(d1+30));10)

OU =DATE(ANNEE(d1+30);MOIS(d1+30)+SI(JOUR(d1+30)>10;1; 0);10) D'une façon générique, paiement à x jours le y : Formule générique : =DATE(ANNEE(d1+x);MOIS(d1+x)+SI(JOUR(d1+x)>y;1;0);y )


Autres fonctions de calculs sur les dates Les fonctions qui suivent sont des fonctions complémentaires qui ne seront disponibles dans Excel que si la macro complémentaire Utilitaires d'analyse est préalablement installée. Pour cela, suivez la procédure d'installation suivante : Cliquez la commande Macro complémentaire… du menu Outils ; la boîte de dialogue suivante s'affiche alors : Installez l'option Utilitaires d'analyse en la cochant ou désinstallez-la en la décochant. Fermez ensuite la boîte de dialogue. Consultez la liste des fonctions en cliquant sur la commande Fonction… du menu Insertion. Sélectionnez la catégorie Dates et Heures : de nouvelles fonctions sont visibles, dont : FIN.MOIS MOIS.DECALER NB.JOURS.OUVRES

NO.SEMAINE SERIE.JOUR.OUVRE

La fonction cachée d’Excel Il existe dans Excel une fonction qui permet de calculer une différence entre deux dates et de renvoyer cette différence sous la forme d'un nombre de jours, de mois ou d'années. Cette fonction fait partie des fonctions cachées d’Excel, à savoir qu’elle n’est documentée ni dans l’aide en ligne du logiciel ni dans le manuel d’utilisation. Syntaxe de cette fonction DATEDIF(d1;d2;"Type de différence") d1 et d2 sont des dates ou des références à des dates (n° d e série, valeur littérale de type date, cellule contenant une donnée de type date ou bien encore une expression de calcul renvoyant une date) De plus, d1 doit être une date antérieure à d2 Type de différence est un code (saisis entre guillemets) qui indique à Excel dans quelle unité de temps calculer la différence entre les deux dates d1 et d2 Les valeurs possibles sont :

• d pour obtenir une différence en nombre de jours • m pour obtenir une différence en nombre de mois • y pour obtenir une différence en nombre d'années • ym pour obtenir le nombre de mois restants après avoir soustrait les

années • md pour obtenir le nombre de jours restants après avoir soustrait les mois

Exemples =DATEDIF("15/5/1967";AUJOURDHUI();"y") renvoie 33 en date du 30/11/2000 (cette formule est idéale pour déterminer l'âge d'une personne ou pour effectuer un calcul à la date anniversaire d'un événement donné). =DATEDIF("15/5/1967";"14/5/2000";"y") renvoie 32 (Cette fonction renverrait 33 à partir du 15/5/2000 jusqu'au 14/5/2001)


=DATEDIF("15/5/1967";"30/11/2000";"m") renvoie 402 (402 mois se sont écoulés entre ces deux dates) =DATEDIF("15/5/1967";"30/11/2000";"d") renvoie 12253 (12253 jours se sont écoulés entre ces deux dates)

Documents

MC - Excel Calculs Sur Les Dates Et Les Heures