Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 Excel - férié, décalé les entretiens au jour ouvré avant le jour férié.

K

kellypombal

XLDnaute Nouveau
Bonjour à toutes et tous,

Je m'appelle Kelly et je suis nouvelle sur ce forum
Heureuse d'être un nouveau membre

Voici ma problématique.
La loi m'indique que les entretiens professionnels sont à faire tous les 2 ans à la date d’arrivée d'un salarié.
Je travaille dans une entreprise où les salariés travaillent les samedis, dimanches et jours fériés mais pas les responsables (ceux qui font passer les entretiens).
Donc je dois trouver une formule pour indiqué que si l'entretien tombe un samedi, dimanche ou un jour férié alors l'entretien doit être fait le vendredi et pas le lundi.
J'essaye désespérément de trouver une formule mais je n'y arrive pas.
J'ai une formule mais elle ne fonctionne que pour les samedis et pas pour les dimanches ou les jours fériés qui tomberaient les dimanches...

Quelqu'un a t-il déjà été confronté à cela ? Avez-vous une solution ?
Je dois mettre ma formule sur la colonne H, I, J et K.
Mes essais de formules sont sur l'onglet "jours fériés".
 

Pièces jointes

  • SUIVI ETABLISSEMENT - Suivi anonymisé.xlsx
    220.5 KB · Affichages: 18
Solution
Bon j'ai compliqué inutilement les formules des fichiers (3) et (4), prenez ce fichier (5) :
Code: 
=SERIE.JOUR.OUVRE(A2+731+(JOUR(A2+730)<>JOUR(A2));-1;J$3:J$184)
C'est donc ce post qu'il faut marquer comme solution.
Trier par date Trier par votes

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour kellypombal, et bienvenu sur XLD,
J'ai utilisé une autre méthode que la votre.
Si la date prévue est dans la liste des jours fériés, alors je fait J-1.
Si J-1 est un dimanche alors je fais J-3, ce qui donne vendredi.
Avec :
VB: 
=SI(ESTERR(EQUIV(A2;K:K;0));B2;SI(JOURSEM(B2-1;2)=7;B2-3;B2-1))
Ca marche au moins pour tous vos exemples.
 

Pièces jointes

  • SUIVI ETABLISSEMENT - Suivi anonymisé.xlsx
    227.1 KB · Affichages: 11
Vote positif 0 Vote négatif

Efgé

XLDnaute Barbatruc
Bonjour

Une autre à mettre en H9 et à tirer jusqu'en J9. (La colonne K reprend la colonne J)
VB: 
=SERIE.JOUR.OUVRE(DATE(ANNEE($F9)+(2*COLONNES($A:A));MOIS($F9);JOUR($F9)-1);1;'jours fériés'!$K$3:$K$184)

Cordialement
 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Bonjour kellypombal, sylvanu, Efgé,

Décalage de 2 ans en B2 :
Code: 
=A2+730+(JOUR(A2+730)<>JOUR(A2))
Date EP en C2, formule matricielle :
Code: 
=B2-4+EQUIV(B2;SI((JOURSEM(LIGNE(INDIRECT(B2-3&":"&B2));2)<6)*NON(NB.SI(K:K;LIGNE(INDIRECT(B2-3&":"&B2))));LIGNE(INDIRECT(B2-3&":"&B2))))
A+
 

Pièces jointes

  • SUIVI ETABLISSEMENT(1).xlsx
    220.5 KB · Affichages: 3
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Plus simple avec MAX, toujours en matriciel :
Code: 
=MAX(SI((JOURSEM(LIGNE(INDIRECT(B2-3&":"&B2));2)<6)*NON(NB.SI(K:K;LIGNE(INDIRECT(B2-3&":"&B2))));LIGNE(INDIRECT(B2-3&":"&B2))))
 

Pièces jointes

  • SUIVI ETABLISSEMENT(2).xlsx
    220.4 KB · Affichages: 0
Vote positif 0 Vote négatif
K

kellypombal

XLDnaute Nouveau
Merci à tous

Bonjour,

Merci mais cette formule ne fonctionne pas car elle décale les dates de quelques jours à chaque fois.
Si je mets que le salarié est entré le 01/05/2016 en F9, il me propose en :
H9 : 1er EP le 02/05/2018 (au lieu du 30/04/2018)
I9 : le 2ème EP le 04/05/2020 (au lieu du 30/04/2020)
J9 : le 3ème EP le 02/05/2022 (au lieu du 29/04/2020, le 30/04/2020 étant un samedi)

