X1 – Les Dates dans Excel

1.  Les dates dans Excel

Excel toutes versions

Voir la liste des autres articles publiés

1.1.    Définition

Pour Excel une date est un nombre comme un autre, cependant sa représentation est un peu différente et, en tous cas, plus sophistiquée.

Une date représente un jour dans la semaine, le mois, l’année. Excel sait donc présenter toute date, depuis une origine qui a été fixée au premier janvier 1900. Ce jour est le premier jour, il vaut donc 1.

Aujourd’hui, 17 juillet 2002, nous sommes le 37454e jour écoulé depuis le 01/01/1900.

Une date se décompose en heures (24), minutes (1440), et secondes (86400). On conviendra de dire que midi qui est le milieu de la journée vaut ½ jour, soit 0,5.

Midi, aujourd’hui, peut donc s’écrire 37454,5, minuit s’écrira 37455,0 et demain midi 37455,5.

Il faut donc répartir les 86400 secondes de la journée sur l’unité de jour. Une seconde équivaut à 1/86400e de jour

Partant de ce principe, à toute heure d’un jour correspond un nombre décimal pouvant le représenter, et inversement, à tout nombre décimal correspond une date du calendrier.

Vérifiez que le nombre 29 588 465 sera converti par Excel, si vous le lui demandez, en 31/12/9999 ! et que 32 jours plus tôt (31/12/9999 – 32) sera le 29/11/9999. Mais attention, avant le 01/01/1900, Excel ne reconnaîtra pas les dates et ne saura pas faire de calcul avec elles. Il ne saura pas plus convertir un nombre négatif en date !

1.2.    Affichage de la date

Si vous entrez 37454 dans une cellule, Excel va l’accepter, bien sûr, et l’afficher tel quel. Si la formule possède un format de type Date, Excel transformera votre saisie en 17/07/2002.

Par contre, dans tout type de cellule, le fait de rentrer 17/07/02 appliquera d’office un format de type Date à la cellule.

Excel à recours au Bios du PC pour connaître en permanence la date et l’heure du jour, grâce à une horloge interne sur batterie. Cette heure est affichée en permanence en bas à droite, dans la barre d’outils de Windows. Windows recharge le jour et la date à chaque mise en route

Donc Excel sait en permanence quel jour nous sommes, à quelle année il appartient, et l’heure qu’il est. De plus toutes les machines affichent la même date et le même jour.

Il n’y a donc plus de courriels qui donnent l’impression d’arriver trois semaines (voire plus) après leur envoi, ce décalage étant dû à une défaillance de la batterie du Bios de l’émetteur.

1.3.    Entrer une date

Vous pouvez donc entrer la date du jour sans rien avoir à entrer si ce n’est que taper l’entrée suivante : <Ctrl>+<;> (point-virgule). Cela signifie que vous appuyez sur <Ctrl>, puis en maintenant cette touche enfoncée, vous appuyez sur <;> que vous relâchez immédiatement, puis vous relâchez <Ctrl>.

Supposons que nous soyons encore en 2002.

Pour des dates de l’année en cours, il est inutile de préciser celle-ci, taper 8/5 (ou 8-5) Excel transformera cette entrée en 08/05/2002, si la cellule est formatée en date jj/mm/aaaa, (ou 8/05 ou 8-mai si la cellule n’a pas un format de date, 2002 sera toujours sous-entendu).

Vous aurez aussi la possibilité d’avoir la date du jour affichée en permanence dans une cellule si vous entrez la formule =Maintenant(). Mais attention, la valeur affichée ne se remet à jour que lorsque vous faites un calcul. Exemple : exécutez cette commande et partez boire un café. Lorsque vous reviendrez vous verrez que sur votre écran aucun temps ne s’est écoulé, et pourtant ! Sélectionnez alors une cellule vide tapez un espace et faites <Entrée>, et voici qu’en quelques fractions de secondes se sont écoulées plus de 25 minutes (à boire le café).

Une explication s’impose : vous avez fait une modification dans la feuille, Excel recalcule toutes les feuilles du classeur et en profite pour mettre à jour toutes les formules.

1.4.    Affichage des heures

Pour que ni Excel, ni vous ne vous plantiez tant sur la saisie que sur la lecture, le format des heures est conforme à ce que nous avons mondialement l’habitude d’utiliser sur les affichages numériques de nos montres à quartz et autres systèmes, à savoir 12:00:30 pour 30 secondes après midi. Les anglo-saxons utiliseront la notion AM / PM avec 12 heures seulement (0 à 12 deux fois), nous, nous pourrons utiliser les 24 heures de la journée (0 à 24).

1.5.    Entrer une heure

Sélectionner une cellule et faire <Ctrl>+<:> (deux points) inscrira alors l’heure actuelle.

Pour dire à Excel qu’il est 6 heures 27 minutes 45 secondes de l’après midi, il faudra entrer 18:27:45. Minuit 2 minutes exactement peut s’écrire 0:2:00. Le chiffre des heures ne doit pas manquer sinon il y a erreur de saisie, par contre les secondes sont facultatives si elles sont nulles. Entrer 0:02, et l’affichage devient 00:02, soit 2 minutes. Les secondes sont omises, pas les heures. L’entrée de l’heure est obligatoire. 2:30 n’est pas 00:02:30, mais 02:30:00.

1.6.    Affichage de la date avec les formats personnalisés

Dans la section Nombre de l’Onglet Accueil, déroulez la liste des formats pour sélectionner l’option Autre formats numériques, ou cliquez sur le bouton/flèche à droite de Nombre pour obtenir le formulaire ci-dessous.

