Pb formule date dans une autre période de date

[fred777]

Bonjour à tous,

Le sujet n'est pas très explicite.

J'ai un fichier de gestion des absences. Je rentre les dates de début et fin d'absence et la formule me donne le nombre de jours d'absence.

J'ai 3 types de salariés :
Les mensuels : la formule me décompte les jours ouvrés
Les horaires "H" : Ces personnes ne travaillent pas les mercredi ni les vacances scolaires
Les horaires "HM" : Ces personnes travaillent les mercredi mais pas les vacances scolaires

Ma formule fonctionne (en G7), mais mon problème que je ne parviens pas à résoudre c'est que les "H" et "HM" peuvent également travaillé durant les vacances ou une partie de celles-ci (date travaillée en Feuille Gestion des absences Cellules E et F). Je ne trouve pas la formule, je m'arrache les cheveux.

Merci d'avance pour vos lumières.

 

[fred777]

Re : Pb formule date dans une autre période de date

Bonjour,

Il n'y a personne pour m'aider ?

Merci d'avance

 

[Gurgeh]

Re : Pb formule date dans une autre période de date

Salut fred777,

Afin de pouvoir bien comprendre ton besoin, est ce que tu pourrais préciser dans quelles conditions la formule ne donne pas le résultat que tu voudrais, et quel est le résultat que tu voudrais dans ce cas...

Gurgeh

 

[fred777]

Re : Pb formule date dans une autre période de date

Bonjour Gurgeh,

Merci de te pencher sur mon problème.

En fait, voila mon pb.

Les employés qui ont le staut "H" et "HM" sont des employés à temps non complet.
Les 1er ne travaillent pas le mercredi ni les vacances scolaires
Les 2nd travaillent le mercredi mais pas les vacances scolaires

Dans le fichier joint on peut voir qu'il y a des vacances au mois d'octobre 2011 (du 24/10/2011 au 02/11/2011).
Si mon employé "H" est en maladie du 02/10 au 04/11, la formule (en G7) va me décompter 14 jours (12 jours ouvrés, sans les mercredi, week-end et jours fériés du 02/10 au 23/10 + 2 jours après les vacances les 3 et 4/11)
Pour l'employé "HM" c'est pareil, sauf que celui-ci travaille normalement le mercredi : donc avec les mêmes dates d'absence, j'obtiens 17 jours pour ce dernier.

Jusqu'ici tout va bien !

La contrainte que j'ai, c'est que ces employés sont susceptibles de travailler durant les vacances scolaires. Dans ce cas, je saisie les dates de vacances travaillées (début et fin) dans la feuille 'Gestion Absences' cellules E et F.

Supposons que l'employé "H" travaille pendant une partie des vacances (du 24/10/11 au 28/10/11), soit 5 jours (le mercredi des vacances étant un jour travaillé).
Si on reprend les dates d'absences (du 02/10 au 04/11), la formule devrait me renvoyer 14 jours + 5 jours (période de vacances où l'employé aurai du travailler).

Je me prend la tête depuis des semaines sans trouver la bonne formule (sachant que la personne peut travailler toutes les vacances ou seulement une partie de celles-ci)

J'espère être suffisament clair.

Merci d'avance de tes lumières.

 

[Gurgeh]

Re : Pb formule date dans une autre période de date

Bon, je progresse dans ma compréhension...

En gros, les "H" et les "HM" ne travaillent pas pendant les vacances scolaires en général, sauf cas particulier de temps en temps, et c'est la gestion de ces cas particuliers qui te pose problème. C'est ça ?

Gurgeh

 

[fred777]

Re : Pb formule date dans une autre période de date

Absolument,

Tu as bien compris ma problématique.

 

[fred777]

Re : Pb formule date dans une autre période de date

Bonjour Gurgeh,

As-tu pu jeter un oeil sur mon problème ?

Je t'en remercie d'avance.

 

[Gurgeh]

Re : Pb formule date dans une autre période de date

