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

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

Statistiques des forums

Discussions
312 890
Messages
2 093 352
Membres
105 696
dernier inscrit
FrancisR