comment faire un cumul mensuel de congés

[rh.finances]

bonsoir à tous les experts d'excel,

cela fait plusieurs jours que j'essaie de me démener sur un tableau excel afin d'introduire un cumul mensuel des congés.

le total des congés pris se situe en F18:F48 (nbr jours). il est fonction de dates de début inscrites en B18:B48 et de dates de fin inscrites en C18:C48. ces congés sont décomptés avec la fonction NB.JOURSOUVRES qui tient compte des jours ouvrés et des jours fériés.
la colonne "type" en c18:c48 précise le type de congés pris: CA (congés annuels), JM (journée du maire),etc.

je souhaiterais faire le cumul mensuel des seuls congés annuels (CA) inscrits en F18:F48 sur la sélection B73:G76. impossible de parvenir à mes fins

j'ai essayé entre autre la fonction SOMMEPROD mais je crois que je maîtrise très mal cette fonction.
bref, j'ai bataillé plusieurs heures durant mais rien à faire, j'ai connu l'échec à tous les coups.

je fais donc appel à tous les internautes qui sauraient me venir en aide!!

d'avance merci beaucoup!!

Alex

 

[Jiheme]

Re : comment faire un cumul mensuel de congés

Bonjour RH, re à tous

Ton fichier en retour avec SOMMEPROD sur janvier à avril.

Il faut que sur ce tableau récap les mois soient des dates. J'ai aussi nommé les plages du tableau de détail "date" "type" et "nbj".

regarde si cela te conviens

A+

 

[jeanpierre]

Re : comment faire un cumul mensuel de congés

Bonsoirrh.finances et Bienvenue sur XLD, Jiheme,

De plus, en F16 et suivant, pour tenir compte des jours fériés, modifie ta formule comme suit :

=SI(ET(D16=A50;D16=A60;D16=A63;D16=A66);0;NB.JOURS.OUVRES(B16;C16;F58:G69))

Bonne soirée.

Jean-Pierre

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

Merci beaucoup Jiheme pour ta contribution!! c'est vraiment sympa de ta part!!
je ne sais pas comment tu as su rentré ces données ("nbj"? etc.) et je vois que j'ai beaucoup à apprendre en en ce domaine.

en revanche, j'ai toujours un petit souci de comptabilisation par mois.
par exemple: j'ai indiqué sur la ligne 22 un congé annuel allant du 30/11/09 au 04/12/09. le cumul mensuel devrait ainsi indiquer 1 jour en novembre sur G75 (30/11/09) et 4 jours en décembre (du 01/12 au 04/12) en G76.
or le tableau indique 5 jours en novembre(cellule G75).

ne faudrait-il pas que j'indique une date de fin sur les périodes de cumul?

et encore merci beaucoup!!!

Alex

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

bonsoir Jean Pierre,

tu as parfaitement raison. j'avais intégré cette donnée lors d'un premier enregistrement et je ne sais pas pourquoi, les jours fériés n'apparaissaient plus.

l'erreur est réparée.

encore merci pour cette information salutaire!!

Alex

 

[Jiheme]

Re : comment faire un cumul mensuel de congés

Re Rh, Jean-Pierre

Toujours le regard aussi perçant JP je n'avais même pas regardé ce point de jour fériés.

Rh pour les plages nommées, une recherche sur le site devrait te permettre de trouver les explications nécessaires, sinon reviens et on tachera de t'expliquer.

Pour ce qui est des périodes à cheval sur deux mois, les formules du fichier ne peuvent pas les gérer, la solution est donc de faire comme tu le suggères 30/11 et sur la ligne du dessous 01/12 au 04/12.

C'est sûrement possible de gérer cela mais ça demande réflexion, du moins à moi!!!

A toi de voir si cela te conviens ou si l'on doit se faire péter le dernier neurone, je crois que je chercherais au moins pour le fun

A+

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

Re Jiheme,

c'est en effet une opération plutôt complexe. j'ai essayé de retravailler cela mais ça n'a pas donné grand chose.
j'ai ainsi recréer un tableau récapitulatif sur les lignes 82 à 93 en indiquant en colonne B la date de début de mois et en colonne C la date de fin de mois via la formule "FIN.MOIS". la colonne D8293 doit permettre de recenser selon les bornes définies précédemment le nombre de CA pris chaque mois.
j'ai essayé la fonction sommeprod (à mon niveau) en y incluant à la fois les CA et les CA-1 (cellule A53 que j'ai créée récemment). mais rien n'y fait, j'ai toujours le même message: "

VALEUR!"

Helllpppp

j'ai par ailleurs tenté de faire du VBA (pas facile pour un débutant) en intégrant une alerte "texte" en cas de dépassement de valeur entre 2 cellules. j'ai bien intégré le code dans l'objet "feuil" et non dans module. la formule semble bonne et pourtant rien ne se passe

en tout cas merci beaucoup pour toutes les avancées déjà portées sur mon tableau.

A plus

