Trouver la bonne fonction
Dans Microsoft Excel, la fonction SEMAINE CALENDRIER est disponible avec laquelle vous pouvez afficher la semaine calendaire pour une date spécifique. Par exemple, vous pouvez afficher automatiquement dans quelle semaine un rendez-vous a lieu. La fonction de table CALENDAR WEEK dans Excel a aussi ses pièges. En effet, une semaine calendaire est définie différemment en Allemagne, en Autriche et en Suisse que dans les pays européens voisins ou aux États-Unis. Cet article montre comment vous pouvez toujours calculer la semaine calendaire correcte et les différences entre la semaine calendaire dans la région DACH et dans d'autres pays.
La fonction SEMAINE CALENDRIER dans Excel
La fonction SEMAINE CALENDRIER calcule également les semaines calendaires dans les versions européennes d'Excel en utilisant la norme américaine. Le premier jour de l'année est généralement affecté à la semaine civile 1.
La syntaxe pour calculer la semaine calendaire est :
= SEMAINE CALENDRIER (DATE)
Une semaine calendaire est définie différemment en Allemagne, en Autriche et en Suisse. Il s'agit essentiellement de la semaine qui, en tant que première semaine de l'année, comprend au moins quatre jours. Si le premier janvier tombe un vendredi, un samedi ou un dimanche, la fonction de table CALENDAR WEEK renvoie un résultat incorrect pour l'Europe.
Déterminez correctement les semaines calendaires dans Excel - voici comment cela fonctionne
Afin d'afficher la semaine calendaire correcte pour l'Allemagne, l'Autriche ou la Suisse, vous avez besoin d'une formule. Par exemple, cela doit être comme suit pour la cellule C4 :
= TRIM ((A4-DATE (ANNÉE (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)
La formule est complexe et extensive. Afin d'expliquer son fonctionnement, nous considérons séparément les composants individuels de la formule :
- La fonction REST (A4-2; 7) apparaît plusieurs fois dans la formule. Cette fonction divise la date par 7 et renvoie le reste résultant. Cette fonction renvoie la valeur 0 pour les lundis, la valeur 1 pour les mardis jusqu'à une valeur de 6 pour les dimanches.
- Dans la formule, le nombre 2 est soustrait de la valeur de la date. La raison en est que la date de base 1/1/1900 était un dimanche. Divisé par 7 donne un reste de 1. Pour cette raison, lundi serait résolu au numéro 2. Pour corriger le nombre de jours à 0 pour le dimanche, 1 pour le lundi, etc., le nombre 2 est soustrait de la date.
- Sur la base de ce calcul du jour de la semaine, la partie (ANNÉE (A4-REST (A4-2; 7) +3) fournit une valeur annuelle réduite de 1 pour les trois premiers jours de l'année, à condition que ces jours soient les vendredis, samedis ou dimanches actes.
- Cette valeur annuelle est incluse dans une date dans la fonction DATE. Cette date est généralement formée avec le deuxième argument 1 pour le mois de janvier.
- Comme valeur journalière, transférez la fonction REST décrite ci-dessus à la fonction DATE moins la valeur 9. Cela se traduit généralement par une valeur journalière négative. La formule montre que la date calculée par DATE est généralement en décembre.
- Pour les trois premiers jours de l'année, cependant, c'est décembre de l'année précédant l'avant-dernier, à condition que ces trois jours soient des vendredis, samedis ou dimanches.
- Vous soustrayez cette date calculée par la fonction DATE de la date de la colonne A, divisez la différence par 7 (pour les 7 jours d'une semaine) Vous utilisez tout ce calcul au sein d'une fonction SHORT afin d'obtenir uniquement des valeurs entières.
Alternativement : Utilisez l'argument de fonction WOCHENSTART
Une autre possibilité de travailler avec la fonction SEMAINE CALENDRIER dans Microsoft Excel et d'obtenir des résultats corrects consiste à ajouter l'argument de fonction début de semaine à la formule. Semblable à la formule ci-dessus, il faut des efforts pour construire la formule.
L'argument de fonction WOCHENSTART permet de différencier les deux systèmes différents de calcul de la semaine calendaire :
- Système 1 (Système américain) - signale la semaine 1 comme la semaine qui inclut le premier janvier.
- Système 2 (norme européenne selon ISO 8601) - KW 1 est la semaine qui comprend le premier jeudi de l'année.
Si vous utilisez l'argument de fonction WOCHENSTART et la fonction de semaine calendaire, vous devez toujours saisir les nombres suivants comme arguments :
|
Lundi |
2 ou 11 |
|
Mardi |
12 |
|
Mercredi |
13 |
|
jeudi |
14 |
|
vendredi |
15 |
|
samedi |
16 |
|
dimanche |
1 ou 17 |
Liez la fonction CALENDAR WEEK avec l'argument de fonction WOCHENSTART en 6 étapes
Pour lier la fonction calendrier semaine avec l'argument de fonction début semaine, procédez comme suit :
-
Entrez une date dans votre feuille de calcul Excel.
-
Faites un clic gauche dans la cellule dans laquelle vous souhaitez incorporer la semaine calendaire.
-
Passez à l'onglet FORMULES et cliquez avec le bouton gauche de la souris sur "Insérer une fonction".
-
Saisissez SEMAINE CALENDRIER dans le champ de recherche.
-
Après avoir trouvé la fonction SEMAINE CALENDRIER, confirmez votre sélection avec OK.
-
Dès que vous êtes dans le champ de saisie de la fonction, saisissez les composants de la formule comme suit :
- Serial_number = cellule contenant la date, par exemple A1.
- Type de nombre = valeur numérique pour le jour de la semaine selon le tableau ci-dessus.
La semaine calendaire est correctement éditée conformément à la norme ISO 8601.
Comment calculer le début et la fin d'une semaine calendaire dans Excel
Les semaines calendaires jouent un rôle crucial dans la planification professionnelle. Les rendez-vous sont souvent planifiés ou convenus pour une semaine calendaire spécifique. Dans ce cas, vous devez
- Sachez également à quelle semaine du calendrier tombe une certaine date
- Découvrez à quelle date commence ou se termine une semaine calendaire spécifique.
Ces informations vous aident, par exemple, lors de la planification des dates de production dans un environnement industriel ou lors de l'organisation des dates de livraison ou de facturation.
À l'aide d'une formule spéciale, vous pouvez facilement déterminer le début et la fin d'une semaine calendaire. La formule a la structure suivante :
= TEXTE (DATE (numéro de l'année; 1 ; 7 * calweek-3-
JOUR DE LA SEMAINE (DATE (numéro de l'année;; ); 3) ); "JJ.MM.AA")
& "-" & TEXTE (DATE (numéro de l'année; 1 ; 7 * semaine calendaire + 3-
JOUR DE LA SEMAINE (DATE (numéro de l'année;; ); 3) ); "JJ.MM.AA")
Vous passez deux arguments à la formule : Avec l'argument Numéro de l'année définir l'année dans laquelle se situe la semaine calendaire. Vous passez le numéro de la semaine calendaire avec l'argument Kalweek.
Le résultat de la formule est la date du lundi de cette semaine calendaire, suivie d'un tiret et de la date du dimanche de la semaine calendaire.
Dans le classeur affiché, la formule suivante dans la cellule D4 donne comme résultat la période « 30/09/13 - 06/10/13 » :
= TEXTE (DATE (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4;;); 3)); "JJ.MM.AA") & "-" & TEXTE
(DATE (C4; 1; 7 * B4 + 3-JOUR DE LA SEMAINE (DATE (C4 ;;); 3));
"JJ.MM.AA")
Cette période correspond à la semaine civile 40 à partir de la cellule B4 en 2013 à partir de la cellule C4.
Le résultat de la formule génère du texte que vous ne pouvez pas utiliser pour continuer le calcul. Afin de clarifier la période de temps qui est éditée, vous pouvez afficher le résultat de manière à ce que les jours de la semaine soient également édités. Pour ce faire, modifiez le format des nombres dans les deux fonctions TEXTE de la formule comme suit :
= TEXTE (DATE (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4;;); 3)); "JJJ JJ.MM.
AA ") &" - "& TEXTE (DATE (C4; 1; 7 * B4 + 3-
JOUR DE LA SEMAINE (DATE (C4 ;;); 3)); "JJJJ.MM.AA")
Après avoir copié la formule dans toutes les lignes du tableau, les dates sont toujours sorties avec les jours de la semaine.
Comment calculer les semaines dans Excel et trouver la bonne date
Dans de nombreux plans, les semaines jouent un rôle en tant qu'unité, par exemple les semaines calendaires ou les semaines de production. Les semaines peuvent également être calculées et affichées dans Excel à l'aide de formulaires faciles à comprendre.
La figure suivante montre une liste de valeurs de date dans la colonne A. Dans la colonne B, il y a une valeur qui indique de combien de semaines cette valeur de date doit être reportée :
Dans la colonne C, vous souhaitez utiliser une formule qui calcule la nouvelle date en fonction de la date de la colonne A et du nombre de semaines de la colonne B. Si le nombre de semaines est une valeur supérieure à zéro, la date est décalée vers le futur, si la valeur est négative, elle est décalée vers le passé.
Vous pouvez atteindre cet objectif en multipliant le nombre de semaines par la valeur 7 et en l'ajoutant à la date d'origine. Avec la structure du tableau décrite ci-dessus, la formule de la cellule C2 ressemble à ceci :
= A1 + 7 * B1
La formule est applicable car Microsoft Excel stocke les valeurs de date en interne sous forme de nombres. La distance d'un jour à l'autre est de 1. La figure suivante montre l'utilisation de la formule dans un exemple de tableau :
Conclusion : les semaines calendaires sont cruciales pour la gestion des rendez-vous professionnels
Les rendez-vous déterminent notre vie aussi bien dans la vie professionnelle que dans la vie privée. Lors de la planification des rendez-vous, les semaines calendaires deviennent plus importantes avec l'intensité croissante des rendez-vous, car elles peuvent être utilisées pour créer plus de clarté dans la planification des rendez-vous.
Microsoft Excel est capable de spécifier la semaine calendaire correcte pour chaque date. Le facteur décisif ici est que les utilisateurs en Europe respectent le système de date européen selon ISO 8601 lors de la construction de leurs formules Excel. Par défaut, la semaine calendaire est calculée selon le système américain, ce qui entraîne des écarts. Aux États-Unis, le premier jour de l'année est généralement attribué à la semaine civile 1. En Allemagne, en Autriche et en Suisse, en revanche, la première semaine calendaire est généralement définie comme la semaine qui, en tant que première semaine de l'année, comprend au moins quatre jours. En pratique, cela signifie que la semaine 1 dans le système européen est la semaine qui comprend le premier jeudi de l'année.
Pour le calcul correct de la semaine calendaire, vous pouvez utiliser à la fois la fonction CALENDAR WEEK et l'argument de la fonction WEEKSTART dans Excel. Il est également possible de paramétrer des formules individuelles et de les adapter spécifiquement à votre tableur.