Excel et les formules sur les dates, ça tourne parfois au casse-tête ! Dans une précédente astuce, nous avions abordé la problématique de calcul du lundi qui précède. Intéressons-nous ici à un problème similaire, mais qui se formule très différemment : le calcul du lundi qui suit. Vous trouverez dans cet article une formule immédiatement utilisable, ainsi que le détail de sa construction.Formule pour Excel 2007 Excel 2010 Excel 2013
Le scénario est simple : vous disposez, dans une cellule formatée au format Date, d’une date de départ. Dans notre exemple, ce sera la cellule A1. Vous souhaitez ensuite obtenir par calcul, par exemple dans la cellule A2 elle aussi formatée au format Date, la date du prochain lundi. Si la date A1 est déjà un lundi, la formule doit simplement retourner la même date en A2 (le prochain lundi, c’est le même lundi).Nous allons utiliser la fonction JOURSEM qui permet de récupérer, sous forme de nombre, le jour de la semaine qui correspond à une date. Par défaut, si on ne spécifie pas de second argument, la fonction JOURSEM renvoie 1 pour dimanche, 2 pour lundi, 3 pour mardi, etc. jusqu’à 7 pour samedi. On pourrait changer cela, mais ce n’est pas nécessaire.
Vous êtes pressé ? Voici la formule pour la cellule A2 :
=A1+2-JOURSEM(A1)+(JOURSEM(A1)>2)*7
Et voici les explications détaillées, qui illustrent très bien la subtilité des formules Excel.
L’idée de notre formule est d’ajouter à la date de départ autant de jours supplémentaires que nécessaire pour « arriver » au prochain lundi.
Si JOURSEM(A1) vaut 1 (dimanche), il faut ajouter 1.
Si JOURSEM(A1) vaut 2 (lundi), il faut ajouter 0.
Si JOURSEM(A1) vaut 3 (mardi), il faut ajouter 6.
Si JOURSEM(A1) vaut 4 (mercredi), il faut ajouter 5.
Si JOURSEM(A1) vaut 5 (jeudi), il faut ajouter 4.
Si JOURSEM(A1) vaut 6 (vendredi), il faut ajouter 3.
Si JOURSEM(A1) vaut 7 (samedi), il faut ajouter 2.
Formuler cette suite de conditions telle quelle dans Excel, c’est un peu compliqué. Aussi, ajustons un peu le problème pour le rendre « calculable », et utilisons la valeur de 2-JOURSEM(A1). Pourquoi cette valeur ? Parce qu’elle présente la particularité très intéressante d’être nulle (0) si A1 est un lundi. Et aussi, au passage, de valoir 1 si A1 est un dimanche, ce qui est exactement le nombre de jours qu’il faut rajouter à un dimanche pour passer au lundi !
Si A1 est un dimanche, 2-JOURSEM(A1) vaut 1.
Si A1 est un lundi, 2-JOURSEM(A1) vaut 0.
Si A1 est un mardi, 2-JOURSEM(A1) vaut -1.
Si A1 est un mercredi, 2-JOURSEM(A1) vaut -2.
Si A1 est un jeudi, 2-JOURSEM(A1) vaut -3.
Si A1 est un vendredi, 2-JOURSEM(A1) vaut -4.
Si A1 est un samedi, 2-JOURSEM(A1) vaut -5.
Ainsi, si A1 est un lundi, en ajoutant à A1 la valeur de 2-JOURSEM(A1) (qui vaut 0), on obtient donc le même jour. C’est bien ce que l’on cherche à obtenir, et c’est donc un très bon départ !
Si A1 est un dimanche, 2-JOURSEM(A1) valant 1, en ajoutant à A1 la valeur de 2-JOURSEM(A1), on obtient le jour suivant, soit le prochain lundi. Ça marche aussi !
Problème : il faut également gérer les autres cas, du mardi au samedi, pour lesquels ajouter à A1 la valeur de 2-JOURSEM(A1) ne donnera pas du tout le résultat escompté.
Dans les listes ci-dessus, vous constatez que ces cas correspondent aux valeurs -1 à -5 de 2-JOURSEM(A1) (voir la deuxième liste), qui doivent être associées à l’ajout de 6 à 2 jours (voir la première liste).
Et si on rajoutait 7 à 2-JOURSEM(A1) ? Bingo, cela donne exactement les valeurs de la première liste que l’on cherche à obtenir !
Si A1 est un mardi, 2-JOURSEM(A1)+7 vaut 6.
Si A1 est un mercredi, 2-JOURSEM(A1)+7 vaut 5.
Si A1 est un jeudi, 2-JOURSEM(A1)+7 vaut 4.
Si A1 est un vendredi, 2-JOURSEM(A1)+7 vaut 3.
Si A1 est un samedi, 2-JOURSEM(A1)+7 vaut 2.
Dernier problème : comment faire une formule unique qui prenne en compte les deux cas particuliers du dimanche et du lundi, auxquels il ne faut rien rajouter du tout ? Avec une portion de formule conditionnelle, bien sûr !
Dimanche et lundi correspondent aux cas où JOURSEM(A1) est égal soit à 1 (dimanche), soit à 2 (lundi). Donc, au final, il faut ajouter 7 uniquement si JOURSEM(A1) est strictement supérieur à 2, sinon il ne faut rien ajouter (ou ajouter 0, ce qui revient au même). Cela se formule très simplement avec cette portion de formule : (JOURSEM(A1)>2)*7.
Pour les cas de dimanche et lundi, (JOURSEM(A1)>2) vaudra FAUX, donc 0. Et zéro multiplié par sept égale zéro. Pour les autres jours, (JOURSEM(A1)>2) vaudra VRAI, donc 1. Et un multiplié par sept égale sept. Le tour est joué !
Et voilà donc notre formule finale :
=A1+2-JOURSEM(A1)+(JOURSEM(A1)>2)*7
Et si dans le cas où la date A1 est un lundi, on veut obtenir non pas le même lundi, mais celui de la semaine suivante ? C’est très simple : il faut rajouter 7 aussi dans le cas où JOURSEM(A1) vaut 2. Comment ? Dans la formule finale, remplacez le signe « strictement supérieur » (>) par « supérieur ou égal » (>=). C’est assez magique, Excel, non ?