Alex

 

[Jiheme]

Re : comment faire un cumul mensuel de congés

Bonjour Rh, re à tous

Un essai ci-joint en rajoutant 2 colonne( G et H) que l'on masque, je pense que si les congés ne dépassent pas le mois suivant cela marche, par contre en cas de congés genre 31/07/09 au 02/09/09 ce ne sera pas le cas.

Faute de temps, je n'ai pas fais tous les essais possibles, fais le souffrir et dis nous.

A+

 

[exene]

Re : comment faire un cumul mensuel de congés

Bonjour,

Ci-joint un fichier avec des formules développées par Monique qui te permettront de faire un calcul mensuel des CA même si la période posée est à cheval sur deux mois distincts.
A+ et au plaisir de te lire.

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

Bonsoir Exene et Jiheme,

et encore merci de votre collaboration si précieuse!

j'ai regardé chacune de vos propositions qui m'ont l'air très intéressantes.
toutefois, je n'ai pu faire aucun test en modifiant ou en ajoutant des dates de congés.
a chaque modification les colonnes "nb jours" et "solde" ont affiché un message d'erreur "NOM?".
je suppose que ce message d'erreur est du aux fonctions complémentaires "NB.JOURS.OUVRES" et "FIN.MOIS". j'ai alors essayé de faire "macros complémentaires" et "utilitaire d'analyse"... ça n'a pas marché

faut-il procéder différemment pour corriger cette erreur?

encore merci pour toute votre aide!

Alex

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

Oupps, j'oubliais!

Jiheme, dans le calcul mensuel sur les cellules D82 à D93, si je souhaites rajouter comme type de congés les CA-1(totalisation en cellule A53), dois-je indiquer:
(type="ca";"ca-1")?

Alex

 

[Jiheme]

Re : comment faire un cumul mensuel de congés

Bonsoir Rh, re à tous

Ci joint ton fichier modifié, je pense que ton message d'erreur venait du fait que je n'avais pas descendu les formules dans les colonnes masquées.

Pour ajouter les "CA-1", il faut faire + et ajouter les SOMMEPROD en modifiant le type.

C'est fait dans le fichier joint, regardes et dis nous...

A+

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

Bonjour Jiheme,

tout simplement... BRAVO!!! ça marche impeccable!! même si les formules ne permettent pas de quantifier le nombre de congés consécutifs sur plus de 2 mois, cela n'a aucune importance. dans notre collectivité, les agents ne peuvent s'absenter plus de 31 jours calendaire consécutifs. donc le cas de figure évoqué plus haut a peu de chance de se produire!!

Merci, merci et encore merci beaucoup Jiheme pour cette aide si précieuse.

et encore merci aux autres internautes de m'avoir apporter leur aide sur la constitution de ce tableau dont j'ai cru un moment ne jamais pouvoir me sortir!!

je vais à présent essayer de créer des messages d'alerte en VBAen cas de dépassement . ça promet d'être chaud!!

et encore une fois... MERCI

 

[Jiheme]

Re : comment faire un cumul mensuel de congés

Bonsoir Rh, Le Forum

Je sais pas exactement ce que sont tes alertes, mais tu peux peut être déjà essayé avec des MFC (Mise en forme conditionnelles) genre une case qui devient rouge si nb congés > 30 (par exemple)

je vais à présent essayer de créer des messages d'alerte en VBAen cas de dépassement . ça promet d'être chaud!!

Si tu as besoin reviens
A+

 

[rh.finances]

Re : comment faire un cumul mensuel de congés

Bonsoir Jiheme (encore merci pour tout), bonsoir à tous les internautes!!

mes messages d'alerte doivent permettre d'avertir les utilisateurs, voire de bloquer leur saisie, en cas dépassement des droits à congés.
pour cela (je pense), il faut passer par le langage VBA.

j'ai donc commencé à constituer un dossier en VB afin d'arriver à mes fins (voir fichier joint). mais je rencontre des difficultés dans la définition des codes.

ex: un agent dispose d'un nombre de congé "CA-1"= 10 (cellule c11) qui correspond au report de congés n-1 défini en début d'année.
en cours d'année, il épuise au fur et à mesure ces reports de congé dans la partie "décomptes des congés..." (ligne 18).
par mégarde (ou par esprit arnaqueur ), il enregistre un report de congés supérieurs aux droits qui lui sont attribués (en C11 toujours). dans ce cas, le formulaire doit lui balancer (ou lui bloquer si possible) un message d'erreur précisant qu'il est en dépassement et qu'il ne peut pas aller au-delà des droits à congés CA-1 qui lui sont attribués dans la celulle C11.

autre ex: en C72 (congé déménagement), si la valeur enregistrée est supérieure à 1, nous devrions avoir un message d'alerte et pourtant rien ne se produit!!

les formules VB ont pourtant l'air correct mais pour autant, l'effet escompté ne se produit pas!!!

help Jiheme!! help à tous!!