I. Introduction▲
La manipulation des dates et des heures représente une part importante des calculs effectués
dans le tableur. Une large panoplie de formules spécifiques, dont les fonctions de
l'utilitaire d'analyse qui sont désormais natives, est mise à votre disposition
et offre des possibilités d'opérations très diversifiées.
Ces fonctions sont disponibles dans la catégorie 'Date et heure' de la bibliothèque de
fonctions :
Sélectionnez l'onglet 'Formules' dans le ruban.
Cliquez sur le menu 'Date et heure' dans le groupe 'Bibliothèque de fonctions'.
La gestion des dates peut parfois sembler ardue. De nombreux problèmes peuvent néanmoins
être facilement résolus en comprenant le principe de fonctionnement dans Excel et en
suivant quelques règles simples d'utilisation.
Cet article présente les règles générales applicables aux dates et aux heures,
décrit chaque fonction et propose quelques exemples d'utilisation.
Remarque :
Les fonctions du complément 'Utilitaire d'analyse' sont désormais natives dans Excel 2007.
Il est possible que les fonctions ATP créées dans un classeur Excel2007 renvoient une
erreur #Nom! lorsque le fichier est ouvert avec une version antérieure du tableur.
De la même manière, un classeur créé dans Excel2003 (ou antérieur), puis ouvert dans Excel2007,
peut renvoyer une erreur #Nom! lorsque les cellules contiennent des formules issues de
l'utilitaire d'analyse. Pour ce dernier cas, il suffit de rééditer et revalider la formule
afin de corriger l'erreur:
Sélectionnez la cellule.
Appuyez sur la touche clavier F2, puis sur la touche F9.
Ron de Bruin propose également une solution pour résoudre ces
erreurs : Consultez le tutoriel.
II. Généralités au sujet des dates et des heures dans Excel▲
Tout d'abord, voici deux principes de base :
Pour Excel, la valeur 1 équivaut à 24 heures (1 journée).
Par défaut, le calendrier de l'application identifie les dates sous forme de nombres entiers
(aussi appelés numéros de série), depuis le 01 Janvier 1900 jusqu'au 31 Décembre 9999.
A partir de ces deux simples définitions, on peut facilement comprendre la structure des
dates dans Excel et comment les manipuler :
1 = 01/01/1900, 2 = 02/01/1900 ... 2 958 465 = 31/12/9999.
Une date est en fait un nombre entier et la cellule est formatée pour
afficher la représentation de cette date.
Excel fait une grande partie du travail en modifiant automatiquement le format de la cellule
en Date si le format de la cellule était Standard avant que la fonction ne soit entrée.
Pour afficher le numéro de série, changez le format de la cellule en Standard ou Nombre.
Il est utile de savoir que le 01/01/1900 était un Dimanche. Cette information est pratique
pour certaines astuces de calcul.
Les heures, minutes et secondes quant à elles sont identifiées par les décimales de 0 à 0,99999.
Une heure est un numéro de série qui représente la portion d'une
journée et la cellule est formatée pour afficher cette heure.
Par exemple la valeur 0,624 représente 15H00, qui correspond à la fraction 15/24.
Si vous saisissez 30 heures, par défaut Excel va l'interpréter par 1 jour + 6 heures, et seules
les 6 heures seront affichées.
Vous devez appliquer un format personnalisé dans la cellule pour faire apparaitre des heures
supérieures à 24 heures, en encadrant le symbole des heures entre crochets :
[hh]:mm:ss
Sur le même principe, si vous devez afficher plus de 60 minutes, encadrez le symbole des minutes
entre crochets :
[mm]:ss
Excel dispose d'un deuxième calendrier, appelé 1904. C'est le calendrier par défaut pour
le système d'exploitation Macintosh.
Le numéro de série 1 correspond au 01/01/1904 dans Excel, sous cette
plateforme. Il y a donc 1462 jours de décalage entre les calendriers
par défaut MAC et Windows, et deux dates identiques sont représentées
par des nombres différents.
Cela peut poser des problèmes lorsque vous ouvrez un classeur créé sous un
système d'exploitation différent.
Pour choisir un autre type de calendrier dans un classeur spécifique :
Cliquez sur le Bouton Office.
Cliquez sur le bouton 'Options Excel'.
Sélectionnez le menu 'Options avancées'.
Déplacez vous jusqu'au champ 'Lors du calcul de ce classeur'.
Sélectionnez le classeur à modifier, dans le menu déroulant.
Cochez ou décochez l'option 'Utiliser le calendrier depuis 1904' en fonction de votre besoin.
Cliquez sur le bouton OK pour valider.
Faites attention à toujours bien spécifier les années avec 4 chiffres lorsque c'est possible,
car dans certains cas une année rédigée sur uniquement les deux derniers chiffres peut renvoyer
des résultats erronés.
En effet, les règles définies par défaut dans le système d'exploitation stipulent que :
00 à 29 correspond aux années 2000 à 2029.
30 à 99 correspond aux années 1930 à 1999.
Pour modifier le mode de gestion des dates du PC, sous Windows XP :
Cliquez sur le bouton 'Démarrer'.
Sélectionnez le menu 'Panneau de configuration'.
Double cliquez sur l'icône 'Options régionales et linguistiques'.
Sélectionnez l'onglet 'Option régionales' dans la boîte de dialogue.
Cliquez sur le bouton 'Personnaliser'.
Sélectionnez l'onglet 'Date'.
Vous remarquerez que cette boîte de dialogue permet aussi de modifier d'autres paramètres de
représentation et de formatage par défaut, pour les dates et les heures saisies dans les
cellules.
Il est important d'uniformiser les formats date dans l'ensemble de votre
tableau pour pouvoir utiliser les autres outils d'Excel :
* Les tris
* Les filtres
* Les tableaux de données
* Les graphiques
* Les tableaux croisés dynamiques
Par exemple, un regroupement par périodes (hebdomadaires, mensuelles...) sera impossible
si toutes les dates ne sont pas
formatées de la même manière dans la plage source (données texte mélangées
avec des valeurs numériques).
L'application possède des raccourcis clavier pour insérer rapidement la date du jour et
l'heure dans la cellule active :
Pour insérer la date du jour, utilisez le raccourci
clavier CTRL + ; (Touche Ctrl et le point virgule).
Pour insérer l'heure, utilisez le raccourci
clavier CTRL + : (Touche Ctrl et les deux points).
La saisie des dates avant le 01 Janvier 1900 doit être effectuée au format texte.
Excel possède un outil de gestion lorsque vous utilisez la poignée
de recopie sur une date.
Une balise active (smartag) apparait en bas et à droite de la dernière cellule. Elle permet
de reformater ou modifier les dates de la sélection.
Par exemple, pour créer une liste incrémentée contenant uniquement des jours ouvrés :
Saisissez votre première date (qui doit être un jour ouvré) en A1.
Utilisez la poignée de recopie vers le bas, jusqu'à la date de fin de votre tableau.
Chaque cellule contient maintenant une date.
Cliquez sur la balise active qui apparait en bas et à droite de la dernière cellule.
Sélectionnez l'option 'Incrémenter les jours ouvrés'.
La liste est automatiquement modifiée pour ne faire apparaitre que les jours ouvrés (Les
dates correspondantes aux samedis et aux dimanches ont été éliminées de la plage de cellules).
Remarque:
Si la première date saisie correspond à un jour non ouvré, celle ci ne sera pas supprimée
de la liste.
III. Les formats de cellule pour afficher les dates et les heures▲
L'affichage des dates et des heures peut parfois paraître délicat pour un utilisateur
non aguerri.
Comme cela a déjà été évoqué dans le chapitre précédent, Excel fait une grande partie du
travail en modifiant automatiquement le format de la cellule
en Date si le format de la cellule est Standard avant que la date ou la fonction ne
soit saisie.
Par contre, si la cellule était préalablement au format nombre, c'est le numéro de série qui
sera affiché et non la représentation de la date. Ce résultat peut sans aucun doute troubler
un utilisateur occasionnel qui ne connaitrait pas parfaitement les règles de fonctionnement d'Excel.
Ce chapitre montre comment reformater les cellules pour afficher différents formats de dates
et d'heures.
Pour accéder aux paramètres de formatage, faites un clic droit dans les cellules.
Sélectionnez l'option 'Format de cellule' dans le menu contextuel.
Cliquez sur l'onglet 'Nombre'.
Les catégories 'Date' et 'Heure' disposent chacune de plusieurs formats prédéfinis :
Nota :
Sous Excel 2007, vous pouvez également gérer les formats de cellules depuis le groupe 'Nombre',
dans l'onglet 'Accueil'.
Utilisez la catégorie 'Personnalisée' pour appliquer un affichage
particulier.
Des lettres symboles, permettent de définir la personnalisation. Elles sont saisies dans le champ
'Type'.
Remarquez que le champ 'Exemple' peut vous aider car il affiche une prévisualisation du résultat,
à partir des données de la cellule active.
Le format Date:
La lettre j représente le jour.
La lettre m représente le mois.
La lettre a représente l'année.
L'association des différents symboles permet de personnaliser l'affichage. Par exemple si une date (05/07/2006)
est saisie dans une cellule:
j renvoie 5
jj renvoie 05
jjj renvoie mer (nom du jour au format court)
jjjj renvoie mercredi
m renvoie 7
mm renvoie 07
mmm renvoie juil (nom du mois au format court)
mmmm renvoie juillet
a et aa renvoient 06
aaa et aaaa renvoient 2006
jjjj jj mmmm aaaa renvoie mercredi 05 juillet 2007
Le format d'heure:
La lettre h représente les heures.
La lettre m représente les minutes.
La lettre s représente les secondes.
Les symboles doivent être encadrés par des crochets si vous désirez afficher des valeurs supérieures
à 24 heures ou à 60 minutes.
Par exemple, utilisez le format personnalisé [hh]:mm:ss pour
que la cellule puisse afficher des temps plus grands que 24 heures.
IV. Description des fonctions▲
II-A. ANNEE▲
La fonction ANNEE permet d'extraire l'année d'une date, entre 1900 et 9999. Le résultat
est affiché sous un format standard de 4 chiffres.
La syntaxe :
=ANNEE(numéro_de_série)
L'argument numéro_de_série représente la date dont vous
souhaitez extraire l'année. Il peut être représenté par :
* Une date au format texte.
* La référence à une autre cellule contenant une date.
* Le numéro de série d'une date.
II-B. AUJOURDHUI▲
La fonction AUJOURDHUI renvoie la date du jour, indiquée par
l'horloge interne du PC.
La syntaxe :
=AUJOURDHUI()
Cette fonction ne possède pas d'argument. La date du jour est actualisée automatiquement
à chaque recalcul.
La date est affichée sous la forme JJ/MM/AAAA si la cellule contenant la formule est au
format standard.
C'est le numéro de série de la date qui est affiché si la cellule est au format 'Nombre'.
II-C. DATE▲
La fonction DATE renvoie une date à partir des paramètres Année, Mois et Jour spécifiés.
La syntaxe :
=DATE(année;mois;jour)
L'argument année doit être
de préférence rédigé sous un format de 4 caractères afin de garantir la validité des calculs
ultérieurs.
Si vous indiquez une valeur comprise entre 0 et 1899, Excel ajoutera ce nombre à l'année
de base (1900). La formule =DATE(120;1;1) renvoie 01/01/2020.
L'argument mois utilise les valeurs de 1 à 12 pour représenter
les mois, de Janvier à Décembre.
Si vous indiquez une valeur négative, ce nombre de mois +1 est soustrait au premier jour
de l'année spécifiée. La formule =DATE(2008;-2;1) renvoie 01/10/2007.
Si vous indiquez une valeur supérieure à 12, ce nombre de mois est ajouté au mois de Janvier
de l'année spécifiée. La formule =DATE(2008;15;1) renvoie 01/03/2009.
L'argument jour utilise les valeurs de 1 à 31 pour représenter
les jours du mois.
Si vous indiquez la valeur 0, la fonction affiche le dernier jour du mois précédent. La
formule =DATE(2008;4;0) renvoie 31/03/2008.
Si vous indiquez une valeur négative, ce nombre de jours est soustrait au dernier jour
du mois précédent spécifié. La formule =DATE(2008;5;-2) renvoie 28/04/2008.
Le résultat est affiché sous la forme d'une date si la cellule contenant la formule est au
format standard.
C'est le numéro de série de la date qui est affiché si la cellule est au format 'Nombre'.
IV-D. DATEDIF▲
La fonction DATEDIF permet d'effectuer une soustraction entre deux dates, à partir
de l'unité de temps spécifiée.
La syntaxe :
=DATEDIF(date_départ;date_fin;unité)
L'argument unité peut prendre une des chaînes suivantes :
* Y (Nombre d'années complètes entre les deux dates)
* M (Nombre de mois complets entre les deux dates)
* D (Nombre de jours entre les deux dates)
* MD (Différence en jours, sans prendre en compte les mois et
les années)
* YM (Différence en mois, sans prendre en compte les jours et
les années)
* YD (Différence en jours, sans prendre en compte les années)
IV-E. DATEVAL▲
Nous avons vu au début de cet article qu'une date est en fait une valeur entière.
Grâce à la Fonction DATEVAL, Excel sait également identifier des dates saisies au
format texte et les transformer en valeur numérique.
La fonction DATEVAL convertit une date saisie au format texte, en numéro de série correspondant.
La syntaxe :
=DATEVAL(date_texte)
L'argument date_texte est une date valide, obligatoirement
spécifiée au format texte, comprise entre le 1er janvier 1900 et le 31 décembre 9999.
La fonction DATEVAL utilise l'année en cours de l'horloge interne du PC si l'année
n'est pas précisée dans l'argument.
DATEVAL est très utile car de nombreux formats texte sont reconnus par la fonction.
IV-F. FIN.MOIS▲
La fonction FIN.MOIS renvoie la date du dernier jour du mois, passé ou futur par rapport à
une date de référence.
La syntaxe :
=FIN.MOIS(date_départ;mois)
L'argument date_départ indique la date référence.
L'argument mois indique le nombre de mois passés (valeur négative)
ou futurs (valeur positive) par rapport à la date de départ.
Spécifiez la valeur 0 pour récupérer le dernier jour de la date de départ.
IV-G. FRACTION.ANNEE▲
La fonction FRACTION.ANNEE renvoie le nombre d'années écoulées entre deux dates.
La syntaxe :
=FRACTION.ANNEE(date_début;date_fin;base)
L'argument base indique comment sont comptés les jours.
Un exemple de calcul d'amortissement :
Nota :
Les bases annuelles de 360 jours sont parfois utilisées dans les systèmes comptables.
IV-H. HEURE▲
La fonction HEURE extrait l'heure (un nombre entier entre 1 et 24) d'un code de temps
(une fraction de 24 heures).
La syntaxe :
=HEURE(numéro_de_série)
L'argument numéro_de_série est une valeur de
temps contenant l'heure que vous voulez retrouver.
IV-I. JOUR▲
La fonction JOUR renvoie le jour du mois (un nombre entier entre 1 et 31)
correspondant à la date spécifiée.
La syntaxe :
=JOUR(numéro_de_série)
L'argument numéro_de_série permet d'indiquer la date dont
vous voulez extraire le jour.
IV-J. JOURS360▲
La fonction JOURS360 renvoie le nombre de jours compris entre deux dates sur la base
d'une année de 360 jours.
Cette fonction est mise en oeuvre pour des calculs comptables utilisant une base
calendaire de 360 jours, l'année étant découpée en 12 mois de 30 jours.
La syntaxe :
=JOURS360(date_début, date_fin, [méthode])
Les arguments date_début et date_fin
doivent contenir des formats dates ou des numéros de série.
L'argument [méthode] est une valeur logique qui permet de
préciser le mode de gestion pour les mois de 31 jours.
La valeur FAUX utilise la méthode États-Unis (NASD) : Si la date de début est le dernier jour
du mois, la date de début devient le 30 du même mois. Si la date de fin est le dernier
jour du mois et que la date de début est avant le 30 du mois, la date de fin
devient le 1er du mois suivant. Sinon, la date de fin devient le 30 du même mois.
La valeur VRAI utilise la méthode européenne : Les dates de début et de fin correspondant au 31
deviennent le 30 du même mois.
IV-K. JOURSEM▲
La fonction JOURSEM convertit la date spécifiée en valeur entière représentant le jour de
la semaine.
Par défaut, Dimanche=1, Lundi=2, ...
Cette fonction est très utile et notamment pour repérer les week-ends.
La syntaxe :
=JOURSEM(numéro_de_série;type_retour)
L'argument numéro_de_série
représente la date dont vous voulez extraire le jour de la semaine.
L'argument type_retour détermine comment est repéré chaque jour
de la semaine.
* 1 (ou omis) : Dimanche=1, Lundi=2 ...
* 2 : Lundi=1, Mardi=2 ...
* 3 : Lundi=0, Mardi=1 ...
IV-L. MAINTENANT▲
La fonction MAINTENANT renvoie la date et l'heure au moment du calcul, et indiquée par
l'horloge interne du PC.
La syntaxe :
=MAINTENANT()
Cette fonction ne possède pas d'argument. La date et l'heure du jour sont actualisées
automatiquement à chaque recalcul.
La date et l'heure sont affichées sous la forme personnalisée JJ/MM/AAAA HH:MM si la cellule
contenant la formule est au format standard avant l'insertion de la formule.
C'est le numéro de série qui est affiché si la cellule est préalablement
au format 'Nombre'.
La date est représentée par la partie entière du numéro de série.
L'heure est représentée par la partie décimale du numéro de série.
IV-M. MINUTE▲
La fonction MINUTE extrait les minutes (un nombre entier entre 0 et 59) d'un code de
temps (une fraction de 24 heures).
La syntaxe :
=MINUTE(numéro_de_série)
L'argument numéro_de_série est une valeur de
temps contenant les minutes que vous voulez retrouver.
IV-N. MOIS▲
La fonction MOIS renvoie le numéro du mois (valeur entre 1 et 12)
correspondant à la date spécifiée.
La syntaxe :
=MOIS(numéro_de_série)
L'argument numéro_de_série permet d'indiquer la date dont
vous voulez extraire le mois.
La fonction MOIS renvoie la valeur 1 si l'argument numéro_de_série fait référence à une cellule vide.
IV-O. MOIS.DECALER▲
La fonction MOIS.DECALER permet d'ajouter ou de soustraire un nombre de mois à
la date spécifiée.
La syntaxe :
=MOIS.DECALER(date_départ;mois)
L'argument date_départ représente la date de référence à partir
de laquelle les calculs vont être effectués.
L'argument mois représente le nombre de mois qui sera soustrait
(valeur négative) ou ajouté (valeur positive) à la date de référence.
Si le nombre de jours de la nouvelle date décalée est inférieur à celui de la date d'origine,
la formule indiquera le dernier jour de la nouvelle échéance (par exemple si vous ajoutez un mois
au 31/10/2008, la formule renvoie le 30/11/2008).
IV-P. NB.JOURS.OUVRES▲
La fonction NB.JOURS.OUVRES compte le nombre de jours ouvrés entre deux dates. Les jours ouvrés
ne comptabilisent pas les Samedi et les Dimanche.
La syntaxe :
=NB.JOURS.OUVRES(date_début;date_fin;jours_fériés)
Comme leur nom l'indique, les arguments date_début et
date_fin permettent de définir dans quelle période
doit être effectué le calcul.
L'argument jours_fériés contient la liste facultative
de tous les autres jours qui doivent être exclus du calcul (généralement des périodes de congés,
des ponts, des temps partiels et des jours fériés). Cette liste peut être définie par une
référence à une plage de cellules, à une plage nommée ou à une constante matricielle
(sous forme de numéros de série).
IV-Q. NO.SEMAINE▲
La fonction NO.SEMAINE renvoie le numéro de semaine pour la date spécifiée.
La syntaxe :
=NO.SEMAINE(numéro_de_série;méthode)
L'argument numéro_de_série représente la date dont vous voulez
retrouver le numéro de semaine.
L'argument méthode permet de préciser quel est le premier jour
de la semaine (le Dimanche par défaut). Indiquez la valeur 1 pour spécifier le Dimanche. Indiquez
la valeur 2 pour spécifier le Lundi.
Attention, la fonction NO.SEMAINE doit être utilisée avec beaucoup de prudence car elle
peut parfois poser des problèmes.
Par exemple, la date 04/01/2005 renvoie la valeur 2 alors qu'il s'agit
de la semaine 1 selon les normes européennes.
En Europe, la première semaine de l'année doit contenir au moins 4 jours.
Par contre, la fonction NO.SEMAINE est basée sur la norme US (La semaine 1 commence le
1er janvier). Cette différence de norme donne donc un résultat erroné pour les européens
si le premier jeudi de l'année tombe après le 4 janvier.
Vous pouvez utiliser la fonction suivante pour contourner ce problème (la date dont vous
souhaitez retrouver le numéro de semaine est saisie dans la cellule A1) :
=ENT(MOD(ENT((A1-2)/7)+0,6;52+5/28))+1
IV-R. SECONDE▲
La fonction SECONDE extrait les secondes (un nombre entier entre 0 et 59) d'un code de
temps (une fraction de 24 heures).
La syntaxe :
=SECONDE(numéro_de_série)
L'argument numéro_de_série est une valeur de
temps contenant les minutes que vous voulez retrouver.
IV-S. SERIE.JOUR.OUVRE▲
La fonction SERIE.JOUR.OUVRE permet d'ajouter ou de soustraire un nombre de jours ouvrés à
la date spécifiée. Les jours ouvrés ne comptabilisent pas les Samedi et les Dimanche.
La syntaxe :
=SERIE.JOUR.OUVRE(date_début;nb_jours;jours_fériés)
L'argument date_départ représente la date de référence à partir
de laquelle les calculs font être effectués.
L'argument nb_jours représente le nombre de jours ouvrés qui sera
soustrait (valeur négative) ou ajouté (valeur positive) à la date de référence.
L'argument jours_fériés contient la liste facultative
de tous les autres jours qui doivent être exclus du calcul (généralement des périodes de congés,
des ponts, des temps partiels et des jours fériés). Cette liste peut être définie par une
référence à une plage de cellule, à une plage nommée ou à une constante matricielle
(sous forme de numéros de série).
IV-T. TEMPS▲
La fonction TEMPS renvoie une fraction de 24 heures sous forme décimale, à partir
des heures, minutes et secondes spécifiées.
Le nombre décimal renvoyé par la fonction TEMPS est une valeur comprise
entre 0 et 0,99999 qui représente l'heure, de 0:00:00 (12:00:00 AM)
à 23:59:59 (11:59:59 PM).
Si le format de cellule était Standard avant que la fonction ne soit entrée, le
résultat est mis en forme en tant que date. Spécifiez le format Nombre pour afficher
la valeur décimale.
La syntaxe :
=TEMPS(heure;minute;seconde)
L'argument heure représente un nombre compris entre
0 et 32767 indiquant l'heure. Toute valeur supérieure à 23 sera divisée par 24 et le
reste sera traité comme la valeur horaire.
Par exemple, TEMPS(27;0;0) = TEMPS(3;0;0) = 0,125 ou 03:00 (03:00 AM).
L'argument minute représente un nombre compris entre 0 et 32767
indiquant les minutes. Toute valeur supérieure à 59 sera convertie en heures et en minutes.
Par exemple, TEMPS(0;750;0) = TEMPS (12;30;0) = 0,520833 ou 12:30 (12:30 PM).
L'argument seconde représente un nombre compris
entre 0 et 32767 indiquant les secondes. Toute valeur supérieure à 59 sera convertie
en heures, minutes et secondes.
Par exemple, TEMPS(0;0;2000) = TEMPS(0;33;22) = 0,023148 ou 00:33:20 (12:33:20 AM)
IV-U. TEMPSVAL▲
Nous avons vu au début de cet article qu'une heure est en fait une valeur décimale.
Grâce à la Fonction TEMPSVAL, Excel sait également identifier des heures saisies au
format texte et les transformer en valeur numérique.
La fonction TEMPSVAL convertit une heure saisie au format texte, en valeur décimale
correspondante.
La syntaxe :
=TEMPSVAL(heure_texte)
L'argument heure_texte représente une heure au format texte.
Cette chaîne de caractères doit être interprétable par la fonction, comme dans les exemples
ci dessous.
V. Créer un calendrier perpétuel▲
V-A. La préparation du calendrier▲
Après avoir passé en revue toutes les fonctions, vous allez réaliser un exercice pratique.
Ce chapitre montre comment créer rapidement un calendrier perpétuel. Il permet de gérer un planning
de présence, une couleur spécifique s'affichant dans les cellules en fonction des types d'absences
(week end, jours fériés, RTT, vacances et un jour de temps partiel hebdomadaire en option). Le
classeur démo au format .xlsx est téléchargeable en bas de cette page.
Tout d'abord, ouvrez un nouveau classeur.
Le fichier doit contenir deux feuilles nommées 'Calendrier' et 'Paramètres'.
La cellule A1 de la feuille 'Calendrier' va contenir l'année de l'agenda. Lorsque vous modifierez
l'année, l'ensemble du calendrier sera automatiquement mis à jour.
Vous allez ensuite créer le nom des mois dans la plage B1:M1.
Insérez la formule suivante dans la cellule B1.
=DATE($A$1;COLONNE()-1;1)
Puis utilisez la poignée de recopie jusque dans la cellule M1.
Sélectionnez la plage B1:M1.
Clic droit sur la plage sélectionnée.
Sélectionnez 'Format de cellule' dans le menu contextuel.
Cliquez sur l'onglet 'Nombre'.
Sélectionnez la catégorie 'Personnalisée'.
Indiquez le type mmmm.
Cliquez sur le bouton OK pour valider.
Vous venez de créer les en-têtes du calendrier.
Recopiez la formule ci-dessous dans tout le calendrier (plage B2:M32), hormis
dans les cellules en fin de colonne ne correspondant pas à des dates (C31, C32, E32...).
=DATE($A$1;COLONNE()-1;LIGNE()-1)
La phase suivante consiste à gérer les années bissextiles.
Placez cette formule dans la cellule C30. Ainsi le 29 Février sera affiché uniquement lorsqu'une
année bissextile est saisie dans la cellule A1.
=SI(MOIS(DATE($A$1;COLONNE()-1;LIGNE()-1))=2;DATE($A$1;COLONNE()-1;LIGNE()-1);"")
Sélectionnez la plage B2:M32 pour paramétrer la mise en forme des dates dans les cellules.
Appliquez le format personnalisé : j jjj
V-B. La gestion des jours fériés▲
L'objectif étant d'actualiser le calendrier automatiquement dès que l'année est modifiée,
vous allez devoir mettre en oeuvre un calcul des jours fériés.
Il existe deux types de jours fériés.
Les dates fixes qui sont facilement identifiables :
=DATE(Calendrier!$A$1;1;1)=DATE(Calendrier!$A$1;5;1)=DATE(Calendrier!$A$1;5;8)=DATE(Calendrier!$A$1;8;15)=DATE(Calendrier!$A$1;11;1)=DATE(Calendrier!$A$1;11;11)=DATE(Calendrier!$A$1;12;25)
Les dates mobiles qui nécessitent un calcul d'identification :
=DATE(Calendrier!$A$1;SI((25-MOD((11*MOD(Calendrier!$A$1-1900;19)+4-ENT((7*MOD
(Calendrier!$A$1-1900;19)+1)/19));29)-MOD(Calendrier!$A$1-1900+ENT((Calendrier!$A$1-1900)
/4)+31-MOD((11*MOD(Calendrier!$A$1-1900;19)+4-ENT((7*MOD(Calendrier!$A$1-1900;19)+1)/19))
;29);7))>0;4;3);SI((25-MOD((11*MOD(Calendrier!$A$1-1900;19)+4-ENT((7*MOD(Calendrier!$A$1-1900
;19)+1)/19));29)-MOD(Calendrier!$A$1-1900+ENT((Calendrier!$A$1-1900)/4)+31-MOD((11*MOD
(Calendrier!$A$1-1900;19)+4-ENT((7*MOD(Calendrier!$A$1-1900;19)+1)/19));29);7))>0;(25-MOD
((11*MOD(Calendrier!$A$1-1900;19)+4-ENT((7*MOD(Calendrier!$A$1-1900;19)+1)/19));29)-MOD(
Calendrier!$A$1-1900+ENT((Calendrier!$A$1-1900)/4)+31-MOD((11*MOD(Calendrier!$A$1-1900;19)
+4-ENT((7*MOD(Calendrier!$A$1-1900;19)+1)/19));29);7));31+(25-MOD((11*MOD(Calendrier!$A$1-1900;
19)+4-ENT((7*MOD(Calendrier!$A$1-1900;19)+1)/19));29)-MOD(Calendrier!$A$1-1900+ENT(
(Calendrier!$A$1-1900)/4)+31-MOD((11*MOD(Calendrier!$A$1-1900;19)+4-ENT((7*MOD(Calendrier!$A$1
-1900;19)+1)/19));29);7))))+1'La cellule A3 contient la formule de calcul de la date de Pâques
=A3+38'La cellule A3 contient la formule de calcul de la date de Pâques
=A3+49
Remarque :
Je ne suis pas l'auteur de la fonction pour calculer la date de Pâques, mais n'ayant pas
noté son nom lorsque j'ai récupéré la formule sur le net je ne peux le citer. N'hésitez pas
à me contacter si vous avez des informations au sujet de l'auteur afin que je puisse le
préciser dans l'article.
Basculez sur la feuille 'Paramètres'.
Indiquez un en-tête dans la cellule A1, par exemple 'Fériés'.
Dans les cellules suivantes de la colonne A, recopiez les formules de jours fériés. Si vous
réutilisez directement les formules citées précédemment, le calcul de la date de Pâques
doit impérativement dans la cellule A3.
Il vous reste à nommer la plage de cellules contenant les jours fériés:
Sélectionnez l'onglet 'Formules' dans le ruban.
Cliquez sur le bouton 'Gestionnaire de noms' dans le groupe 'Noms définis'.
Cliquez sur le bouton 'Nouveau'.
Indiquez Feries dans le champ 'Nom'.
Collez la formule suivante dans le champ 'Fait référence à :' :
=DECALER(Paramètres!$A$1;;;NBVAL(Paramètres!$A:$A);1)
La plage de cellules de la colonne A est ainsi nommée dynamiquement. Vous n'aurez pas
besoin de renommer les cellules si vous souhaitez enlever certains jours fériés de la liste.
V-C. L'identification des autres causes d'absence▲
Il peut y avoir d'autres causes d'absence en plus des weeks-ends et des jours fériés :
* Les ponts
* Les vacances
* Les RTT
* Les éventels jours de temps partiel.
Indiquez un en-tête dans la cellule B1 : 'Vacances'.
Indiquez un en-tête dans la cellule C1 : 'RTT'.
Indiquez un en-tête dans la cellule D1 : 'Ponts'.
Vous renseignerez manuellement les dates d'absences à la suite, dans chaque colonne.
Reste à nommer dynamiquement chaque colonne. Comme pour les jours fériés, la plage s'adaptera
automatiquement au nombre d'enregistrements que vous aurez indiqué.
Créez 3 nouveaux noms :
Vacances fait référence à : =DECALER(Paramètres!$B$1;;;NBVAL(Paramètres!$B:$B);1)
RTT fait référence à : =DECALER(Paramètres!$C$1;;;NBVAL(Paramètres!$C:$C);1)
Ponts fait référence à : =DECALER(Paramètres!$D$1;;;NBVAL(Paramètres!$D:$D);1)
Le classeur démo dispose de la gestion optionnelle d'un jour de temps partiel.
Excel identifiera un jour particulier de la semaine que vous aurez spécifié
Indiquez un en-tête dans la cellule E1 : 'TempsPartiel'.
Nommez la cellule E2 : TempsPartiel.
Indiquez la date 02/01/1900 dans la cellule G2 et utilisez la poignée de recopie jusqu'en G6.
Sélectionnez la plage de cellules G2:G6.
Nommez la plage G2:G6 : ListeJoursOuvres
Appliquez le format personnalisé jjjj.
Vous obtenez la liste reformatée des jours de la semaine, du Lundi au Vendredi. Ces éléments
vont servir de support pour la liste de validation dans la cellule E2. Vous pourrez sélectionner
un jour qui sera identifié comme jour de temps partiel. Effacez le contenu de la cellule E2
si vous ne souhaitez pas utiliser cette option.
Sélectionnez la cellule E2.
Appliquez le format personnalisé jjjj.
Sélectionnez l'onglet 'Données' dans le ruban.
Cliquez sur le bouton 'Validation de données' dans le groupe 'Outils de données'.
Ouvrez l'onglet 'Options'.
Sélectionnez 'Liste' dans le menu déroulant 'Autoriser :'.
Indiquez =ListeJoursOuvres dans le champ 'Source :', pour faire
référence à la liste des jours de la semaine.
Cliquez sur le bouton OK pour valider.
V-D. La visualisation des absences dans le calendrier▲
Rebasculez sur la feuille 'Calendrier'.
Les dates d'absence vont être visualisées dans le calendrier par des mises en forme
conditionnelles.
Les nouvelles possibilités offertes par Excel 2007 permettent
d'insérer une couleur particulière pour chaque type d'absence, la limite des
3 conditions n'existant plus.
Les MEFC vont faire apparaitre les week-ends, les jours fériés, les ponts, les vacances,
les RTT et les éventuels jours partiels par une couleur spécifique.
Sélectionnez la plage B2:M32.
Sélectionnez l'onglet 'Accueil' dans le ruban.
Cliquez sur le menu déroulant 'Mise en forme conditionnelle' dans le groupe 'Style'.
Sélectionnez l'option 'Nouvelle règle'.
Sélectionnez le type de règle 'Utiliser une formule pour déterminer pour quelles
cellules le format sera appliqué.
Dans le champ 'Appliquer une mise en forme aux valeurs pour lesquelles cette formule
est vraie :', indiquez la formule suivante, pour identifier les week ends :
=ET(JOURSEM(B2;2)>5;B2<>"")
Choisissez un format de remplissage pour la cellule, lorsque la formule sera vraie.
Cliquez sur le bouton OK pour valider.
Procédez de la même manière pour identifier,
Les jours fériés : =ET(NB.SI(Feries;B2)>0;B2<>"")
Les vacances : =ET(NB.SI(Vacances;B2)>0;B2<>"")
Les ponts : =ET(NB.SI(Ponts;B2)>0;B2<>"")
Les RTT : =ET(NB.SI(RTT;B2)>0;B2<>"")
Les jours de temps partiel : =JOURSEM(B2;1)=TempsPartiel
Vous pouvez ensuite gérer l'ordre des conditions. La MEFC Excel applique par défaut
la première condition vraie. Un jour férié peut par exemple tomber sur un
week-end et vous pouvez préférer l'afficher en priorité.
Sélectionnez la plage B2:M32.
Sélectionnez l'onglet 'Accueil' dans le ruban.
Cliquez sur le menu déroulant 'Mise en forme conditionnelle' dans le groupe 'Style'.
Sélectionnez l'option 'Gérer les règles'.
La boîte de dialogue permet de définir l'ordre d'application des mises en forme
conditionnelles :
Sélectionnez la règle à déplacer.
Utilisez les boutons 'Monter' ou 'Descendre' pour changer l'emplacement de la règle.
L'ordre des règles est spécifié par leur position dans le tableau (La première
en haut et la dernière en bas).
Excel possède également une option sous forme de case à cocher pour définir si le
calcul doit être interrompu si la formule est vraie.
Vous disposez maintenant d'un calendrier perpétuel. Changez l'année dans la cellule A1
de la feuille 'Calendrier' pour actualiser l'affichage. Vous pouvez indiquez
d'autres jours d'absences dans la feuille 'Paramètres'.
Adaptez et modifiez librement le classeur démo en fonction de vos besoins.
V-E. Une adaptation pour Les versions antérieures d'Excel▲
Vous trouverez également en téléchargement une version simplifiée pour les versions d'Excel
antérieures à 2007.
Jusqu'à Excel 2003, vous êtes limité à 3 conditions dans la mise en forme conditionnelle.
L'adaptation du projet consiste à regrouper tous les types de congés dans la 3ième condition.
Une plage nommée 'Absences' va regrouper toutes les informations que vous aurez renseignées dans les
colonnes B à E :
=DECALER(Paramètres!$B$1;;;MAX(NBVAL(Vacances);NBVAL(RTT);NBVAL(Ponts));3)
Ce nom est ensuite utilisé dans la 3ième condition de la MEFC du calendrier :
=OU(ET(NB.SI(Absences;B2)>0;B2<>"");JOURSEM(B2;1)=TempsPartiel)
VI. Quelques exemples divers▲
Ce chapitre récapitule quelques exemples classiques d'utilisation des fonctions Date et
Heure dans Excel, qui j'espère pourront vous être utiles.
=AUJOURDHUI()-DATE(ANNEE(AUJOURDHUI());1;0)
'Effectuer la somme des valeurs de la plage B1:B10,
'si la date de la plage A1:A10 est comprise entre le 01/01/2004 et le 31/12/2004
'Formule matricielle à valider par CTRL+MAJ+ENTREE
{=SOMME((B1:B10)*(A1:A10>=DATE(2004;1;1))*(A1:A10<=DATE(2004;12;31)))}
=CNUM(SUBSTITUE(A2;"h";":"))-CNUM(SUBSTITUE(A1;"h";":"))
=JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))
=TEXTE(AUJOURDHUI(); "jjjj jj mmmm aaaa")
=TEXTE("1/"&A1;"mmmm")
'Les cellules doivent etre au format [hh]:mm
=MOD(A2-A1;1)
'pour un total d'heures saisi dans la cellule A1 au format [hh]:mm
=ENT(A1*24/8)
'Et pour récupérer le restant d'heures
=((A1*24/8)-ENT(A1*24/8))*8
'Affiche Vrai si la date saisie dans la cellule A1 est un jour de Week end, sinon renvoie Faux
=JOURSEM(A1;2)>5
'Exemple pour calculer l'âge d'une personne dont la date de naissance est saisie dans la cellule A1
=DATEDIF(A1;AUJOURDHUI();"y")&" ans , "&DATEDIF(A1;AUJOURDHUI();"Ym")&" mois
et "&DATEDIF(A1;AUJOURDHUI();"Md")&" jours"
'La date est saisie en A1
=SI(ET(DATEDIF(A1;AUJOURDHUI();"ym")=0;DATEDIF(A1;AUJOURDHUI();"md")=0);"Oui";"Non")
'La date est saisie en A1
=SI(DATEDIF(A1;AUJOURDHUI();"y")>=18;"Majeur";"Mineur")
'La date est saisie dans la cellule A1
=ANNEE(AUJOURDHUI()-A1)-1900&" ans "&MOIS(AUJOURDHUI()+1-A1)-1&" mois "
'La cellule contenant la formule doit être au format hh:mm:ss
=A1/60/60/24
'Une autre solution (la cellule contenant la formule toujours au format hh:mm:ss )
=A1*"0:0:1"
=ENT(MOD(ENT((A1-2)/7)+0,6;52+5/28))+1
=SOMME(A1:A10)/1440
'Appliquez le format heure à la cellule contenant la formule
'Les dates sont dans la plage A1:A10
=SOMMEPROD((MOIS(A1:A10)=2)*1)
'Une autre solution pour compter le mois de Janvier dans une plage pouvant contenir des cellules vides :
=SOMMEPROD((A$1:A$10<>"")*(MOIS(A$1:A$10)=1))
=A1-JOUR(A1)+9-JOURSEM(A1-JOUR(A1))
'Pensez à adapter le format de la cellule contenant la formule
=FIN.MOIS(DATE(TEXTE(A1;"aaaa");TEXTE(A1;"mm");1);0)
'Une autre solution :
=DATE(ANNEE(A1);MOIS(A1)+1;0)
=DATE(ANNEE(A1);MOIS(A1)+1;2)-JOURSEM(DATE(ANNEE(A1);MOIS(A1)+1;))
=FIN.MOIS(AUJOURDHUI();0)
="TRIMESTRE "&ENT((MOIS(A1)+2)/3)
=SI(MOIS(DATE(ANNEE(A1);2;29))=2;"Bissextile";"Non bissextile")
=(ENT(A1/7,4))/24
'Par exemple: 6,8 renvoie 0 et 7,6 renvoie 1
'Appliquez le format [hh] à la cellule contenant la formule
=A1/24
'La cellule contenant la formule doit être au format [hh]:mm
'Si par exemple vous saisissez 2,5 dans la Cellule A1, la cellule contenant la formule renvoie 02:30
'Par exemple 10:30 devient 10,5
=(A1-ENT(A1))*24
=A1+TEMPS(0;30;0)
=ARRONDI(A1/(1/24);0)*(1/24)
=SOMME(SI(JOURSEM(A1:A5)=1;1/NB.SI(A1:A5;A1:A5)))
'Fonction matricielle à valider par Ctrl+Maj+Entree.
'Remarque: la formule accepte des cellules vides dans la Plage A1:A5
'coefficient multiplicateur (x2) au résultat. Les jours sont en A1:A10, Les heures en B1:B10
=SOMMEPROD((JOURSEM(A1:A10)=1)*B1:B10)*2
'En paramètres: L'année dans la cellule A1. Le numéro de semaine dans la cellule A2.
'L'index du jour dans la cellule A3: 'Lundi=0 , Mardi=1, Mercredi=2 ...etc...
=TEXTE(DATE(A1;1;3)-JOURSEM(DATE(A1;1;3))-5+(7*A2)+A3;"mmmm")
'La date de début est saisie en A1
'La date de fin en B1
=SOMMEPROD((JOURSEM(LIGNE(INDIRECT(A1&":"&B1)))>1) *ESTNA(EQUIV(LIGNE(INDIRECT(A1&":"&B1));;0)))
'Nota: Dans cet exemple, les jours fériés ne sont pas comptabilisés.
=SOMMEPROD((JOURSEM(G3:G20)=2)*1)
'Pour une date saisie en A2 et le numéro de trimestre saisi en B2
=DATE(ANNEE(A2)+((MOIS(A2)+(B2*3))-(MOD((MOIS(A2)+(B2*3));12)))/12;((MOD((MOIS(A2)+(B2*3));12))-
MOD((MOD((MOIS(A2)+(B2*3));12));3))+1;1)-1
'Une autre solution :
=DATE(ANNEE(A2)+ENT(B2/4);ENT((MOIS(A2)+MOD(B2;4)*3)/3)*3+1;0)
'Pour une date saisie dans la cellule A2
=DATE(ANNEE(A2);MOIS(A2)+1;1)-JOURSEM(DATE(ANNEE(A2);MOIS(A2)+6;6))
'1=Dimanche
'2=Lundi
'3=Mardi
'4=Mercredi
'5=Jeudi
'6=Vendredi
'7=Samedi
'Autre solution :
=DATE(ANNEE(A2);MOIS(A2)+1;1)-MOD(DATE(ANNEE(A2);MOIS(A2)+1;1)+5;7)-3
=SERIE.JOUR.OUVRE(A1;1;liste_feries)
'La plage nommée 'liste_feries' contient la liste des jours fériés.
Les dates sont saisies en A1 et B1
=MAX(0;MOD(MOIS(B1)-MOIS(A1);12)-1+(JOUR(A1)=1)+(DATE(ANNEE(B1);MOIS(B1)+1;0)=B1))
=(ANNEE(B1)-ANNEE(A1))*12+MOIS(B1)-MOIS(A1)+(JOUR(A1)=1)-(JOUR(B1+1)>1)
=MAX(0;(ANNEE(B1+1)-ANNEE(A1-1))*12+MOIS(B1+1)-MOIS(A1-1)-1)
=TEMPS(ENT(A1/3600);ENT(MOD(A1;3600)/60);MOD(MOD(A1;3600);60))+ENT(A1/3600/24)
'Utilisez le format de la cellule [HH]:MM:SS
=SERIE.JOUR.OUVRE(DATE(2009;1;1);-1;liste_feries)
'La plage nommée 'liste_feries' contient la liste des jours fériés.
VII. Liens▲
Les dates et les calendriers, dans
la FAQ Excel de developpez.com
Création, enregistrement et fusion de format personnalisés,
par Jean Ballat.
Description des modifications apportées à l'Utilitaire d'Analyse (ATP) pour Microsoft Office Excel 2007
Reverse compatibility problem of the old ATP functions in non English versions of Excel
VIII. Remerciements▲
Je remercie toute l'équipe Office de DVP et particulièrement
Jean Ballat ,
pour la relecture et la correction du tutoriel.