Lorsque l’on travaille sur des dates avec Excel, on est très souvent mis en difficulté. Il faut dire qu’il y a tellement de facteurs à prendre en compte… Les jours ouvrés, les jours fériés, le nombre de jours par mois qui varie… Un enfer !
Mais quand on connait toutes les petites astuces pour les manipuler, ça devient littéralement un jeu d’enfant et on peut facilement intégrer tout et n’importe quoi à nos tableaux.
Dans ce cas précis, on a besoin de lister automatiquement les derniers jours de tous les mois pour l’année 2023 dans nos cellules. Ce qui peut sembler chronophage et compliqué, par exemple pour le mois de février dont on ne sait jamais s’il compte 28 ou 29 jours…Mais avec cette formule, en plus d’être efficace et dynamique, on va pouvoir le faire en un éclair.
Top chrono !
La formule magique
Pour réaliser la recette de cette formule vous aurez besoin de ces ingrédients :
- Une fonction SEQUENCE : pour que la formule s’étende sur 12 lignes (12 mois)
- Une fonction DATE : pour créer les dates
- Un format personnalisé : pour les afficher au format avec le jour de la semaine + la date
- Une fonction FIN.MOIS : pour obtenir le DERNIER jour
Et en mixant le tout, attention SPOILER : =FIN.MOIS(DATE(2023;SEQUENCE(12);1);0)
Mais décryptons ce calcul méthodiquement pour savoir comment assembler ces fonctions simplement au lieu de sauter les étapes.
Etape 1 : une fonction qui créer 12 nombres en une seule fois
Plus besoin de faire des formules puis de viser le coin inférieur droit de la cellule pour étirer jusqu’en bas de notre colonne. Tellement has been !
La fonction SEQUENCE et le modern Excel vont tout simplement nous permettre de créer non pas les dates une par une, mais les 12 en un seul coup !
Pour cela nous allons tout simplement commencer par appliquer automatiquement notre formule sur 12 lignes. La réponse : =SEQUENCE(12)
Il suffira d’une petite pression sur la touche ENTRÉE pour constater que les cellules constituant notre tableau seront numérotées de 1 à 12. Mais pour l’instant pas de date… Pas de panique, ça vient tout de suite !
Etape 2 : Transformer nos données en dates
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. Voilà le résultat de notre calcul dans notre colonne. Mais toutes ces données sont amenées à devenir des dates. Comment faire ?
Il existe sur Excel une fonction nommée DATE (on peut difficilement faire plus intuitif).
La fonction DATE va permettre de transformer chacun de ces nombres en une date correspondant au premier jour de chacun des mois. Et pour ça il nous faudra 3 arguments
- L’année : L’exercice se basant sur l’année 2023 on va tout simplement saisir « 2023 » dans le premier argument,
- Le mois : rien besoin d’écrire il existe déjà. Le mois (ou plutôt les mois) c’est tout simplement notre fonction SEQUENCE et nos nombres de 1 à 12 qui vont servir de 2ème argument,
- Le jour : on cherche le premier jour de chaque mois donc on va juste écrire le chiffre « 1 ».
On valide et la magie opère : SPOILER : =DATE(2023;SEQUENCE(12);1)
Tous nos nombres ont été transformés et sont devenus les premiers jours de chacun des 12 mois de l’année 2023.
Enfin ça ne se voit pas au premier coup d’œil car pour le moment ces nombres n’évoquent pas grand-chose…
Etape 3 : afficher un résultat … en date
À part sur la première ligne, pour l’instant nos nombres sont assez insignifiants. Causes ? Les dates sont des nombres et pour qu’elles soient affichées comme des dates, il faut choisir le bon format.
Dans le chapeau des options il en existe une simple et efficace.
Ce que l’on souhaite c’est qu’elles renvoient deux choses :
- Le jour de la semaine
- La date complète (jour/mois/année)
Aucun des formats standards proposés ne permet de générer toutes ces informations, mais nous allons fouiller du côté des formats personnalisés. Ensuite il suffit de saisir ou de sélectionner le format : « jjjj jj/mm/aaaa ».
jjjj : pour afficher le jour de la semaine en toutes lettres
jj/mm/aaaa : pour afficher la date avec les jours, les mois et les années
On clique sur le bouton « OK » et mission accomplie, on a enfin le bon résultat. Les premiers jours de chaque mois de l’année 2023 sont révélés dans le bon format.
Etape 4 : Du premier jour du mois au dernier
Plus qu’une seule chose à faire désormais dans notre cellule : transformer le résultat en : le DERNIER jour des mois et non le premier.
Et pour cela il existe la fonction ultime : FIN.MOIS (inutile d’expliquer son utilité c’est assez limpide là encore).
On va donc insérer cette fonction au début de notre formule (juste après le « = » ).
Deux arguments :
- Mois de quelle date ? De celles générées par notre formule DATE + SEQUENCE.
- En décalant de combien de mois ? de 0 dans ce cas précis car on ne veut rien décaler, donc on écrit le chiffre “0”.
Réponse finale : =FIN.MOIS(notre formule;0)
Et voilà comment en 4 étapes, 3 fonctions et un temps record nous avons pu générer la liste des 12 derniers jours des mois de l’année 2023 ! Et il est bien sûr possible de le faire sur plusieurs années.
Pour s’inscrire à l’une de nos formations Excel c’est par ici !