Bon, j'avoue que j'ai eu des difficultés à essayer de retracer la construction de ta fonction. Du coup je suis reparti du besoin et j'ai reconstruit une formule perso.

Elle utilise la fonction NB.JOURS.OUVRES.INTL qui permet de calculer un nombre de jours ouvrés en spécifiant des jours spécifiques à enlever chaque semaine et une liste de jours fériés. Je te laisse regarder l'aide de la fonction pour le détail (et pour savoir si ta version d'excel la prend en charge, ce n'est pas évident).

La fonction NB.JOURS.OUVRES.INTL prend comme deux premiers arguments les jours de début et de fin, et comme troisième argument une chaîne qui décrit quels sont les jours qu'il faut compter et quels sont ceux qu'il faut exclure chaque semaine. Exemple : "0100100" va exclure tous les mardis et tous les vendredis.

On va donc utiliser le code suivant qui permet de renvoyer "0010011" (ne pas compter les mercredis et les S/D) ou "0000011" (ne pas compter les S/D) en fonction du type de contrat de la personne :

"00"&SI(RECHERCHEV(B7;statut;2;FAUX)="H";"1";"0")&"0011"

Le dernier argument de la fonction NB.JOURS.OUVRES.INTL est la liste des jours fériés. On va donc lui donner vacfer (la liste des jours fériés + les vacances scolaires) ou jfdate (la liste des jours fériés) en fonction du type de contrat :

SI(RECHERCHEV(B7;statut;2;FAUX)="M";jfdate;vacfer)

Ce qui donne la formule suivante pour calculer le nombre de jours ouvrés en fonction de l'horaire de la personne :

=NB.JOURS.OUVRES.INTL(D7;E7;"00"&SI(RECHERCHEV(B7;statut;2;FAUX)="H";"1";"0")&"0011";SI(RECHERCHEV(B7;statut;2;FAUX)="M";jfdate;vacfer))

Maintenant, on va y ajouter le nombre de jours travaillés en plus sur les vacances scolaires en enlevant les S/D et les jours fériés :

NB.JOURS.OUVRES.INTL(RECHERCHEV($B8;CL;3;FAUX);RECHERCHEV($B8;CL;4;FAUX);"0000011";jfdate)

ce qui donne la formule globale :

=NB.JOURS.OUVRES.INTL($D8;$E8;"00"&SI(RECHERCHEV($B8;statut;2;FAUX)="H";"1";"0")&"0011";SI(RECHERCHEV($B8;statut;2;FAUX)="M";jfdate;vacfer))+NB.JOURS.OUVRES.INTL(RECHERCHEV($B8;CL;3;FAUX);RECHERCHEV($B8;CL;4;FAUX);1;jfdate)