Bonne journée
 
Vote positif 0 Vote négatif
K

kellypombal

XLDnaute Nouveau
Bonjour job75,

La formule ne fonctionne pas sur la 1ère cellule (C2), cela indique 2019 au lieu de 2020 alors que les autres sont bonnes. Je n'arrive pas à déterminer pourquoi. sinon ça fonctionne très bien.

Le seul problème c'est que cela nécessite 2 case (une pour calculer la date N+2 ans en B et l'autre pour enlever les samedis, dimanches et jours fériés en C). Or, mon tableau doit être le plus synthétique possible car je vais l'envoyer à des personnes qui ne maitrise pas du tout Excel et qui vont se perdre si je mets 6 colonnes de dates.

Mais merci beaucoup
 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
kellypombal à dit:
La formule ne fonctionne pas sur la 1ère cellule (C2), cela indique 2019 au lieu de 2020 alors que les autres sont bonnes.
Cliquez pour agrandir...
Allons réveillez-vous, le jour qui précède le 01/01/2020 c'est le 31/12/2019 !!

Une dernière formule plus simple avec SERIE.JOUR.OUVRE dans ce fichier (3) :
Code: 
=MAX(SERIE.JOUR.OUVRE(B2+1;-LIGNE(INDIRECT("1:4"));K$3:K$184))
Elle ne nécessite pas de validation matricielle.
 

Pièces jointes

  • SUIVI ETABLISSEMENT(3).xlsx
    220 KB · Affichages: 7
Vote positif 0 Vote négatif
K

kellypombal

XLDnaute Nouveau
Oui, effectivement, je suis réveillée, désolée

Cette formule ne fonctionne pas avec certaines dates le 26/12/2019 par exemple (A4) ou la formule propose un entretien le 27/12/2021 au lieu de le proposer le 24/12/2021.

Merci quand même.

La formule fonctionnant le mieux est celle de @sylvanu même si elle comporte quelques inactitudes (par exemple l'entré d'un salarié le 31/12/2019, la formule me propose une entretien le jeudi 30/12/2021 au lieu du vendredi 31/12/2021). Cela dit elle fonctionne très bien pour le décalage de jour les dimanches donc c'est parfait.

Le problème étant que cela nécessite deux colonnes, une pour calculer la date théorique de l'entretien 2 ans après et une pour enlever les samedis, dimanches et jours fériés, ce qui me fera multiplier le nombre de colonne x2. Les personnes qui vont lire mon tableau étant débutante en Excel, cela risque de fortement les perturber...

​

 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Bon j'ai compliqué inutilement les formules des fichiers (3) et (4), prenez ce fichier (5) :
Code: 
=SERIE.JOUR.OUVRE(A2+731+(JOUR(A2+730)<>JOUR(A2));-1;J$3:J$184)
C'est donc ce post qu'il faut marquer comme solution.
 

Pièces jointes

  • SUIVI ETABLISSEMENT(5).xlsx
    221 KB · Affichages: 19
Vote positif 0 Vote négatif
Solution
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

R
  • Question
XL 2021 Jour férié dans Planning
Réponses
6
Affichages
437
Retraite
R
L
  • Question
Microsoft 365 Plages horaire, décompter nombre d'heures selon type d'horaire
Réponses
10
Affichages
488
Laurencean
L
A
  • Question
XL 2013 Renvoie le jour en dehors des week ends et jours fériés
Réponses
5
Affichages
179
abouam
A
S
  • Question
Microsoft 365 Compteur heures travaillées jour et nuit
Réponses
2
Affichages
539
Sam7
S
P
  • Question
Google Sheets ARRAYFORMULA pour identifier les jours fériés
Réponses
0
Affichages
298
pierrrot
P
C
  • Question
XL 2016 Création d'un fichier Excel de planification d'équipe avec contraintes
Réponses
8
Affichages
320
fanfan38
G
  • Question
Microsoft 365 Formule pour calculer délai de 10 jours, en excluant samedis, dimanches et les jours férié
Réponses
7
Affichages
832
GILBERTO BRAGA
G
P
  • Question
Microsoft 365 lancer une macro excel tous les jour sans ouvrir le fichier
Réponses
6
Affichages
1 K
patricktoulon
G
  • Question
XL 2016 Calcul date incluant jours ouvrables et jours fériés
Réponses
11
Affichages
707
grumeau10
G
  • Question
Microsoft 365 MACRO Remplacer date si jour non ouvré remplace par jour ouvré précédent
Réponses
7
Affichages
625
sylvanu
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…