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

[kellypombal]

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

 

[sylvanu]

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 :

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

 

[Efgé]

Bonjour

Une autre à mettre en H9 et à tirer jusqu'en J9. (La colonne K reprend la colonne J)

=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]

Bonjour kellypombal, sylvanu, Efgé,

Décalage de 2 ans en B2 :

=A2+730+(JOUR(A2+730)<>JOUR(A2))

Date EP en C2, formule matricielle :

=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+

 

[Efgé]

Re
Plus courte:

=SERIE.JOUR.OUVRE(MOIS.DECALER($F9;(24*COLONNES($A:A)))-1;1;'jours fériés'!$K$3:$K$184)

Cordialement

 

[job75]

Plus simple avec MAX, toujours en matriciel :

=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))))

 

[kellypombal]

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 :

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

Merci

 

[kellypombal]

Merci à tous

Bonjour

Une autre à mettre en H9 et à tirer jusqu'en J9. (La colonne K reprend la colonne J)

=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]

Bonjour kellypombal, sylvanu, Efgé,

Décalage de 2 ans en B2 :

=A2+730+(JOUR(A2+730)<>JOUR(A2))

Date EP en C2, formule matricielle :

=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]

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) :

=MAX(SERIE.JOUR.OUVRE(B2+1;-LIGNE(INDIRECT("1:4"));K$3:K$184))

Elle ne nécessite pas de validation matricielle.

 

[kellypombal]

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) :

=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]

Le seul problème c'est que cela nécessite 2 case

Bah c'est assez évident, si l'on ne veut pas de la colonne + 2 ans, fichier (4) avec cette formule en B2 :

=MAX(SERIE.JOUR.OUVRE(A2+730+(JOUR(A2+730)<>JOUR(A2))+1;-LIGNE(INDIRECT("1:4"));J$3:J$184))

 

[job75]

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

Non vous n'êtes pas réveillée; la formule propose la date du 24/12/2021.

 

[kellypombal]

Bah c'est assez évident, si l'on ne veut pas de la colonne + 2 ans, fichier (4) avec cette formule en B2 :

=MAX(SERIE.JOUR.OUVRE(A2+730+(JOUR(A2+730)<>JOUR(A2))+1;-LIGNE(INDIRECT("1:4"));J$3:J$184))

Merci beaucoup, cela résout mon problème ! Merci, merci, merci !

 

[job75]

Bon j'ai compliqué inutilement les formules des fichiers (3) et (4), prenez ce fichier (5) :

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