[Ballajack] Les fonctions de gestion de dates dans Excel

Généralités sur les dates dans Excel

Dans Excel, les dates sont en fait des nombres appelés numéros de série. Par exemple, le numéro de série représentant le 24 février 2015 est 42 059. Pour retrouver le numéro de série d'une date, il suffit de saisir la date en question puis d'appliquer à la cellule un format "standard". Les numéros de série sont séquentiels, autrement dit, le 25 février 2015 est représenté par 42 060 (42 059 + 1). Ce mode de représentation numérique d'Excel facilite par exemple les opérations de comparaison de dates et les calculs. Par défaut, le 1er janvier 1900 est le numéro de série 1 et le 1er janvier 2008 est le numéro de série 39 448 parce que 39 447 jours se sont écoulés depuis le 1er janvier 1900.

Attention : la version Excel pour Mac utilise une autre base de calendrier qui débute au 1er janvier 1904.

La plus connue des fonctions de manipulation de date est certainement AUJOURDHUI() qui renvoie automatiquement la date du jour (à utiliser dans la formule =AUJOURDHUI()). Mais prenez garde, car il s'agit d'une fonction avec un résultat dynamique : si vous rouvrez le classeur le lendemain, n'espérez pas retrouver la date de la veille.

La fonction DATE

La fonction DATE permet de faire la même chose que précédemment, mais à l'aide d'une fonction qui accepte des paramètres. En effet, cette fonction renvoie le numéro de série séquentiel qui représente une date passée en paramètres (entre parenthèses) sous la forme de trois nombres : année, mois, jour. Par exemple, la formule =DATE(2015;02;24) renvoie la valeur 42 059 qui correspond au numéro de série du 24/02/2015. La fonction DATE est particulièrement utile lorsque les informations année, mois et jour sont fournies séparément dans diverses cellules de la feuille de calcul. Par exemple, une feuille de calcul peut contenir des dates dans un format non directement reconnu par Excel, tel 20081208 (format AAAAMMJJ). On peut alors combiner la fonction DATE avec trois autres fonctions de texte pour obtenir sous forme de date la valeur 08/12/2008. En supposant que la valeur 20081208 soit saisie en cellule A1, la formule sera :

=DATE(GAUCHE(A1;4);STXT(A1;5;2);DROITE(A1;2))

