Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
Calculer un numero de semaine en fonction d'une date
Bonjour à tous,
J'aimerais, comme l'intitulé l'indique, calculer un numero de semaine en fonction d'une date. J'ai effectué beaucoup de recherche mais ca ne marche toujours pas. J'ai testé plein de formules que j'ai récupérer sur les forums mais ca ne marche pas. Par exemple, pour la date du 31/12/2008, le résultat affiché est 1 alors qu'il devrait être 52. Si quelqu'un pouvait m'aider. Merci d'avance.
Merci pour cette synthèse de différentes approches et formules.
J'en profite pour faire des propositions pour compléter ce fichier, très instructif:
- il me semble qu'une colonne manque à savoir la référence ISO qui donne le LA, que l'on pourrait obtenir en appliquant les règles précises énoncées. J'ai essayé de trouver ce f... calendrier officiel, sans y être parvenu -
- à partir de cela, on pourrait, sur une période plus large que celle retenue, mettre en évidence les dates de premières divergences des différentes formules proposées, en choisissant les dates critiques (débuts et fins d'année, et visiblement autour de février)
Par exemple entre la "formule de Monique" et la formule astussss avec des constantes matricielles ({....}), j'ai observé une divergence en l'an 2105. Dans mon cas personnel, je prépare quelque chose qui ne durera pas au-delà de 10 ans, donc la formule plus simple de Monique me convient parfaitement. Peut-être même existe-t-il une formule encore plus simple sur une durée plus limitée encore. Après à chacun de choisir la formule qui lui va bien en fonction de son horizon temporel !
- la fonction inverse, qui permet de passer du numéro de semaine à une date, par exemple celle du lundi de la semaine n° x est aussi utile. Je l'ai construite en partant du constat que le 4 janvier est toujours dans la première semaine de l'année (source ISO). Cela donne la formule suivante :
Format pour identifier une semaine : 0935 dans $A$1 (35ème semaine de 2009)
Résultat : 24/08/2009
Formule = DATE(2000+ENT($A$1/100);1;4)-JOURSEM(DATE(2000+ENT($A$1/100);1;4);2)+1+7*(MOD($A$1;100)-1)
(la formule est rendue compliquée par la séparation de la semaine 0935 en 09 (l'année) et 35 (la semaine recherchée) sinon c'est super simple)
Principe du calcul : le 4 janvier est dans la semaine 1 ; on ajoute à cette date 1-JOURSEM(4/01/yy) pour trouver le lundi (qui peut être en année YY-1, mais peu importe) ; on ajoute 7 fois le numéro de semaine pour obtenir la date du lundi de la semaine donnée.
Si quelqu'un veut bien se donner la peine de vérifier, deux paires d'yeux valent mieux qu'une !
Re : Calculer un numero de semaine en fonction d'une date
Bonjour à tous
Jean-Claude: Bon premièrement merci pour ton super fichier sur les semaines. Je me suis même vu cité, mais je te rassure, cette formule n'est pas de moi, je l'avais récupérée sur le net il y a quelques années.
Mais bon comme tu n'avais pas encore la petite application pour retrouver ses fichiers facilement, tu n'avais pas pu le retrouver (et pourtant, tu te disais, aH! mais j'ai déjà fait un fichier pour ce problème).
Sinon, il y a peu j'avais mis un fichier dans ce post qui fait aussi le point sur le numéro de semaine à partir d'une date:
Deux remarques :
Remarque 1 : suis-je le seul à ne pas connaître le coup de = SOMME(MOD(X;{1E99;Y})*{1;-1}) pour exprimer élégamment X-MOD(X;Y), tellement çà paraît une évidence pour tous ? Y a-t-il d'autres classiques du même genre ?
Remarque 2 : en fait j'utilise dans $A$1 la valeur numérique 935 qui avec le format "S"0000 donne la présentation explicite pour l'utilisateur S0935. En effet, je trouve plus facile de manipuler des valeurs numériques pour, par exemple, ajouter des semaines. Ta formule marche bien avec la valeur alphanumérique "0935" mais est troublée par 935 (on obtient alors 24/08/2093 au lieu de 24/08/2009, et on comprend bien pourquoi).
Tu me diras avec raison qu'il n'y a plus qu'à attendre un peu plus d'un mois pour que le problème ne se pose plus !!
Du coup, j'ai essayé de trouver une formule plus compacte, au détriment de la compréhensibilité ... et je propose après quelques essais la formule suivante qui permet, je rappelle l'objectif, de trouver la date du lundi d'une semaine identifiée par son numéro sous la forme d'un nombre YYSS où SS est le numéro de la semaine et YY signifie l'année 20YY (voire YSS pour les années 2000 à 2009) :
Code:
=ENT(DATE(2000+$A$1/100;1;2)/7+MOD($A$1;100))*7-5
(on passe de 88 caractères à 49 caractères ; tu l'avais bien dit JCGL, on pouvait faire plus court !)
En pièce jointe des essais et une enigme : les formules 4 et 5 donnent des résultats faux et je ne comprends pas pourquoi (j'ai essayé d'utiliser la fameuse formule avec {} )
Re : Calculer un numero de semaine en fonction d'une date
Bonjour à tous,
Comme beaucoup, je suis tombé sur le problème de la fonction NoSemaine fournie par Excel "qui ne marche pas" et sur la recherche d'une fonction ou d'une formule Excel qui permette de calculer le numéro de semaine d'une date donnée. J'ai finalement trouvé mon bonheur sur le présent site, avec cependant des questions qui restaient en suspens. J'essaie ici de partager ces questions, les réponses que j'ai trouvées et les conclusions que j'en ai tirées, pour "tordre le coup" défintivement à cette question du calcul du numéro de semaine.
Les ressources trouvées ici :
- Pourquoi NoSemaine() "ne marche pas" : c'est fait pour les américains du Nord, or en France et en Europe, on utilise la définition ISO des n° de semaine. Régulièrement, selon l'année, les deux définitions diffèrent d'un écart de 1 tout au long de l'année et plus fréquemment encore pour les semaines de fin d'année.
- La définition ISO des n° de semaine
- J'ai retenu deux formules :
- celle rapportée par MJ13 et d'autres :
(longueur : 48 caractères, 2 appels de $A$1) (je l'ai vérifiée mais si quelqu'un voulait bien la vérifier aussi, ce serait bien et je mettrais ici contre-vérifié par XXXX, YYYY pour rassurer les potentiels utilisateurs !)
Les questions que je me suis posées avec d'autres :
- Quelle formule "est la bonne" ?
- Pourquoi ou comment ces formules fonctionnent-elles ?
- En terme de syntaxe Excel, je n'avais jamais vu une formule avec des matrices à l'intérieur et sans brackets {} tout autour comme dans la première des formules citées au-dessus : =ENT(($A$1+5-SOMME(MOD(DATE(ANNEE($A$1-MOD($A1-2;7)+3);1;2);{1E+99;7})*{1;-1}))/7)
Les conclusions sur la(les) formule(s): à utiliser selon les besoins - "la bonne formule":
- La formule de MJ13, à savoir =ENT(($A$1+5-SOMME(MOD(DATE(ANNEE($A$1-MOD($A1-2;7)+3);1;2);{1E+99;7})*{1;-1}))/7) , est conforme avec la définition ISO des numéros de semaine ; maintenant, cette formule "fait un peu peur" et elle est un peu lourde
- La formule de Monique, à savoir =ENT(MOD(ENT(($A$1-2)/7)+0,6;52+5/28))+1 , est plus compacte mais elle cesse d'être conforme à l'ISO à partir de 2105.
- "Ma" formule, à savoir =ENT(($A$1+5)/7-ENT(DATE(ANNEE(ENT(($A$1+5)/7)*7-2);1;2)/7)) est un bon compromis à mon sens : toujours exacte quelque soit l'année, elle est de dimension acceptable
- Personnellement, j'utilisais la formule simplifiée de Monique car mes applications ne regardaient pas au-delà de quelques années en avance et d'ici 2100 ... je ne serai plus là pour me plaindre. Depuis, j'utilise "ma" formule car elle est presque aussi compacte et toujours juste. Pour ceux qui ont besoin d'une formule qui va au-delà de 2100, utiliser la première formule de MJ13 ou celle que je propose.
A partir d'ici, c'est de la littérature pour ceux qui veulent savoir "pourquoi" : pourquoi la formule de MJ13 est conforme à la recommandation ISO ? comment elle marche ? l'explication de la drôle de syntaxe, comment marche la formule de Monique et son domaine d'utilisation.
L'étrange syntaxe
Etrange elle l'est parce que ce n'est pas une formule matricielle normale avec les brackets { et } à l'extérieur mais une formule normale avec des brackets en sons sein ; jamais vu çà ! Apparemment çà ne marche qu'avec des constantes : c'est donc un raccourci syntaxique. C'est l'équivalent compact de la formule suivante : =ENT(($A$1+5-DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;2)+MOD(DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;2);7))/7)
On a pas gagné en concision ... mais au moins on comprend le calcul opéré à défaut d'en voir encore le sens (çà va venir plus bas !). En fait la formule supoer astucieuse, s'appuie sur le fait que mod(x ; l'infini ou un nombre "trés grand" c'est-à-dire > à tous les x qu'on utilisera) = x !
=SOMME(MOD(x ; {1E+99 ; 7}) * {1 ; -1}) est équivalent à = x - mod(x ; 7) ; cela évite de répéter deux fois x quand x est long comme le bras (et la formule déjà assez longue comme çà).
Quelle formule toujours conforme à l'ISO ?
Devant le double constat de plusieurs formules incompatibles (car divergentes à certaines dates) et de formules que je ne comprenais pas, je me suis lancé dans la construction d'une "bonne formule" à partir de la définition de l'ISO. J'ai ainsi trouvé la troisième formule suivante : =ENT(($A$1-MOD($A$1-2;7)-DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;2)+5+MOD(DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;2);7))/7)
Cà ne vous rappelle rien ? Eh oui, c'est la formule de MJ13 une fois l'astuce syntaxique dévoilée ! C'est ainsi que j'en ai déduit que la formule de MJ13 est conforme à l'ISO quelque soit l'année (>1/1/1900 et pour un système de date Excel dont le jour 1 est justement le 1/1/1900). Je n'ai pas repris cette formule parmi les formules utilisables car elle est équivalent à celle de MJ13 en plus long. Elle n'a d'intérêt que pédagogique (ou pour ceux qui n'ont pas la possibilité de taper des brackets ou sont allergiques aux formules matricielles).
Comment démontre-t-on à la formule "pédagogique" ?
Le principe du calcul : soit une date donnée dont on cherche le numéro de semaine ; on prend le jeudi appartenant à la même semaine que la date (pourquoi ? cf. plus bas) ; ensuite on cherche l'année de ce jeudi ; puis on cherche le premier jeudi de cette année ; on calcule enfin le nombre de jour entre les deux jeudis qu'on divise par 7 pour trouver le nombre de semaines d'écart ; comme on sait que le premier jeudi de l'année est forcément dans la semaine 1 (source ISO), on connait le numéro de semaine recherché .
Voici la cascade des calculs opérés:
=$A$1 : date dont on cherche le numéro de semaine
=MOD($A$1-2;7) : jour dans la semaine avec 0 pour lundi et 6 pour dimanche (équivalent de JOURSEM($A$1;3) ou encore de JOURSEM($A$1;2)-1 )
=$A$1-MOD($A$1-2;7)+3 : date du jeudi de la semaine de $A$1 ; en effet =$A$1-MOD($A$1-2;7) donne toujours la date du début de semaine à savoir le lundi ; +3 pour avoir le jeudi pour simplifier les choses : comme tous les jours de la semaine ont par définition le même numéro de semaine, on a intérêt à choisir le jeudi ; en effet, la définition ISO implique que le premier jeudi de l'année appartient à la semaine n°1 (source ISO/Wikipedia) ; cela fournit la base à partir de laquelle on va pouvoir calculer le numéros de semaine au sein de l'année
=DATE(ANNEE($A$1-MOD($A1-2;7)+3);1;4)-MOD(DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;4)-2;7)+3 : on applique la formule juste au-dessus pour un jour de la première semaine : l'ISO dit que le 4 janvier est toujours dans la semaine n° 1 ; pour l'année, il s'agit de bien prendre l'année du jeudi calculé au-dessus ; on remplace donc dans la formule de l'alinéa au-dessus, $A$1 par DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;4)
=ENT( ( $A$1-MOD($A$1-2;7)+3 - (DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;4)-MOD(DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;4)-2;7)+3) ) /7 ) +1 : il suffit alors de faire la différence de jours entre les deux jeudi, de diviser par 7 pour obtenir un écart en semaines, + 1
=ENT(($A$1-MOD($A$1-2;7)-DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;2)+5+MOD(DATE(ANNEE($A$1-MOD($A$1-2;7)+3);1;2);7))/7) : après quelques simplifications (+3 et -3, Date(YYYY,MM, JJ)-2 = Date(YYYY,MM,JJ-2), l'entrée du +1 dans la formule sous la forme de +7 )
Ouf !
Comme on a montré plus haut l'équivalence de cette formule avec celle de MJ13, cela montre que la formule de MJ13 donne toujours le bon n° de semaine au sens de l'ISO. On pourra donc évaluer les autres formules à l'aune de celle-ci pour vérifier si elles sont justes ou sur quels domaines elles sont justes. Application à la formule de Monique.
La formule de Monique :
Elle est visiblement plus compacte. Mais en calculant la différence entre cette formule et la formule "étalon" (celle proposée par MJ13) on observe qu'elle est fausse à partir de 2105.
Comment expliquer cette formule. D'abord l'idée d'une fonction modulo pour représenter une fonction qui croit de 1 à 52 ou 53 puis repart à 1 et recroit jusqu'à 52 ou 53 fait inévitablement penser à une fonction modulo. Essayons de construire une formule à partir de cette intuition.
Le principe du calcul : soit une date dont on cherche le numéro de semaine ; cherchons la date du jeudi appartenant à la même semaine ; on ne garde qiue la partie fractionnaire d'année (on ôte les jours correspondant aux années pleines) ; on passe de jours à des semaines pour obtenir le nombre de semaines.
Voici la cascade des calculs opérés:
=$A$1 : date dont on cherche le numéro de semaine
=ENT(($A$1-2)/7)*7 +2 : lundi de la semaine la date donnée ; remarque : autre façon de faire équivalente à $A$1 - MOD($A$1-2;7) rencontré plus haut
=ENT(($A1-2)/7)*7 +2-1 : pour avoir un numéro de jour avec un nouveau référentiel tel que le 1/1/1900 est 0 (pour utiliser les modulos ; on a fait de même avec les jour de semaine en prenant 0 pour lundi, 1 pour mardi, .., 6 pôur dimanche)
=ENT(($A$1-2)/7)*7 +2-1+3 pour avoir le jeudi de la semaine dans notre nouveau référentiel
=MOD(ENT(($A$1-2)/7)*7 +4;365,25) pour avoir un numéro de jour débarassé des années pleines précédant (on reconnaitra dans 365,25 la longueur moyenne d'une année)
=ENT(MOD(ENT(($A$1-2)/7)*7 +4;365,25)/7)+1 : division par 7 pour avoir le numéro de semaine, +1 pour avoir un numéro de semaine commençant à 1
Remarque 1 : on peut procéder ainsi car les semaines ISO ont toutes 7 jours (ce n'est pas le cas de la version US qui a des semaines de longueur irrégulières, les semaines de début ou de fin de semaine)
Remarque 2 : le raisonnement qui consiste à prendre le modulo 365,25 pour ne garder que les jours dans l'année et calculer le n° de semaine ainsi est une approximation ; heureusement, on utilise le fait que d'une semaine à une autre le n° du jour prend 7 jours ; dès lors un modulo 365 ou 365,25 ne change pas trop. Avec un grand nombre d'années, on a une bonne approximation du nombre de jours avec nombre d'année * 365,25 : c'est l'idée même de moyenne. Autre façon de dire la même chose en raisonnant en semaines : le premier jeudi de 1900 qui donne son numéro à la semaine est 5 (numéro de série Excel) -1(pour que le 1/1/1900 ait 0 comme n°) /52 soit 0,076 soit 0 comme numéro après extraction de la partie entière ; la première semaine de 1901 c'est de même (5-1+7*52)/52 = 52,57 ; on a 365,25/7 = 52,178 semaines par année ; ENT(MOD(52,57;52,178)) +1 = 1, ce qui est bien le numéro de la première semaine de 1901.
Remarque 3 : en appliquant la division par 7 à l'intérieur de la formule on trouve =ENT(MOD(ENT(($A$1-2)/7) +4/7;52+5/28))+1 : c'est la formule de Monique à un détail près : on a 4/7 au lieu de 0,6 (soit 4,2/7) - en fait sur une large plage d'années (toutes ?), la valeur du coefficient 4 peut être changée pour n'importe quelle valeur entre 4 et 4,2 !
Remarque 4 : la formule de Monique est fausse à partir du 29/12/2104. En substituant d'autres coeffcient à 0,6, on couvre d'autres domaines de validité (cf. tableau pour couvrir les années d'ici l'an 3000, par palier de 100 ou 200 ans).
Coefficient Domaine validité Nb années
0,6 01/01/1900 22/12/2104 205
0,72 27/12/2100 22/12/2200 100
0,88 26/12/2196 22/12/2302 106
1,02 27/12/2297 22/12/2504 207
1,15 27/12/2500 22/12/2600 100
1,3 26/12/2596 22/12/2702 106
1,45 27/12/2697 22/12/2904 207
1,6 27/12/2900 22/12/3000 100
.
Vos remarques sont les bienvenues !
Re : Calculer un numero de semaine en fonction d'une date
j'ai utilisé cette formule afin de l'insérer dans un tableau de données. Je l'ai donc copié collé. Lorsque la date est vide, le numéro de semaine affiche 52.
Comment ne rien afficher si aucune date n'est rentrée ?
Re : Calculer un numero de semaine en fonction d'une date
Bonjour à tous
Merci à aldus_85 pour sa note complète sur la question du numéro d'une semaine dans l'année. Il est agréable de voir non seulement des formules, mais aussi leur justification.
J'ai essayé la formule proposée sur un assez grand nombre de dates : il me semble qu'elle est correcte.
Juste un détail : comme les années séculaires ne sont bissextiles qu'une fois sur quatre dans le calendrier grégorien (1600, 2000, 2400 sont bissextiles, mais pas 1700, 1800, 1900, 2100...), il y a 146097 jours, soit exactement 20871 semaines en quatre cents ans.
L'année grégorienne moyenne vaut 365,2425 jours.
Dans le calendrier julien, une année sur quatre est bissextile, sans exception. 400 ans comptent 146100 jours, ou 20871 semaines et trois jours.
L'année julienne moyenne vaut 365,25 jours.
En tout cas, bravo pour cette synthèse qui devrait rendre service à plus d'un esprit curieux.
Re : Calculer un numero de semaine en fonction d'une date
Bonjour à tous
Aldus:Alors la, tu m'as scotché. J'avais point vu d'explications aussi détaillées depuis des lustres. Bravo pour cet exercice de style. Mais je te rassure: je n'ai pas tout saisi.
Sinon, je pense que ma formule doit provenir de Laurent Longre, assez connu ici. J'ai trouvé ceci sur le net:
Ce lien n'existe plus
Sinon, je vois souvent notre ami Roger (que je salue) nous parler de calendrier Grégorien ou Julien. Pour le premier je pensais naïvement que c'était celui de Grégoire et pour le second celui de Julien Doré.
Mais bon j'ai trouvé ce lien qui me paraît intéressant pour mieux connaître les différents calendriers.
Juste un détail : comme les années séculaires ne sont bissextiles qu'une fois sur quatre dans le calendrier grégorien (1600, 2000, 2400 sont bissextiles, mais pas 1700, 1800, 1900, 2100...)[...]
L'année grégorienne moyenne vaut 365,2425 jours.
A vrai dire, je n'étais pas franchement ravi de la dernière remarque de mon petit laïus qui donnait des coefficients venus d'on ne sait où. On voyait bien qu'il fallait changer de coefficient grosso modo avec tout nouveau siècle. Et la présence d'une année moyenne à 365,25 me gênait. D'où 2 tentatives (ratées jusqu'à présent) :
- J'avais essayé brutalement avec 365,2425 et çà ne marchait plus du tout
- Je me suis dit "corrigeons après les années séculaires, et repartons sur de bonnes bases en remplaçant comme jour 0 le 1/1/1900 par le 1/1/2000 soit le premier jour du siècle précédant la date dont on cherche le n° de semaine" ou idem avec le 1/1 juste après l'année bisextile : sans succès.
De plus, si la formule de correction est un tant soit peu longue, on perd l'avantage de compacité de la "formule de Monique" d'où le retour à la case départ avec une formule compacte qui marche sur un domaine limité.
Re : Calculer un numero de semaine en fonction d'une date
Re...
Bonjour aldus_85
J'ai fait les mêmes tentatives que vous avec 365.2425 sans plus de succès pour arriver aux mêmes conclusions : il faut choisir entre une formule simple de portée limitée ou une formule compliquée de portée générale. A chacun d'arbitrer en fonction de l'usage qu'il en a. En tous cas, merci encore pour la qualité de votre synthèse qui permet de faire son choix en connaissance de cause.
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
