Calcul dateheure

Utiliser les fonctions Date et Heure sous Excel 2007

par SilkyRoad (silkyroad.developpez.com)

Date de publication : 17 Novembre 2008

Dernière mise à jour : 22 Novembre 2008

Cet article montre comment utiliser les fonctions de la catégorie 'Date et Heure' sous Excel2007.

http://www.developpez.com

http://www.developpez.net/forums/member.php?u=70508

http://silkyroad.developpez.com

Utiliser les fonctions Date et Heure sous Excel 2007 par SilkyRoad (silkyroad.developpez.com)

- 2 -Les sources présentées sur cette pages sont libres de droits, et vous pouvez les utiliser à votre convenance. Par contre la page de présentationde ces sources constitue une oeuvre intellectuelle protégée par les droits d'auteurs. Copyright © 2008 Developpez LLC. Tout droits réservésDeveloppez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et imagessans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommageset intérêts.

http://silkyroad.developpez.com/excel/dateheure/

I - Introduction..............................................................................................................................................................3II - Généralités au sujet des dates et des heures dans Excel....................................................................................3III - Les formats de cellule pour afficher les dates et les heures................................................................................5IV - Description des fonctions..................................................................................................................................... 6

II-A - ANNEE.......................................................................................................................................................... 6II-B - AUJOURDHUI............................................................................................................................................... 6II-C - DATE............................................................................................................................................................. 6IV-D - DATEDIF......................................................................................................................................................7IV-E - DATEVAL..................................................................................................................................................... 7IV-F - FIN.MOIS..................................................................................................................................................... 7IV-G - FRACTION.ANNEE..................................................................................................................................... 8IV-H - HEURE........................................................................................................................................................ 8IV-I - JOUR.............................................................................................................................................................8IV-J - JOURS360....................................................................................................................................................8IV-K - JOURSEM....................................................................................................................................................9IV-L - MAINTENANT.............................................................................................................................................. 9IV-M - MINUTE.......................................................................................................................................................9IV-N - MOIS..........................................................................................................................................................10IV-O - MOIS.DECALER........................................................................................................................................10IV-P - NB.JOURS.OUVRES.................................................................................................................................10IV-Q - NO.SEMAINE............................................................................................................................................ 11IV-R - SECONDE................................................................................................................................................. 11IV-S - SERIE.JOUR.OUVRE................................................................................................................................11IV-T - TEMPS....................................................................................................................................................... 12IV-U - TEMPSVAL................................................................................................................................................ 12

V - Créer un calendrier perpétuel............................................................................................................................. 12V-A - La préparation du calendrier...................................................................................................................... 12V-B - La gestion des jours fériés.........................................................................................................................13V-C - L'identification des autres causes d'absence............................................................................................. 15V-D - La visualisation des absences dans le calendrier......................................................................................15V-E - Une adaptation pour Les versions antérieures d'Excel..............................................................................16

VI - Quelques exemples divers................................................................................................................................. 17VII - Liens...................................................................................................................................................................21VIII - Remerciements................................................................................................................................................. 21IX - Téléchargement.................................................................................................................................................. 21








I - Introduction

La manipulation des dates et des heures représente une part importante des calculs effectués dans le tableur. Unelarge 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ésolusen 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 proposequelques 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 lefichier est ouvert avec une version antérieure du tableur. De la même manière, un classeur créé dans Excel2003 (ouantérieur), puis ouvert dans Excel2007, peut renvoyer une erreur #Nom! lorsque les cellules contiennent des formulesissues 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érosde 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 commentles 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 dela 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 decalcul.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 affichercette 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 serontaffiché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:ssSur le même principe, si vous devez afficher plus de 60 minutes, encadrez le symbole des minutes entre crochets :[mm]:ssExcel dispose d'un deuxième calendrier, appelé 1904. C'est le calendrier par défaut pour le système d'exploitationMacintosh.





