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çons de comptabiliser les numéros de semaine
- La méthode Nord-Américaine (les formules NO.SEMAINE dans Excel)
- La méthode internationale (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 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 :
- NO.SEMAINE, quelques soit le premier jour de l'année, la fonction retourne toujours la valeur 1.
- 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)
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 en remplaçant certaines parties par une variable.
=LET(Janv1;DATE(Année;1;1);
Jour1;JOURSEM(Janv1;2);
Janv1+SI(Jour1<5;(Semaine-1)*7;Semaine*7)-Jour1+1)
Nicolas
Frédéric LE GUEN
guillaume
Loïc
Caro
Efgé
Émélie Delisle
LAFOURCADE
07/09/2023 @ 17:19
Bonjour à tous,
dans un esprit d'excelangisme de connaissance, voici mes remarque et proposition:
par rapport aux formule no.semaine et no.semaine.iso:
no.semaine.iso est apparu dans Excel 2013, mais depuis la version précédente, la formule no.semaine peut etre utilisé avec le paramètre 21 pour sortir le même resultat (Semaine ISO). (perso, j'utilisais toujours no.semaine ne sachant pas que l'autre existait).
Pour le calcul inverse, au lieu de joué avec des "si", on peut faire une soustraction en modifiant le paramètre de joursem à 15 pour trouvé le 1er jour de l'année, on obtient ainsi le dernier jeudi de l'année précédente (+ 4 pour avoir le premier lundi de l'année):
pour garder la structure de la fonction tel que présenté avant:
=LET(Janv1;DATE(Année;1;1);
jour1;Janv1-JOURSEM(Janv1;15)+4;
jour1+(Semaine-1)*7)
pour simplifié (car le let en 2 étape n'est plus utile):
=LET(Janv1;DATE(Année;1;1);
Janv1-JOURSEM(Janv1;15)+4+(Semaine-1)*7)
Cordialement
08/09/2023 @ 05:37
Bonjour,
alors l'astuce du 21 remonte à bien plus longtemps que ça (version 2010 de mémoire). Toutes les innovations apportées à Excel le sont dans le but de simplifier le travail. Il fallait avouer que bien peu de personnes savaient qu'il fallait rajouter ce paramètre à la fonction NO.SEMAINE et donc ça introduisait des erreurs de calcul pour les pays Européens. D'où la création de la fonction NO.SEMAINE.ISO pour bien faire la distinction entre les 2 calculs.
Ensuite, je fais toujours en sorte que mes articles soient en phase avec les dernières nouveautés. Et il m'arrive très fréquemment de remettre à jour des articles qui présentaient des solutions, non pas obsolètes, mais assez complexes à écrire. L'arrivée des fonctions FILTRE et SEQUENCE ont énormément changé la façon de concevoir des fonctions Excel. C'est pour ça que l'explication du paramètre 21 était dans l'article il y a encore qques années, mais je l'ai retiré depuis longtemps.
Et pour la fonction LET, j'ai surtout voulu montrer qu'on pouvait faire des appels de LET imbriqués. Mais merci pour la simplification.
24/07/2023 @ 10:42
Bonjour, j'essaie de reprendre la formule de EFGE mais ça ne fonctionne pas, je ne suis pas sur des valeurs que je dois mettre : j'ai bien remplacé semaine par ma cellule semaine et année par ma cellule année mais j'oublie peut être certaine valeur, par exemple je ne comprend pas "MonAn"
Je suis débutant sur Excel, merci de votre compréhension.
24/07/2023 @ 11:27
Bonjour, déjà, je vous remercie de préciser votre niveau.
Attention, la fonction LET est tout sauf simple et demande pas mal de connaissances avec Excel. Je vous conseillerais dans un premier temps d'utiliser les autres formules de l'article.
Maintenant, si vous débutez sur Excel, je vous conseillerais de faire les quiz et exercices en lignes.
Ce n'est pas pour avoir une note, mais pour voir et apprendre les possibilités proposées par Excel et elles sont très nombreuses.
Bien à vous
18/07/2023 @ 13:12
Bonjour (je ne peux pas répondre à ou modifier mon post)
J'ai vérifié mes résultats en comparaison des exemple.
Avec cette formule :
=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)
Pour la semaine 14 de 2015 j'obtiens : 06.04.2015 et non le 30.03.2015
Bonne journée
18/07/2023 @ 13:02
Bonjour,
J'ai appliqué la dernière formule (LET) pour récupérer le mois et le 1er jour d'un numéro de semaine selon une année définie.
Dans certains cas il y a un décalage de 7 jours.
Par exemple la semaine 3 de 2013 me dit que c'est le 21.01.2013 au lieu du 14.01.2013.
C'est comme si cette formule considère que la semaine 01 est la 1ère semaine 100% en janvier.
En 2013 la semaine 1 commence le 31.12.2012. Mais Excel la fait commencé 07.01.2013.
Bonne journée
20/07/2023 @ 03:47
Bonjour,
Je viens de vérifier la formule et pour la 3e semaine de l'année 2013, la fonction LET retourne bien le 14 janvier. Je viens d'ailleurs de changer l'image d'illustration.
27/04/2023 @ 15:27
Bonjour,
J'ai fait la formule : "=DATE(B1;1;1)+(C1)*7-JOURSEM(DATE(B1;1;1);2)+1"
Ce qui me fait 24/04/2023, on est OK
Maintenant ce que je voudrais serait que ce soit "24/04/2023 au 28/04/2023" (du lundi au vendredi).
Est-il possible de le faire ? si, oui comment ?
Merci d'avance
Caro.
27/04/2023 @ 16:00
Vous pouvez utiliser les formules dans cet article pour calculer n'importe quel jour de la semaine https://www.excel-exercice.com/formules-pour-calculer-les-jours-de-la-semaine-dans-le-mois/
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
23/01/2023 @ 18:19
Super cette formule, je vais la rajouter dans l'article
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)
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
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