Calculer une date à partir du numéro de semaine

Temps de lecture : 3 minutes

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 :

Calcul du numéro de semaine avec le 1er Janvier
  • 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
Tableau de calcul de la premiere semaine de lannee

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

Formule date en fonction du numero de semaine

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)

Calcul du jour de la semaine 14 avec la fonction LET

5 Comments

  1. Efgé
    21/01/2023 @ 21:17

    Bonjour à tous et merci à Frédéric pour son partage.

    Il me semble que la formule ne fonctionne pas lorsque le 1er janvier n'est pas en semaine 1.
    Pour 2023 le 1er janvier est en semaine 52 et la formule renvoie le 25/12/2023.
    Une idée de modification pour 365 :
    =LET(MonAn;SI(ET(NO.SEMAINE.ISO(DATE(Année;1;1))>51;Semaine>51);Année-1;Année);
    Janv1;DATE(MonAn;1;1);
    Jour1;JOURSEM(DATE(MonAn;1;1);2);
    Janv1+SI(Jour1<5;(Semaine-1)*7;Semaine*7)-Jour1+1)

    Cordialement

    Reply

    • Frédéric LE GUEN
      23/01/2023 @ 18:19

      Super cette formule, je vais la rajouter dans l'article

      Reply

  2. Émélie Delisle
    17/10/2022 @ 20:48

    Bonjour - je cherche une formule qui permettrait de calculer le nombre de semaines d'un congé de maternité et parental à partir d'une date précise: Ex : si le congé débute le 14 novembre - calculer 15 semaines de maternité (ddate X) et ensuite 25 semaine de parental (Date x)

    Reply

  3. LAFOURCADE
    12/10/2022 @ 15:36

    Bonjour,

    Merci pour votre article.
    Je cherche à faire exactement l'inverse : trouver le 1er jour de la semaine au format date en saisissant le numéro de semaine.
    Comment faire svp ?

    Cdt,
    Jean-Baptiste

    Reply

    • Frédéric LE GUEN
      13/10/2022 @ 12:19

      Bonjour, Oui c'est possible mais il faudrait que je fasse un article spécifiquement pour cela
      En attendant, dans cet article (étape 3) la formule présentée peut vous aider

      Reply

Leave a Reply

Your email address will not be published. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

RECHERCHE D’EMPLOI AVEC