La fonction GAUCHE(A1;4) récupère les quatre premiers chiffres (l'année de la date). La fonction STXT(A1;5;2) récupère deux chiffres à partir du cinquième chiffre (soit le mois sur deux chiffres). Enfin, la fonction DROITE(A1;2) récupère les deux chiffres de droite (le jour).

À savoir : Années sur deux ou quatre chiffres ? La plupart des fonctions acceptant un paramètre "Année" acceptent deux ou quatre chiffres. Privilégiez les quatre chiffres pour lever l’ambiguïté : par exemple 07 va être interprété comme 1907 et pas comme 2007. De même, soyez attentif si vous échangez des fichiers entre Excel PC et Mac : Excel pour Windows utilise le calendrier depuis 1900 alors que la version d'Excel pour Mac utilise le calendrier depuis 1904.

Dans la plupart des formules, les dates doivent être précisées en utilisant la fonction DATE ou en tant que résultat "date" d'autres formules ou fonctions. L'application d'un format texte peut conduire à d'inextricables problèmes difficiles à détecter.

La fonction DATEVAL

La fonction DATEVAL convertit une date stockée sous forme de texte en un numéro de série reconnu par Excel comme une date. Par exemple, la formule =DATEVAL("1/1/2008") renvoie 39 448, le numéro de série du 1er janvier 2008.

DATEVAL sert souvent lorsqu'il s'agit de filtrer, trier ou mettre en forme des dates saisies en tant que texte (c'est parfois le cas lors de l'importation de données venant d'un système autre qu'un micro-ordinateur). La fonction nécessite comme paramètre une cellule contenant du texte dont le format correspond à une date Excel. Ainsi : "30/1/2018" est correct, mais aussi "30-Jan-2018", car il s'agit d'une chaîne de texte entre guillemets qui correspondent à une date au sens Excel.

La fonction MOIS.DECALER

Cette fonction renvoie le numéro de série qui représente la date correspondant à la date passée en paramètre plus ou moins le nombre de mois indiqué. Si par exemple la cellule A1 contient la date 24/02/2015, la formule =MOIS.DECALER(A1;2) renvoie le numéro de série 42 118 qui correspond au 24/04/2015, soit deux mois plus tard. Cette fonction s'utilise généralement dans des calculs de dates d'échéance.

Le paramètre date_départ est nécessairement de type date (au sens Excel). Le paramètre mois peut être positif (date future) ou négatif (date dans le passé).

La fonction DATEDIF

Probablement pour des raisons de compatibilité, la fonction DATEDIF n'est pas référencée sous Excel 2010, mais elle existe bel et bien. Elle permet de calculer le nombre de jours, de mois ou d'années entre deux dates. La syntaxe est la suivante : DATEDIF(date_début;date_fin;unité). Le paramètre date_début repère la date de début de la période, le paramètre date_fin la date de fin de période et unité au type de comptage à effectuer : "y" (year en anglais) permet de calculer le nombre d'années entre les deux dates, "m" (month en anglais) le nombre de mois et "d" (day en anglais) le nombre de jours.

Par exemple : Si la cellule A1 contient la date du 01/01/2015 et la cellule A2 la date du 24/02/2015, la formule =DATEDIF(A1;A2;"d") renvoie le nombre 54, c'est-à-dire le nombre de jours entre ces deux dates. De la même manière la formule =DATEDIF(A1;A2;"m") renvoie le nombre 1, c'est-à-dire e nombre de mois entre ces deux dates.

Vous pourrez aussi essayer "ym" pour récupérer la différence entre le mois de date_début et celui de date_fin sans tenir compte des jours et des années. Il existe aussi les paramètres "yd" et "md".

Calculer en jours fériés

La fonction SERIE.JOUR.OUVRE permet de disposer d'un nombre qui représente une date qui correspond à une date (paramètre date_début) plus ou moins le nombre de jours ouvrés spécifié (nb_jours). Les jours ouvrés excluent les fins de semaine et toutes les dates identifiées comme étant des jours féries. La syntaxe est la suivante : SERIE.JOUR.OUVRE(date_début; nb_jours; [jours_fériés]).

Par exemple, si la cellule A1 contient la date 01/01/2015, la formule =SERIE.JOUR.OUVRE(A1;10) renvoie le numéro de série qui correspond à la date du 15/01/2015, qui prend en compte que le 1er janvier 2015 est férié ainsi que les deux samedis et dimanches suivants des week-ends qui ne sont pas travaillés.

Notez que le paramètre jours_fériés est précisé entre crochets ; ce qui signifie que sa présence est facultative. S'il est précisé, ce paramètre représente une liste d'une ou plusieurs dates à exclure du calendrier des jours de travail, comme les jours fériés ou d'autres jours contractuellement chômés. Cette liste est généralement définie en tant que plage de cellules contenant les dates en question. La fonction SERIE.JOUR.OUVRE sert souvent dans le calcul des échéances de factures, des horaires de livraisons ou encore dans le calcul des congés.

Trouver le numéro de semaine dans l'année pour une date

Si la date est indiquée dans la cellule A1 (puis d'autres dates en A2 et A3), la formule suivante renvoie le numéro de la semaine (entre 1 et 53) de cette date dans l'année en question :

=ENT((A1-SOMME(MOD(DATE(ANNEE(A1-MOD(A1-2;7)+3);1;2);{1E+99;7})*{1;-1})+5)/7)

by via MyTFMRSS