XL 2010 Solde jours travail/JF/Vacances/Absences

[excelraideo]

Bonjour,

Je me permets d'ouvrir une nouvelle discussion car n'ayant pour trouvé précisément ce dont j'avais besoin dans les discussions existantes.

En effet, j'ai fait un calendrier annuel du suivi des activités pour un groupe cible de notre personnel et qui fonctionne par demi-journée de travail (et non par heure) dans la colonne "T." et les indications de lieu d'activité ou absences dans les colonnes "AM" et "PM". La colonne "S." permet d'obtenir le solde journalier cumulé.

Mon besoin est de faire figurer dans ce fichier les différents éléments suivants (tableaux situés en AQ38-BO48) :

A titre titre informatif, j'ai mis les cases qui demandent d'être revues en jaune.

1. Jours de travail :

- La somme des jours travaillés sous "jours effectués"
- Le solde de jours réels qui prend en compte le planifié sous "solde" (ex. : si 260 jours à faire dans l'année et aujourd'hui on est le 14 janvier mais l'employé n'a pas travaillé = -1 et pas 260-9 = 251).

2. Allocation jours fériés :

- La somme des jours travaillés durant une période en "JF" sous "Jours travaillés JF". Afin de savoir si la personne a travaillé durant la demi-journée "JF", la case d'une colonne "AM" ou "PM" contiendra de tout manière un des deux éléments suivants "-JF" ou "JF-". Je mentionne le "-" car il y aura le site de travail qui suivra au précédera la mention "JF" et qui permet de déterminer que la personne a travaillé durant cette période.

3. Vacances :

- La somme des jours pris sous "Jours pris" (dans les colonnes "AM" et "PM", il y a la mention "VAC" qui permet de déterminer des périodes de vacances)
- Le solde réel des vacances sous "Solde"

4. Absences diverses :

- La somme en jours des différents types d'absences indiquées (maladie, accident, etc.) que l'on retrouve dans le calendrier avec les raccourcis suivants dans les colonnes "AM" ou "PM" :

Maladie = MAL
Covid = COV
Quarantaine Covid = 40
Accident = ACC
Maternité = MAT
Congé familial = CF
Congé non-payé = CNP

J'espère que j'ai été assez clair et que vous pouvez m'aider afin de terminer ce calendrier.

Merci de votre collaboration.

 

[Compte Supprimé 979]

Bonjour,

Il suffit d'utiliser la fonction SOMME.SI(), non

 

[excelraideo]

Bonjour,

J'ai déjà essayé mais en testant avec des vacances par exemple, cela reste à 0. Donc soit je fais faux ou la fonction n'est pas la bonne. Voici la formule utilisée :

=SOMME.SI(D4:BS34;"VAC")

Merci du retour.

 

[riton00]

bonjour,

A tester

=SI(ESTERREUR(NB.SI($D4:$BS34;"VAC"));"";NB.SI($D4:$BS34;"VAC"))

Slts

 

[excelraideo]

Bonsoir,

Merci pour votre retour.

La formule a fonctionné pour le point 3. Vacances et 4. Absences diverses.

Pour le point 2, à savoir la somme des jours travaillés durant une période fériée, cela ne fonctionne pas car lorsque l'employé travaille durant le jour férié on ajoute l'abréviation du lieu d'activité en plus de la mention "JF". C'est pour cela que l'inscription ne doit pas être exactement "JF" mais contenir le mot "JF" car cela peut être "JF-M" ou "JF-REP" par exemple.

Pour le point 1, j'ai un petit souci dans la formule pour la calcul de la durée journalière de travail (colonne "T." du tableau). Comme déjà expliqué, l'employé travaille par demi-journée et si dans une des deux colonnes (AM ou PM) il y a une inscription, c'est 0.5j de travail, si c'est les deux c'est 1j de travail et si c'est aucune ben 0j de travail. Voici la formule utilisée :

=SI(ET(E4="")*NON(ESTVIDE(D4));0,5;SI(ET(D4="")*NON(ESTVIDE(E4));0,5;SI(NON(ESTVIDE(D4:E4));1;"")))

Une fois ce problème résolu, il manque plus que le calcul du solde de jours réel à aujourd'hui (déduction faite des jours planifié).

Je joins le fichier modifié.

Merci de votre collaboration.

 

[Hasco]

Bonjour,

Il me semble que la formule suivante suffirait en colonne T. :

