Une formule dure à comprendre…

La formule ci-dessous serait due à Laurent Longre, qui avait entre autres créé dans le temps une DLL appelée MoreFunc.DLL.

L’objectif est d’identifier le premier lundi de la semaine indiquée en B2 pour l’année en B1.

Voici la formule placée en B4 :
=somme(mod(("1/"&B1)-{0;6};{1E+99;7})*{1;-1})+B2*7-4

Pouvez-vous comprendre comment cette formule s’y prend pour obtenir le bon résultat ?

Les explications sérieuses suivantes proviennent du MVP Craig Hatmaker, qui a fourni une analyse détaillée du processus utilisé. Il a dû tirer cela de son chapeau… Les explications plus farfelues sont de votre serviteur.

La première date normale dans Excel est 1, pour le dimanche 1^er janvier 1900.

La première date possible dans Excel est 0, pour le samedi précédent. Toute valeur négative est refusée comme date.

On voit bien là que Bill Gates est un peu un extraterrestre puisqu’il fait commencer Excel à une date qui n’existe pas. Steve Jobs, qui n’était pas un extraterrestre, a fait débuter Excel Mac sur le 1er janvier 1904, ce qui fait qu’il y avait 4 ans et 1 jour d’écart entre les deux calendriers. Vous pouvez le vérifier en passant par les options d’Excel, dans la section « Options avancées », bloc « Lors du calcul de ce classeur ».

"1/"&B1 donne le 1^er janvier de l’année en B1.

("1/"&B1)-{0;6} donne le 1^er janvier de l’année et 6 jours plus tôt.

1E+99 est un nombre si grand que n’importe quelle date divisée par ce nombre a un reste égal à la date. Si nous remplaçons ce grand nombre par 1000000, nous obtenons le même résultat…

{1er janvier;Reste} * {1 ;-1} donne {1er janvier;-Reste}

Somme({1er janvier;-Reste}) donne le vendredi précédant le lundi cherché

Quand la semaine cherchée est 1, alors 7*semaine – 4 = 3 : on cherche donc le troisième jour après le vendredi cité ci-dessus, ce qui est bien un lundi !

En général, dans ce blog, j’essaye de vous apprendre à faire simple. 

Aujourd’hui, pour changer, c’était l’inverse. Mais un peu de stimulation intellectuelle pour disperser les vapeurs de l’alcool de fin d'année, c’est peut-être bien utile…

J’espère que cela ne vous empêchera pas de bien profiter de votre réveillon…

Bonne année !