http://www.rondebruin.nl/atp.htm




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écalageentre les calendriers par défaut MAC et Windows, et deux dates identiques sont représentées par des nombresdiffé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 uneanné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 deformatage 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 outilsd'Excel : * Les tris * Les filtres * Les tableaux de données * Les graphiques * Les tableaux croisés dynamiquesPar exemple, un regroupement par périodes (hebdomadaires, mensuelles...) sera impossible si toutes les datesne sont pas formatées de la même manière dans la plage source (données texte mélangées avec des valeursnumé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 modifierles 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 auxsamedis 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 modifiantautomatiquement le format de la cellule en Date si le format de la cellule est Standard avant que la date ou la fonctionne 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 lareprésentation de la date. Ce résultat peut sans aucun doute troubler un utilisateur occasionnel qui ne connaitraitpas 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éesde 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) estsaisie dans une cellule:j renvoie 5jj renvoie 05jjj renvoie mer (nom du jour au format court)jjjj renvoie mercredim renvoie 7mm renvoie 07mmm renvoie juil (nom du mois au format court)mmmm renvoie juilleta et aa renvoient 06aaa et aaaa renvoient 2006jjjj jj mmmm aaaa renvoie mercredi 05 juillet 2007Le 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 heuresou à 60 minutes.Par exemple, utilisez le format personnalisé [hh]:mm:ss pour que la cellule puisse afficher des temps plus grandsque 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 formatstandard 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 calculsulté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. Laformule =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) renvoie31/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, Excelsait é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 janvier1900 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 datede 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 dedé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 le30 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 30du 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 à chaquerecalcul.La date et l'heure sont affichées sous la forme personnalisée JJ/MM/AAAA HH:MM si la cellule contenant la formuleest 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 24heures).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 à unecellule 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) à ladate 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 ledernier jour de la nouvelle échéance (par exemple si vous ajoutez un mois au 31/10/2008, la formule renvoie le30/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 necomptabilisent 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 êtreeffectué 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éfiniepar une référence à une plage de cellules, à une plage nommée ou à une constante matricielle (sous forme denumé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 lavaleur 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 desproblè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 estbasée sur la norme US (La semaine 1 commence le 1er janvier). Cette différence de norme donne donc un résultaterroné 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 lenuméro de semaine est saisie dans la cellule A1) :

Formule =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 de24 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é (valeurpositive) à 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éfiniepar une référence à une plage de cellule, à une plage nommée ou à une constante matricielle (sous forme de numérosde série).








IV-T - TEMPS

La fonction TEMPS renvoie une fraction de 24 heures sous forme décimale, à partir des heures, minutes et secondesspé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 quedate. 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 à 23sera 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 parla 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échargeableen 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'ensembledu 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.

Formule

=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 colonnene correspondant pas à des dates (C31, C32, E32...).

Formule

=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 estsaisie dans la cellule A1.

Formule

=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 enoeuvre un calcul des jours fériés.Il existe deux types de jours fériés.Les dates fixes qui sont facilement identifiables :

Nouvel an

=DATE(Calendrier!$A$1;1;1)








Fête du travail


Armistice 1945


Assomption


Toussaint


Armistice 1918


Noël


Les dates mobiles qui nécessitent un calcul d'identification :

Pâques

=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

Ascension

'La cellule A3 contient la formule de calcul de la date de Pâques=A3+38

Pentecôte

'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'airécupéré la formule sur le net je ne peux le citer. N'hésitez pas à me contacter si vous avez des informations au sujetde 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 lesformules 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 lescellules 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 automatiquementau 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 : ListeJoursOuvresAppliquez 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 supportpour la liste de validation dans la cellule E2. Vous pourrez sélectionner un jour qui sera identifié comme jour de tempspartiel. 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 typed'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 éventuelsjours 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 formulesuivante, 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)=TempsPartielVous 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 sila formule est vraie.Vous disposez maintenant d'un calendrier perpétuel. Changez l'année dans la cellule A1 de la feuille 'Calendrier' pouractualiser 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 colonnesB à 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èrepourront vous être utiles.

Afficher le numéro du jour (pour la date d'aujourd'hui)

=AUJOURDHUI()-DATE(ANNEE(AUJOURDHUI());1;0)

Effectuer une somme conditionnelle pour une période de dates

'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)))}

Soustraire des heures qui sont au format texte '20h15'

=CNUM(SUBSTITUE(A2;"h";":"))-CNUM(SUBSTITUE(A1;"h";":"))

Afficher le nombre de jours dans un mois, pour une date définie dans la cellule A1

=JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))

Afficher la date du jour au format texte

=TEXTE(AUJOURDHUI(); "jjjj jj mmmm aaaa")

Convertir un numéro de mois (saisi dans la cellule A1) en nom de mois

=TEXTE("1/"&A1;"mmmm")

Calculer le temps écoulé pour des heures saisies en A1(début) et A2(fin)

'Les cellules doivent etre au format [hh]:mm=MOD(A2-A1;1)

Extraire le nombre de journées de 8 heures

'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

Vérifier si une date est un jour de semaine ou un week end








