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é.

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.

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

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
 

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

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

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
 

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
 

job75

XLDnaute Barbatruc
La formule ne fonctionne pas sur la 1ère cellule (C2), cela indique 2019 au lieu de 2020 alors que les autres sont bonnes.
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

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...

 

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

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…