Comment savoir si une date tombe un week-end sur Excel ?

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, je vais vous montrer une série de formules qui va nous permettre de déterminer rapidement si une date donnée tombe pendant un week-end ou en cours de semaine. Nous verrons également comment extraire directement des dates d’une base de données afin de compter le nombre de dates tombant un week-end, tout en appliquant des critères de sélection.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Introduction

Comme nous venons de le voir à l’instant, nous allons maintenant voir comment déterminer si une date donnée tombe un week-end.

Et pour illustrer ce tutoriel, nous allons utiliser la base de données suivantes :

Excel formation - est ce un week end - 01

Dans celle-ci, nous retrouvons donc une liste des déplacements effectués par des commerciaux d’une entreprise.

L’objectif sera alors de déterminer si ces déplacements ont eu lieu en semaine ou durant le week-end, ce qui permettra par exemple de calculer des primes à leur verser.

 

2. Comment savoir si une date tombe un week-end

Bien entendu, pour déterminer si une date donnée tombe en semaine ou week-end, nous allons tout d’abord devoir déterminer le jour correspondant dans la semaine.

Pour cela, nous allons pouvoir utiliser une fonction que nous avons déjà utilisée à plusieurs reprises au cours des tutoriels précédents, il s’agit de la fonction JOURSEM().

 =JOURSEM(numéro_de_série;type_retour) 

Les deux arguments attendus sont les suivants :

  • Numéro_de_série : il s’agit de la date pour laquelle nous souhaitons obtenir le numéro du jour au sein de la semaine
  • Type_retour : permet de spécifier le type de semaine à utiliser en fonction des jours de début et de fin :

Excel formation - est ce un week end - 02

Pour une semaine classique en France, nous souhaiterons que le premier jour soit un lundi et le dernier un dimanche, nous saisirons alors la valeur « 2 » à cet argument type_retour.

Dans notre exemple, nous saisirons donc la formule suivante dans la cellule C9 :

 =JOURSEM([@[Date déplacement]];2) 

Excel formation - est ce un week end - 03

Cela nous permet donc d’obtenir la position de chaque journée au sein de la semaine, en sachant que les jours de week-end, donc le samedi et le dimanche arrivent respectivement en 6e et 7e position.

Pour déterminer s’il s’agit d’un jour de week-end, il suffira donc de modifier la formule comme ceci :

 =SI(JOURSEM([@[Date déplacement]];2)>=6;"Week-end";"Semaine")  

Excel formation - est ce un week end - 04

 

3. Comment compter le nombre jours tombant un week-end

Maintenant que nous avons déterminé si une date tombe durant la semaine ou le week-end, nous allons pouvoir dénombrer très rapidement le nombre de déplacements effectués uniquement sur les samedis et dimanches.

Pour cela, il nous suffira en effet d’utiliser la fonction NB.SI() qui permet de dénombrer des cellules en fonction d’un critère donné :

 =NB.SI(plage;critère) 

Ici, l’argument plage correspond aux cellules dans lesquelles nous venons d’insérer la formule de détermination du type de déplacement (la colonne « Est week-end ? ») et l’argument critère correspond au critère permettant d’effectuer le dénombrement :

 =NB.SI(baseDeplacements[Est week-end ?];"Week-end")  

Excel formation - est ce un week end - 05

Ici, l’argument critère étant un texte, nous le saisissons entre guillemets.

Il y a donc 38 déplacements effectués par tous les commerciaux pendant les week-ends.

Maintenant, imaginons que nous souhaitions réaliser le même calcul, mais sans utiliser de colonne de traitement intermédiaire.

Dans ce cas-là, nous ne pourrons utiliser la fonction NB.SI(), étant donné que celle-ci ne permet d’effectuer de traitement sur l’argument plage.

À la place, nous utiliserons plutôt la fonction SOMMEPROD(), que nous détournerons de son usage classique pour effectuer des calculs sur la base de données, comme nous l’avons vu lors d’un tutoriel dédié à cette fonction très puissante.

Pour effectuer ce calcul, nous commençons par appeler la fonction SOMMEPROD() :

Excel formation - est ce un week end - 06

Ensuite, nous y saisissons notre critère de dénombrement entre parenthèses, en reprenant la formule vue précédemment :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)>=6)) 

Si nous validons à ce moment-là, nous obtiendrons comme résultat 0 :

Excel formation - est ce un week end - 07

En effet, cette formule ne permet pour le moment que d’obtenir une matrice constituée de VRAI et de FAUX, comme nous le montre l’outil d’évaluation des formules :

Excel formation - est ce un week end - 08

Il suffit ensuite de multiplier cette matrice par « 1 » pour remplacer les VRAI par des 1 et les FAUX par des zéros, de manière à ce qu’Excel en calcul la somme et donc détermine le nombre VRAI :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)>=6)*1) 

Ce qui nous permet bien entendu d’obtenir exactement le même résultat, à savoir 38 déplacements effectués durant des jours de week-end :

Excel formation - est ce un week end - 09

 

4. Comment compter le nombre de déplacement en week-end par commercial ?

Maintenant que nous savons comment calculer le nombre de week-ends total, nous pouvons développer un tout petit peu la formule pour obtenir l’information par commercial.

Pour cela, nous allons reprendre la dernière formule :

Excel formation - est ce un week end - 10

Puis, nous allons y introduire un second critère qui va correspondre au nom du commercial.

Pour cumuler les critères de sélection dans la fonction SOMMEPROD(), il faut multiplier les matrices de critères entre elles, en les saisissant entre parenthèses :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)>=6)*(baseDeplacements[Commercial]=[@Commercial])*1) 

Excel formation - est ce un week end - 11

Enfin, pour déterminer le nombre de déplacements effectués en semaine par commercial, il suffit d’inverser l’inégalité afin de ne garder dans le calcul que les jours de la semaine inférieurs ou égaux à 5 :

 =SOMMEPROD((JOURSEM(baseDeplacements[Date  déplacement];2)<=5)*(baseDeplacements[Commercial]=[@Commercial])*1) 

Excel formation - est ce un week end - 12

 

 



Articles qui pourraient vous intéresser

Comment créer un décompte entre deux dates (jours, heures, minutes et secondes) sur Excel ?
Comment savoir si une date tombe un week-end sur Excel ?
Comment compter le nombre de lettres ou de chiffres dans une cellule Excel ?
N’imbriquer pas les fonctions SI() sur Excel !
Comment calculer une moyenne avec un ou plusieurs critères sur Excel ?
Comment convertir des données en nombre ou en texte sur Excel ? Les fonctions N() et T()
Comment créer une référence automatiquement sur Excel ? La fonction DECALER()
Comment extraire les plus petites et plus grandes valeurs avec Excel ? Les fonctions PETITE.VALEUR() et GRANDE.VALEUR()
Comment mettre en place une référence dynamique ? La fonction INDIRECT() d’Excel
Comment calculer le PGCD et le PPCM (« Plus Grand Commun Diviseur » et « Plus Petit Commun Multiple ») sur Excel ?
Comment utiliser la fonction SOMMEPROD d’Excel
Comment nettoyer une chaîne de caractères - Supprimer les espaces indésirables et les caractères illisibles sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2022 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.