Bon, c'est sans doute pas parfait, il faudrait gérer les cas d'erreur (quand le recherchev sur CL ne donne rien car il n'y a pas de travail supplémentaire), mais je pense que ça te fait une bonne base de réflexion.

A ta dispo si difficulté.

Gurgeh

 

[fred777]

Re : Pb formule date dans une autre période de date

Bonjour Gurgeh,

Merci pour cette formule, elle fonctionne bien sur ma version excel.

Seul bémol, et je ne parviens pas à le résoudre : Pour le personnel "H", si l'arrêt de travail débute ou fini dans la période de vacances travaillée, la formule me compte toute les vacances.

Exemple : Pour personnel "H" (pas de mercredi et pas de vacances scolaires sauf choix du salarié)

Vacances scolaires du 24/10/2011 au 02/11/2011
La personne choisi de travailler la 1ère semaine des vacances du 24/10 au 28/10
Si la personne est en arrêt du 18/10 au 25/10, la formule me renvoie 8 jours alors qu'elle devrait me renvoyer 5 jours (le 18,20,21,24 et 25/10)
En fait dès que l'arrêt débute ou fini pendant la période de vacances ou la personne a choisi de travailler, la formule me compte toute la période choisie. Dans cette exemple, il me compte : le 18,20,21,24,25,26,27 et 28/10)

Je ne sais pas si je suis clair mais je te remercie encore pour ton aide.

Fred

 

[Gurgeh]

Re : Pb formule date dans une autre période de date

Ah effectivement je n'avais pas prévu ce cas là.

Dans ce cas, remplace

NB.JOURS.OUVRES.INTL(RECHERCHEV($B8;CL;3;FAUX);RECHERCHEV($B8;CL;4;FAUX);"0000011";jfdate)

par

NB.JOURS.OUVRES.INTL(MAX(RECHERCHEV($B8;CL;3;FAUX);$D8);MIN(RECHERCHEV($B8;CL;4;FAUX);$E8);"0000011";jfdate)

et ça devrait coller.

Je te laisse tester pour trouver les autres cas incorrects, on n'a sans doute pas fini...

Gurgeh

 

[fred777]

Re : Pb formule date dans une autre période de date

Bonjour Gurgeh,

Encore mille merci, la nouvelle formule fonctionne bien.

Juste un problème et tu semblait l'avoir vu : lorsque le recherchev sur CL ne donne rien car il n'y a pas de travail supplémentaire (il est donc vide) : la formule me renvoie en nombre de jour un chiffre du genre " -2946 " alors que je devrais avoir " 11 " jours.

Peut-être une formule du genre si recherchev CL est vide alors ...

Merci à toi

Fred

 

[Gurgeh]

Re : Pb formule date dans une autre période de date

Tu peux tester si recherchev te renvoie quelque chose en utilisant la fonction ESTNA(), avec une syntaxe du genre :

si(ESTNA(RECHERCHEV(.....);0;RECHERCHEV(.....))

un peu lourd mais ça marche... Je te laisse modifier la formule en conséquence, à ta dispo si difficulté...

Gurgeh

 

[fred777]

Re : Pb formule date dans une autre période de date

Merci encore une fois,

Je dois avoir le cerveau un peu embrumé en cette fin de semaine, mais je ne parviens pas à modifier la formule :

=NB.JOURS.OUVRES.INTL($D8;$E8;"00"&SI(RECHERCHEV($B8;statut;2;FAUX)="H";"1";"0")&"0011";SI(RECHERCHEV($B8;statut;2;FAUX)="M";jfdate;vacfer))+NB.JOURS.OUVRES.INTL(MAX(RECHERCHEV($B8;CL;3;FAUX);$D8);MIN(RECHERCHEV($B8;CL;4;FAUX);$E8);"0000011";jfdate)

avec la fonction :

si(ESTNA(RECHERCHEV(.....);0;RECHERCHEV(.....))

Désolé d'abuser.

Cordialement.

Fred

 

[Gurgeh]

Re : Pb formule date dans une autre période de date

En fait ce que je t'ai suggéré ne marche pas, car si(ESTNA(RECHERCHEV(.....);0;RECHERCHEV(.....)) va te renvoyer 0 si on ne trouve rien, et dans la formule générale si on met 0 ça ne marche pas.

Donc il faut simplement faire un test pour savoir s'il y a quelque chose de renseigné en CL pour l'employé en question, et ensuite utiliser la formule qui permet de calculer les jours correspondants :

=NB.JOURS.OUVRES.INTL($D8;$E8;"00"&SI(RECHERCHEV($ B8;statut;2;FAUX)="H";"1";"0")&"0011";SI(RECHERCHEV($B8;statut;2;FAUX)="M";jfdate;vacfer))+SI(ESTNA(RECHERCHEV($B8;CL;3;FAUX);;NB.JOURS.OUVRES.INTL(MAX(RECHERCHEV($B8;CL;3;FAUX);$D8);MIN(RECHERCHEV($B8;CL;4;FAUX);$E8);"0000011";jfdate))

J'ai pas testé mais ça doit marcher !

Gurgeh

 

[fred777]

Re : Pb formule date dans une autre période de date

J'ai testé, mais excel me dit "vous avez tapé un nombre trop important d'argument pour cette fonction".

Une idée ?