=NBVAL(D4:E4)*0,5

Non ?

cordialement

 

[excelraideo]

Bonjour,

Merci de votre retour. J'ai testé et légèrement changé la formule car il y a 3 critères à prendre en compte. Le temps de travail ne doit pas être pris en compte si dans la plage D6:E6 par exemple, il y a la mention "AI", "VAC" ou "JF". Je n'arrive pas à trouver la bonne formule. Voici ce que j'ai actuellement comme formule :

=(NBVAL(D6:E6)-NB.SI(D6:E6;"*AI*"))/2

Avec cette formule, le hic est que seul le critère "AI" est pris en compte.

Et ce serait TOP si quelqu'un à un retour à me donner pour la somme des jours travaillés durant une période fériée. Comme expliqué, cela ne fonctionne pas avec la formule SI / NB.SI car lorsque l'employé travaille durant le jour férié on ajoute l'abréviation du lieu d'activité en plus de la mention "JF". C'est pour cela que l'inscription ne doit pas être exactement "JF" mais contenir le mot "JF" car cela peut être "JF-M" ou "JF-REP" par exemple.

Merci de votre collaboration.
Cordialement,

 

[Hasco]

Re,

Vous pouvez essayer :

=(NBVAL(D4:E4)-(NB.SI(D4:E4;"*AI*")+NB.SI(D4:E4;"*JF*")+NB.SI(D4:E4;"*VAC*")))/2

Vous avez excel 2010, il est temps de passer au format .xlsx ou .xlsm non ? Le format .xls n'est plus d'actualité depuis la version 2007

Dans le fichier joint , j'ai nommé une cellule Cal.Année (i1 de la feuille Données)
J'ai créé un tableau structuré des jours fériés, nommé la colonne des jours fériés Fériés.Dates.
Enfin à partir de ces éléments, j'ai modifié les cellules A4:A31 et G4:G31, pour que votre calendrier soit valide d'une année à l'autre. Je vous laisse continuer le reste.
Voir les formules en bas de colonne G pour le jour 29 éventuel de février
Dans la colonne A4:A31 et G4:G31 il y a une mise en forme conditionnelle pour les jours fériés.

Cordialement

[Edit 18:59] rechargé le fichier

 

[excelraideo]

Re,

J'ai fait la motif c'est tout bon pour cette partie merci bien !

Si quelqu'un peut encore m'aider sur sur cet élément :

1. Solde des jours de travail à aujourd'hui (case AU44).
Je pense qu'il faudrait trouver une formule qui calcule la différence journalière entre ce qui a été planifié sous la colonne "P." et ce qui a été réellement fait la colonne "T." selon les critères suivants :
Si la valeur de "T." moins "P." = 0 alors cela vaut 1 (car un jour de travail planifié et fait), si la valeur de "T." moins "P." > 0 alors mettre valeur "T." moins "P.", si la valeur de "T." moins "P." < 0 alors mettre valeur "T." moins "P." uniquement si colonnes "AM" et/ou "PM" ne sont pas vides sinon valeur de "P."

Le cumul journalier sur l'année (jours travaillés + planifiés) est ensuite additionné au jours à faire sur l'année (224). Le résultat nous permet de savoir jour après jour si l'employé est en positif ou négatif sur sa balance de jour.

Merci beaucoup !

Cordialement,

 

[excelraideo]

Bonsoir,

J'ai tenté de faire quelque chose pour ma demande ci-dessus et j'ai réussi partiellement...J'ai utilisé la formule suivante :

=SI(F6="";"";SI(C6-F6=0;1;SI(C6-F6>0;C6-F6;SI(C6-F6<0;SI(NBVAL(D6:E6)>0;C6-F6;F6);F6))))

C'est vrai certainement trop longue et tout et peut-être pas la plus adaptée. Dans tous les cas, je l'ai mise sur tous les jours de l'année et le solde tombe juste à 0 sans jours de travail ce qui est juste mais j'ai testé avec un demi-jour d'absence injustifiée ce qui devrait mettre -0,5j mais ça affiche -2j donc je ne comprends pas.

J'ai aussi remarqué que si il y a eu un jour de planifié mais l'employé n'a travaillé qu'une demi-journée, ça enlève 1,5j et pas 0,5j.

Merci de votre retour.

Cordialement,

 

[excelraideo]

Bonsoir,

C'est réglé, j'ai trouvé la solution problème réglé !

Cordialement,