Vérifier si une date est un jour de semaine ou un week end'Affiche Vrai si la date saisie dans la cellule A1 est un jour de Week end, sinon renvoie Faux=JOURSEM(A1;2)>5

Calculer le temps écoulé entre 2 dates

'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"

Vérifier si la date correspond au jour anniversaire (Aujourdhui)

'La date est saisie en A1=SI(ET(DATEDIF(A1;AUJOURDHUI();"ym")=0;DATEDIF(A1;AUJOURDHUI();"md")=0);"Oui";"Non")

Contrôler si une personne est majeure.

'La date est saisie en A1=SI(DATEDIF(A1;AUJOURDHUI();"y")>=18;"Majeur";"Mineur")

Calculer l'âge en tenant compte du mois et de l'année de naissance

'La date est saisie dans la cellule A1=ANNEE(AUJOURDHUI()-A1)-1900&" ans "&MOIS(AUJOURDHUI()+1-A1)-1&" mois "

Convertir des secondes (saisies dans la cellule A1) en heures

'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"

Afficher le numéro de semaine pour une date saisie en A1

=ENT(MOD(ENT((A1-2)/7)+0,6;52+5/28))+1

Convertir en heure une somme de minutes

=SOMME(A1:A10)/1440 'Appliquez le format heure à la cellule contenant la formule

Compter le nombre de dates correspondant à un mois spécifique

'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))

Afficher le premier Lundi du mois

=A1-JOUR(A1)+9-JOURSEM(A1-JOUR(A1))








Afficher le premier Lundi du mois'Pensez à adapter le format de la cellule contenant la formule

Afficher le dernier jour du mois

=FIN.MOIS(DATE(TEXTE(A1;"aaaa");TEXTE(A1;"mm");1);0)'Une autre solution :=DATE(ANNEE(A1);MOIS(A1)+1;0)

Afficher le dernier Lundi du mois

=DATE(ANNEE(A1);MOIS(A1)+1;2)-JOURSEM(DATE(ANNEE(A1);MOIS(A1)+1;))

Afficher le dernier jour du mois en cours

=FIN.MOIS(AUJOURDHUI();0)

Afficher le numéro de trimestre

="TRIMESTRE "&ENT((MOIS(A1)+2)/3)

Vérifier si une date appartient à une année bissextile

=SI(MOIS(DATE(ANNEE(A1);2;29))=2;"Bissextile";"Non bissextile")

Convertir un nombre d'heures spécifique en journées (7,4h = 1j)

=(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

Convertir des heures décimales en heures minutes

=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

Convertir des heures minutes en décimales

'Par exemple 10:30 devient 10,5=(A1-ENT(A1))*24

Ajouter 30 minutes à une heure saisie en A1

=A1+TEMPS(0;30;0)

Arrondir à l'heure la plus proche (2:45 devient 3:00)

=ARRONDI(A1/(1/24);0)*(1/24)








Compter le nombre de dates différentes, et qui correspondent à un Dimanche, dans une plage

=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

'Additionner les heures qui correspondent à des dimanches et appliquer un coefficient multiplicateur au résultat

'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

Retrouver le mois en fonction du numéro de semaine et de l'index du jour

'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")

Compter le nombre de jours ouvrés entre 2 dates, les samedi compris

'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.

Compter le nombre de Lundi contenus dans une plage de cellules

=SOMMEPROD((JOURSEM(G3:G20)=2)*1)

Retrouver le dernier jour d'un trimestre

'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)

Afficher le dernier Vendredi du mois

'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

Afficher le 1er jour ouvré suivant

=SERIE.JOUR.OUVRE(A1;1;liste_feries) 'La plage nommée 'liste_feries' contient la liste des jours fériés.








Compter le nombre de mois complets entre 2 dates

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)

Tranformer des secondes en [HH]:MM:SS

=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

Trouver le dernier jour ouvré de l'année 2008

=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.comCré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 2007Reverse 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.

IX - Téléchargement

Téléchargez le calendrier démo.





http://excel.developpez.com/faq/?page=Dates

http://jeannot45.developpez.com/articles/excel/formatspersonnalises/

http://www.developpez.net/forums/u46274/jeannot45/

http://support.microsoft.com/default.aspx/kb/912719/fr

http://www.rondebruin.nl/atp.htm

http://www.developpez.net/forums/u46274/jeannot45/

ftp://ftp-developpez.com/silkyroad/excel/dateheure/ClasseurCalendrier.zip

Documents

Calcul dateheure