Dans ce cours de votre blog Formation Excel, vous allez découvrir comment résoudre des problèmes liés à la modification des années d'une série de dates.
J’ai une colonne contenant 215 dates dont l’année est 2020, je veux modifier en une seule fois l’année 2020 par 2021 en laissant le jour et le mois intacts pour toutes ces dates.
- Comment faire ?
Je peux procéder de deux façons :
1- Utiliser l’outil Remplacer :
Je sélectionne toute ma colonne puis je compose Ctrl+H.
Excel m’affiche la boite de dialogue Rechercher et Remplacer
en activant l’onglet Remplacer.
Je tape donc 2020 dans la zone Rechercher et 2021 dans la
zone Remplacer par.
Je clique ensuite sur Remplacer tout.
Et voilà, la modification est bien effectuée.
Attention !
Veuillez vérifier le résultat obtenu; en effet et puisque l’année 2020 est bissextile, vous avez 29 jours en mois du février, mais après la modification la colonne de dates va afficher 29/02/2021 ce qui n’est pas juste pour l’année 2021 qui est une année commune (remarquez qu’Excel l’aligne à gauche pour vous signaler qu’il y a un problème avec cette date). Vous devez donc supprimer cette valeur.
2- Utiliser la fonction MOIS.DECALLER
Je vais me servir d’une autre colonne pour modifier 2020 en
2021 en utilisant la fonction MOIS.DECALLER qui a pour fonction de renvoyer la
date à partir du nombre de mois indiqué.
Sa syntaxe est : MOIS.DECALER(date_début;mois)
Par exemple dans la cellule B2 je tape =MOIS.DECALER(A2;12)
- A2 contient la date de départ.
- 12 est le nombre de mois à ajouter à ma date de départ, équivalent à un an bien sûr.
Ce qui donne : 02/01/2021.
Je copie la formule vers le bas et j’obtiens rapidement mes nouvelles
dates avec comme année 2021.
Remarque :
Remarquez que la date 29/02/2020 est modifiée en 28/02/2021,
ce qui est souhaité, mais le problème c’est que j’ai obtenu cette date en
double.
Je peux donc la supprimer ou bien demander à Excel de supprimer
les doublons dans cette nouvelle colonne.
Pour supprimer alors les doublons et actualiser ma colonne de
dates, je dois tout d’abord convertir les formules contenues dans les cellules
de cette colonne en valeurs dates pour éviter la réexécution de ces formules après
suppression.
Je sélectionne donc toute ma colonne puis je clique sur Copier et sur Coller les valeurs.
Je clique maintenant sur le bouton Supprimer les doublons qui
se trouve dans le groupe Outils de données sous l’onglet Données puis je
valide.
Voilà donc ma liste de dates est bien réajustée.
Un autre problème !!
Dans le cas d’une année commune qui vient juste avant une
année bissextile, Excel sautera le 29ème jour du mois de février.
Je dois donc réadapter les dates qui viennent après le 28/02
en tapant par exemple :
=B59+1 et de faire copier vers le bas.
👉 Utiliser une formule dynamique
En tout cas, je vais utiliser une formule qui va afficher la
bonne liste de dates qui sera ajustée automatiquement quelques soit le type d’année saisie dans la première
colonne sans avoir besoin à la réadapter manuellement à chaque fois.
Dans la cellule B2 je tape la formule suivante :
=SI(MOD(ANNEE(A2);4)=0;SI(A2>=DATE(ANNEE(A2);2;29);MOIS.DECALER(A2;12)+1;MOIS.DECALER(A2;12));SI(A2>=DATE(ANNEE(A2);3;1);MOIS.DECALER(A2;12)-1;MOIS.DECALER(A2;12)))
- SI(MOD(ANNEE(A2);4)=0 permet de vérifier d’une façon dynamique si l’année de la date saisie dans la cellule A2 est bissextile, c’est à dire que le reste de la division de cette année par 4 égale 0.
- Si oui, dans ce cas il faut vérifier cette fois si la date saisie dans la cellule A2 est supérieure ou égale à 29/2/aaaa: (aaaa correspond à l'année saisie) : SI(A2>=DATE(ANNEE(A2);2;29)
- Si oui, je demande à Excel d’ajouter un an et un jour à la date que contient A2 : MOIS.DECALER(A2;12)+1;
- Si non, Excel doit ajouter un an à cette date : MOIS.DECALER(A2;12)
- En revanche, si l’année est commune, je demande à Excel de vérifier une autre condition : Si la date saisie dans A2 est supérieure ou égale à 1/3/aaaa: SI(A2>=DATE(ANNEE(A2);3;1)
- Si oui, Excel doit décaler la date d’un an moins un jour : MOIS.DECALER(A2;12)-1
- Si non ; Excel doit ajouter un an à la date saisie dans A2 : MOIS.DECALER(A2;12)
En validant la formule et en la copiant vers le bas, Excel
renvoie ces résultats :
Ce que j'ai ajouté, c'est une autre condition : SI(OU(MOD(ANNEE(A2);4)=1;MOD(ANNEE(A2);4)=2) qui va vérifier si le reste est 1 ou 2 puis de renvoyer le résultat correspondant en exécutant cette formule MOIS.DECALER(A2;12).
Cours super intérressant et très utile. J'ai essayé pour faire mon plannig de l'année prochaine et ça fonctionne bien. Merci beaucoup pour tous vos conseils.
RépondreSupprimerContent que ce cours vous a rendu ce service.
Supprimer