Cet article va vous expliquer comment calculer une date à partir d'un numéro de semaine quelconque.

Numéro de semaine ISO

Avant de vous montrer les formules, il est important d'avoir à l'esprit qu'il y a 2 façon de comptabiliser les numéros de semaine

• La méthode Nord-Américaine (les formules NO.SEMAINE dans Excel)
• La méthode international (la formule NO.SEMAINE.ISO)

En Europe, le calcul des numéros de semaine est régie par la norme ISO 8601. Cette norme considère que la première semaine de l'année doit avoir au minimum 4 jours. Lorsque la première semaine de l'année commence un vendredi, un samedi ou un dimanche, ces jours ne sont pas considérés comme la première semaine de l'année.

Mais pour l'Amérique du Nord, cette règle n'existe pas. Le calcul des numéros de semaine commence dès le 1er Janvier, même si le 1er Janvier est un dimanche.

Avec Excel, on peut afficher cette différence de la façon suivante :

• Avec la fonction NO.SEMAINE, quelques soit le premier jour de l'année, la fonction retourne toujours la valeur 1.
• Avec NO.SEMAINE.ISO, si le premier jour de l'année est un vendredi, samedi ou dimanche, la fonction considère que la date du 1er Janvier appartient à la semaine de l'année précédente.

Cette notion est importante dans le résultat que nous allons obtenir

Formule généralement utilisée

La formule généralement utilisée pour retourner le premier jour d'une semaine (semaine qui commence un lundi) est la suivante :

=DATE(Année;1;1)+(Semaine-1)*7-JOURSEM(DATE(Année;1;1);2)+1

Si la semaine commence un dimanche, on retire simplement le +1 à la fin

=DATE(Année;1;1)+(Semaine-1)*7-JOURSEM(DATE(Année;1;1);2)

Erreur commise avec la formule ❗❗❗

Mais cette formule ne prend pas en compte le problème des numéros de semaines ISO. Cette fonction marche pour les Etats-Unis et le Canada, mais pas pour les autres pays 😲

Pour s'en rendre compte, le tableau suivant montre le problème

• La valeur des années en colonne A
• Le jour du premier janvier en colonne B ; =DATE(A2;1;1)
• Le résultat du calcul de la date à partir de la première semaine
• Le numéro de semaine ISO

Dans le tableau précédent, on constate que la date calculée pour la première semaine est souvent en décembre. En fait, la formule retourne le mois de janvier seulement si le 1er janvier est un lundi 😟🤔

Maintenant, si on considère l'année 2016, on peut voir que le 01/01/2016 est un vendredi. Et donc, le lundi calculé sera le 28/12/2015. Or dans ce cas de figure, nous ne sommes plus en semaine 1 (norme ISO)

D'où un décalage d'une semaine par rapport aux normes de calculs en Europe 😡😡😡

Donc, la formule précédente doit être adaptée pour gérer la règle des numéros de semaines ISO.

Formule qui prend en compte la règle des semaines ISO

Compte-tenu des remarques précédentes la formule qui permet de calculer une date à partir du numéro de semaine en respectant la règle des semaines ISO est la suivante

=DATE(Année;1;1)+SI(JOURSEM(DATE(Année;1;1);2)<5;(Sem-1)*7;Sem*7)-JOURSEM(DATE(Année;1;1);2)+1

Nous pouvons voir qu'avec cette formule, systématiquement, le numéro de semaine ISO (NO.SEMAINE.ISO) est toujours égal à 1.

Simplification avec LET

La formule précédente reprend plusieurs fois les mêmes éléments. Dans cette situation, la fonction LET va nous permettre de simplifier l'écriture et remplaçant certaines parties par une variable

=LET(Janv1;DATE(Année;1;1);
Jour1;JOURSEM(DATE(Année;1;1);2);
Janv1+SI(Jour1<5;(Semaine-1)*7;Semaine*7)-Jour1+1)