Vous êtes-vous déjà demandé comment obtenir les dates de début et de fin de semaine à partir d’un numéro de semaine dans Google Sheets ? Eh bien, c’est là que ma formule de semaine de calendrier entre en jeu !
Qu’est-ce que la formule de semaine de calendrier dans Google Sheets ?
La fonction Weeknum (voir mon guide sur les fonctions) est la seule fonction qui permet d’identifier les numéros de semaine. Mais elle ne renvoie pas la plage de semaines.
Ce que je veux, c’est obtenir la date de début de semaine et la date de fin de semaine, par exemple du 4 février 2019 au 10 février 2019 (du lundi au dimanche) au lieu du numéro de semaine 6.
C’est là que ma formule de semaine de calendrier dans Google Sheets prend toute son importance.
Remarque : J’ai utilisé le calcul du numéro de semaine de type 2, où le jour de la semaine commence le lundi et se termine le dimanche.
Si j’avais utilisé le type 1, la plage de la semaine de calendrier aurait été du 3 février 2019 au 9 février 2019 (du dimanche au samedi).
Je vais vous donner plus d’informations à ce sujet dans la partie de la formule concernée ci-dessous. Ici, je vais utiliser la fonction Weekday, et non Weeknum.
Ma formule de semaine de calendrier dans Google Sheets renverra les dates de début et de fin de semaine sous forme combinée à partir d’une liste de dates. De plus, la formule peut être utilisée dans une plage de dates qui s’étend sur différentes années.
Voici d’abord le résultat renvoyé par ma formule de semaine de calendrier, puis je vous dirai où nous pouvons l’utiliser.
Vous pouvez facilement trouver la date de début et de fin de semaine de n’importe quelle date en utilisant une formule simple. J’ai déjà expliqué cela ici – Formule pour trouver la date de début et de fin de semaine dans Google Sheets.
Je vais l’utiliser sous forme de tableau pour obtenir notre résultat. En fait, j’ai deux types de formules différentes à vous proposer. Je vais vous expliquer et vous fournir les deux options. Vous pouvez choisir celle qui vous convient le mieux.
Le but de la formule de semaine de calendrier dans Google Sheets
Le but d’utiliser ma formule de semaine de calendrier est simple : il s’agit de combiner la date de début d’une semaine avec la date de fin. Mais le véritable objectif est différent. Grâce à cette formule, nous pouvons créer des rapports de synthèse hebdomadaires tels que :
- La moyenne hebdomadaire des ventes pendant la période du 01/01/2018 au 16/02/2019 (données quotidiennes pour moyenne hebdomadaire).
- La somme hebdomadaire des ventes qui s’étendent sur plusieurs années (données quotidiennes pour somme hebdomadaire).
- Le nombre hebdomadaire de réceptions de marchandises (nombre de camions) pour une période donnée (données quotidiennes pour nombre hebdomadaire).
Si vous êtes satisfait d’obtenir un résumé basé sur les numéros de semaine, les choses seront faciles pour vous.
Nous pouvons utiliser la fonction Query pour cela, et vous pouvez voir un exemple dans mon précédent tutoriel ici – Comment créer un rapport de synthèse hebdomadaire dans Google Sheets.
Vous pouvez résumer une plage de dates en fonction de la semaine de calendrier, non pas du numéro de semaine, mais des dates de début et de fin de semaine en tant qu’en-tête de colonne, en utilisant ma formule de semaine de calendrier dans Google Sheets.
Dans ce tutoriel, je n’aborderai pas la partie de la synthèse. Ici, vous pouvez apprendre comment obtenir les dates de début et de fin de semaine en tant qu’en-tête de colonne.
Le résumé par semaine de calendrier, que vous pourrez apprendre dans l’un de mes tutoriels à venir, qui est déjà en préparation. Je vous fournirai certainement le lien. Je veux dire que je mettrai à jour cet article avec le lien inclus.
Combinez les dates de début et de fin de semaine à l’aide de la formule de semaine de calendrier
Données d’exemple :
J’ai quelques dates aléatoires dans la colonne A, par exemple :
Les lignes A2:A29 contiennent des dates du 01/02/2019 au 28/02/2019 et les lignes A30:A58 contiennent des dates du 01/02/2020 au 29/02/2020.
Je veux vous montrer la compatibilité sur plusieurs années de ma formule. C’est pourquoi j’ai inclus des dates qui s’étendent sur deux années.
De plus, même si les dates ne sont pas triées, cela n’affectera pas mes formules. Donc, vous êtes en sécurité avec des dates dans n’importe quel ordre de tri – ordre croissant, décroissant ou aléatoire.
Je propose deux types de formules. Commençons par la formule 1. Il s’agit d’une formule de combinaison, je vais donc vous guider pas à pas.
Étape 1 :
Comment retourner les dates de début de semaine d’une plage de dates quotidiennes données.
Formule #1 (appliquez temporairement dans la cellule B2)
=ArrayFormula(A2:A58-WEEKDAY(A2:A58,2)+1)
Cette formule placera les dates de début de semaine dans la colonne B2:B à partir des dates de toutes les lignes de A2:A.
Enveloppez la formule ci-dessus avec la fonction Unique pour supprimer les dates de début de semaine en double.
Formule #2 (modifiez la formule dans la cellule B2)
=unique(ArrayFormula(A2:A58-WEEKDAY(A2:A58,2)+1))
Maintenant, nous devons trier la sortie de cette formule. Enveloppez simplement la formule ci-dessus avec la fonction SORT. Vous pouvez supprimer la fonction ArrayFormula lors de l’utilisation de SORT.
Formule #3 (modifiée dans la cellule B2)
=sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1))
Voici le résultat.
Remarque : Consultez la colonne B2:B qui montre les dates de début de semaine commençant le lundi. Pour commencer à partir du dimanche, modifiez simplement le type de jour de la semaine 2 dans la formule ci-dessus en 1.
Pour en savoir plus sur les types de jours de la semaine, consultez mon guide complet des fonctions de date pour Weekday.
Étape 2 :
Comment retourner les dates de fin de semaine d’une plage de dates quotidiennes données.
Ici, je ne vais pas vous guider à travers toutes les étapes ci-dessus. La formule est la même, à l’exception du +6.
Formule (appliquez temporairement dans la cellule C2)
=sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1+6))
Cette formule ajoute simplement 6 jours aux dates de début de semaine pour obtenir les dates de fin de semaine. Une logique simple, n’est-ce pas ?
Voyez les dates de fin de semaine, ce sont les dimanches. Pour les changer en samedi, comme je l’ai mentionné ci-dessus, changez le type 2 en type 1 dans Weekday.
Les sorties ci-dessus peuvent être utilisées dans des formules telles que Somme.si, Nb.si, Moyenne.si pour faire la somme, le compte et la moyenne en fonction de la plage de dates dans Google Sheets.
Étape 3 :
Combinez les dates de début et de fin de semaine pour créer des colonnes pour la semaine de calendrier.
Vous pouvez combiner les dates de début et de fin de semaine et les transposer en colonnes comme indiqué dans la capture d’écran n°1 au début. Vous trouverez les données dans la plage C1:L1.
Voici les instructions étape par étape. Essayez simplement de comprendre les formules ci-dessous.
=ArrayFormula(B2:B11&" - "& C2:C11)
Cette formule peut combiner les colonnes contenant les dates de début de semaine (B2:B) et les dates de fin de semaine (C2:C), puis insérer le tiret entre elles. Mais je ne vous recommande pas d’utiliser cette formule pour les raisons suivantes :
- Elle fera perdre le format de date.
- Elle utilise les colonnes auxiliaires B et C.
J’ai déjà publié un tutoriel sur Google Sheets qui explique très bien comment concaténer une date avec du texte, ou une date avec une autre date en conservant le format de date.
Je vais utiliser cette méthode ici. Ensuite, je vous montrerai comment supprimer les colonnes auxiliaires. Notez l’utilisation de la fonction Text.
=ArrayFormula(text(B2:B11,"JJ/MMM/AA")&" - "&text(C2:C11,"JJ/MMM/AA"))
Remplacez maintenant B2:B11 par la formule dans B2 et C2:C11 par la formule dans C2. Cette formule sera la suivante :
=ArrayFormula(text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1)),"JJ/MMM/AA")&" - "&text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1+6)),"JJ/MMM/AA"))
Si j’avais donné cette formule dès le début, certains d’entre vous auraient certainement arrêté de lire en pensant que la formule est assez compliquée, n’est-ce pas ?
Enfin, enveloppez la formule avec la fonction Transpose pour la déplacer vers les colonnes. Cela sera la formule de la semaine de calendrier dans Google Sheets.
=transpose(ARRAYFORMULA(text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1)),"JJ/MMM/AA")&" - "&text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1+6)),"JJ/MMM/AA")))
Veuillez vous référer à la première capture d’écran pour le résultat de la formule.
Option 2
Je vous ai promis qu’il y a une autre formule avec moi. Vous pouvez remplacer la formule que j’ai utilisée dans la cellule B2 (étape 1) par :
=sortn(A2:A58-WEEKDAY(A2:A58)+1,9^9,2,1,true)
et la formule C2 (étape 2) par :
=sortn(A2:A58-WEEKDAY(A2:A58)+1+6,9^9,2,1,true)
Vous pouvez combiner cette formule selon les instructions données à l’étape 3. La fonction SORTN dans les deux formules ci-dessus remplace les fonctions Unique et Sort. Cela n’est donné qu’à titre d’information.
Conclusion
Vous pouvez vous attendre bientôt à un tutoriel dans lequel j’utiliserai la formule de semaine de calendrier mentionnée ci-dessus dans Google Sheets. Il s’agit d’un résumé hebdomadaire basé sur les semaines de calendrier.
Mise à jour : Résumé des données par dates de début et de fin de semaine dans Google Sheets.
Merci de votre attention. Amusez-vous bien !