Selon le style d’affichage que vous aurez sélectionné, la lecture pourra être très différente d’un style à l’autre.

Pour la même valeur saisie, 15/08/2000, voyez que les résultats sont peu semblables. En revanche, la valeur sous-jacente est bien conservée intégralement.

1.7.    Personnalisation des formats de dates

Vous pourrez aussi vous familiariser à utiliser les formats personnalisés afin que le contenu des cellules fournisse des informations les plus pertinentes.

En effet, vous vous rendrez ridicule si vous établissez un planning pour la deuxième semaine du mois prochain et que vous saisissiez comme date du premier jour de la semaine le « Lundi 12 mars », alors que tout le monde sait que le 12 mars est un mardi…

Il suffit de saisir 12/03 de l’année et afficher cette date au format long. Excel appliquera de lui-même le bon jour de la semaine, donc le mardi. Ce qui devrait vous permettre réagir et de corriger votre saisie en 11/03.

N’oubliez pas que vous pouvez créer à l’infini (ou presque) un format de date personnalisé.

Par exemple : « Prochaine date de réunion le « , « Merci de nous retourner ce document signé avant le « , « Total des participations au « .

Vous n’oublierez pas l’espace final pour séparer le texte de la date qui va s’ajouter en tant que résultat !

1.8.    Calcul sur des dates

Les dates étant des nombres comme les autres, il sera possible de faire toutes sortes de calculs. Combien de temps séparent deux dates avec réponses en jours, mois et jours, heures, etc. Vous aurez certainement d’autres idées.

Il sera aussi possible d’extraire d’un nombre l’année qu’il représente : =Année(nombre), le mois : =Mois(nombre), le jour : =Jour(nombre), nombre représentant une date et pouvant être le contenu de la cellule voisine, ou toute autre cellule : =Jour(A17).

Nota : une formule commence toujours par le signe « = ».

Pour connaître toutes les possibilités de calcul sur les dates, placez-vous dans une cellule et faites Insertion / Fonction et sélectionnez dans la Catégorie de fonction : Date & heure. Cliquez ensuite sur les fonctions disponibles dans Nom de la fonction et vous aurez, en partie basse, un descriptif sommaire de la fonction disponible.

Nous verrons, dans la fiche Formats personnalisés, les différents affichages possibles.

1.9.    Exemples de calculs

1.9.1.   Soustraire des années

Pour soustraire 5 années à la date présente en A1 :

=DATE(ANNEE(A1)-5;MOIS(A1);JOUR(A1))

1.9.2.   Ajouter des années

Pour ajouter 3 années à la date présente en A1 :

=DATE(ANNEE(A1)+3;MOIS(A1);JOUR(A1))

1.9.3.   Ajouter des mois

Pour ajouter 2 mois à la date présente en A1 :

=DATE(ANNEE(A1);MOIS(A1)+2;JOUR(A1))

1.9.4.   Ajouter des jours

Pour ajouter 10 jours à la date présente en A1 :

=DATE(ANNEE(A1);MOIS(A1);JOUR(A1)+10)

1.9.5.   Durée (Âge) en années simples

Attention ce calcul utilise la fonction non répertoriée et non documentée : DATEDIF.

DD est la Date de début, DF est la Date de fin.

DATEDIF calcule le temps écoulé entre la Date de Début et à la Date de Fin et exprime le résultat en années (« y »).

=DATEDIF(DD;DF; »y »)&SI(DATEDIF(DD;DF; »y »)>1; » ans »; » an »)

1.9.6.   Durée (Âge) en années et mois

Attention ce calcul utilise la fonction non répertoriée et non documentée : DATEDIF.

DATEDIF calcule le temps écoulé entre la Date de Début et à la Date de Fin et exprime le résultat en années et mois (« ym »).

La formule ci-dessous s’écrit sur une seule ligne !

=DATEDIF(DD;DF; »y »)&SI(DATEDIF(DD;DF; »y »)>1; » ans, « ; » an, « )&DATEDIF(DD;DF; »ym »)& » mois »

1.9.7.   Durée (Âge) en années, mois et jours

Attention ce calcul utilise la fonction non répertoriée et non documentée : DATEDIF.

DATEDIF calcule le temps écoulé entre la Date de Début et à la Date de Fin et exprime le résultat en années, mois et jours (« y », « ym » et « md »).

La formule ci-dessous s’écrit sur une seule ligne !

=DATEDIF(DD;DF; »y »)&SI(DATEDIF(DD;DF; »y »)>1; » ans, « ; » an, « )&DATEDIF(DD;DF; »ym ») & » mois, « &DATEDIF(DD;DF; »md »)&SI(DATEDIF(DD;DF; »md »)>1; » jours »; » jour »)

1.9.8.   Nombre de jours dans le mois

D est la date considérée. Elle peut se trouver en A1.

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

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

1.9.9.   Nombre de jours du mois précédent

=JOUR(TEXTE(A1; »m/aaa »)-1)

1.9.10. Date du dernier jour du mois

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

1.9.11. Date du premier jour du mois

=DATE(ANNEE(A1);MOIS(A1);1)

1.9.12. Nom du jour de la semaine

=TEXTE(A1; »jjjj »)

1.9.13. Date du premier lundi du mois

=TEXTE(A1; »m/aaa »)-MOD(TEXTE(A1; »m/aaa »)-3;7)+6

1.9.14. Date du premier lundi de l’année

=DATE(ANNEE(A1);1;1)-MOD(DATE(ANNEE(A1);1;-2);7)+6

 

 

 

Voir la liste des autres articles publiés

X1 – Les Dates dans Excel

One thought on “X1 – Les Dates dans Excel

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *