«

»

Semaine et jours ouvrés

Dans le traitement des dates, gérer le numéro de semaine ou des jours ouvrés, peuvent rapidement devenir complexes et être source d’erreurs.

Dans cette page vous trouverez la liste des fonctions à utiliser pour travailler sur les semaines

  • Numéro du jour de la semaine
  • Numéro de semaine (pas si simple à déterminer)
  • Ajout de jours sans tenir compte des week-ends (parfait pour les plannings)

Fonction JOURSEM

Principe

La fonction JOURSEM va renvoyer un nombre (entre 1 et 7) qui correspond au numéro du jour de la semaine pour une date donnée.

=JOURSEM(date;paramètres)

Erreur d’interprétation

La fonction a été développée aux Etats-Unis et, aux Etats_Unis le premier jour de la semaine est le dimanche.




C’est pourquoi, il très important pour les utilisateurs européens de compléter la formule avec le second paramètre de la fonction : cela évite un décalage dans votre résultat (erreur très fréquente).

Valeur possible pour le second paramètre de la fonction (toutes versions)

  • 1 : la semaine commence un dimanche (1) et se termine un samedi (7)
  • 2 : la semaine commence un lundi (1) et se termine un dimanche (7)
  • 3 : la semaine commence un lundi (0) et se termine un dimanche (6)

Avec Excel 2010 et les versions supérieures, de nouvelles valeurs pour ce paramètre ont été ajoutées mais elles ne sont jamais utilisées. Il est recommandé de rester sur les paramètres 1, 2 ou  3

  • 11 : la semaine commence un lundi (1) et se termine un dimanche (7)
  • 12 : la semaine commence un mardi (1) et se termine un lundi (7)
  • 13 : la semaine commence un mercredi (1) et se termine un mardi (7)

Utilisation

En soit, la formule est assez peu intéressante. Par contre, associée dans des tests avec la fonction SI ou les mises en forme conditionnelles, elle vous permettra de créer des calendriers ou des rapports de suivi de projets plus performants.

Par exemple, dans le cas d’un calendrier automatique, j’ai utilisé une mise en forme conditionnelle pour changer la couleur des week-ends. La formule pour la mise en forme conditionnelle est la suivante :
=JOURSEM(Votre Date;2)>5

Changez les valeurs du mois et des années pour modifier les couleurs des week-ends automatiquement.

Calcul avec des jours ouvrés

La fonction SERIE.JOUR.OUVRE est très utile pour tous ceux qui élaborent des emplois du temps sur plusieurs semaines.

En effet, lorsque vous avez une date de départ et que vous voulez ajouter un nombre de jours, il ne faut comptabiliser ni les samedis ni les dimanches. (comme c’est le cas dans cet exemple). Si vous faites une simple addition entre votre date de début et le nombre de jours à ajouter les week-ends seront comptabilisés et donc fausseront vos résultats.

fonction_semaine_1

La fonction SERIE.JOUR.OUVRE corrige ce problème et son utilisation est très simple.

Il vous suffit de passer en premier paramètre votre date de départ et en second le nombre de jours à ajouter. La fonction va vous retourner la date de fin sans comptabiliser les week-ends (image ci-dessous).

fonction_semaine_2

Mieux ! Vous pouvez également incorporer les jours fériés ou vacances exceptionnelles en passant ces informations dans un troisième paramètre (non obligatoire).

Par exemple, le lundi 16 Mai 2016 était un jour férié en France, il fallait donc ne pas le comptabiliser pour la Tâche 1. La formule est alors la suivante et cela change le calcul de la date de fin.

=SERIE.JOUR.OUVRE(B2;C2;”16/05/2016″)

fonction_semaine_3

Si vous avez une plage de données qui contient plusieurs jours fériés, vous pouvez indiquer les références de cette plage dans le troisième paramètre.

Numéro de semaine

Pour le calcul des numéros de semaine, la fonction NO.SEMAINE(date) retourne le numéro de la semaine à partir d’une date. Seulement le calcul des numéros de semaine n’est pas le même entre l’Amérique du Nord et l’Europe.

Règle : 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. Lorsqu’une année commence un vendredi, un samedi ou un dimanche, ceci n’est pas considéré comme une semaine. Pour l’Amérique du Nord, cette règle n’existe pas et le calcul des numéros de semaine commence dès le 1er Janvier, même si le 1er Janvier est un dimanche.

Quand la fonction a été développée par les développeurs de Microsoft, c’est la règle du calcul pour l’Amérique du Nord qui a été retenue. Mais face à la critique, la formule a évolué pour permettre de prendre en compte les 2 règles. Cette évolution a été introduite depuis Excel 2010 puis la fonction NO.SEMAINE.ISO est apparue dans la version d’Excel 2013.

Pour récupérer les formules, il vous suffit de double cliquer dans les cellules du classeur incorporé ci-dessous.

Tableau récapitulatif qui montre le numéro de semaine obtenu par la fonction NO.SEMAINE (colonne C) et par la fonction NO.SEMAINE.ISO (colonne D) pour la même date du 4 Janvier de différentes années.

fonction_semaine_4

Décalage dans la méthode de calcul du numéro de semaine selon la formule utilisée.

Version antérieure à Excel 2007

La fonction NO.SEMAINE retourne systématiquement la valeur d’Amérique du Nord. Pour retourner la valeur pour l’Europe (norme ISO 8601), il faut utiliser cette formule très complexe.

=ENT((AUJOURDHUI()-SOMME(MOD(DATE(ANNEE(AUJOURDHUI()-MOD(AUJOURDHUI()-2;7)+3);1;2);{1E+99;7})*{1;-1})+5)/7)

Excel 2007

Pour cette version, Microsoft a introduit un second paramètre complètement inutile car il ne corrige pas le problème du mode de calcul 🙁

Les valeurs possibles pour ce paramètre sont 1 (la semaine commence le dimanche) ou 2 (la semaine commence le lundi). Aucun rapport avec les normes de calcul ISO 8601. Pour calculer le numéro de semaine pour l’Europe, il faut encore utiliser la formule complexe précédente.

Excel 2010

Dans cette version, le second paramètre a été modifié pour prendre en compte les 2 modes de calcul. D’autres valeurs ont été ajoutées pour le second paramètre mais seule la valeur 21 permet de retourner le numéro de semaine selon la norme ISO 8601 (méthode européenne).

Excel 2013 et Excel 2016

Microsoft a finalement créé une nouvelle fonction dans Excel 2013 pour retourner le numéro de semaine tel qu’il est calculé en Europe, afin d’ harmoniser les différentes techniques de calcul des numéros de semaine.

=NO.SEMAINE.ISO(Date)




Lien Permanent pour cet article : https://www.excel-exercice.com/semaine-jours-ouvres/

(5 commentaires)

1 ping

Passer au formulaire de commentaire

  1. Alex

    Bonjour, je veux créer un tableau Excel 2010 avec pour chaque ligne une semaine. C’est à dire que dans ma ligne 1, j’aie une case qui écrit “28 mai au 2 juin” (étant une semaine du dimanche au samedi) et que les lignes suivantes se complètent de la même façon. Est-ce possible de le faire ? si oui, quelle serait la formule à utiliser ?

  2. juojuo

    je veux voir quand commence -t- il les controles de la 2 série 2016

  3. Anonyme

    très utile, merci !

  4. Anonyme

    merci pour la formule d’ Excel 2010,

  5. Anonyme

    Bravo pour la formule pour les versions 2007 et antérieures

  1. Excel sur votre portable gratuitement - Excel Exercice

    […] Fonctions sur les semaines […]

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger