DIF et excel [Résolu]

Réponse 2 / 9

laureal Messages postés 62 Date d'inscription vendredi 14 septembre 2012 Statut Membre Dernière intervention 29 avril 2013
17 sept. 2012 à 11:20

Bonjour Vaucluse,

Merci pour votre réponse et effectivement... pour l'instant le tableau n'est pas explicite du tout... !!

Voici donc le tableau modifié, j'espère que ma demande sera plus précise!

Aussi j'ai bien modifié ma formule par "aujourdhui" :)

Ps : je vous joins un autre tableau très complet que j'ai trouvé sur internet, sauf que je ne comprends pas ses formules... J'aimerai quelque chose dans son style, mais en comprenant :(

- mn tableau corrigé : https://www.cjoint.com/?BIrlueFzbKL
- le tableau trouvé sur internet : https://www.cjoint.com/?BIrluwFRQ3H

Merci encore pour votre aide.

Meilleures salutations.

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 396
17 sept. 2012 à 13:50

Bonjour

pour comprendre les formules, il faudrait pouvoir accéder aux colonnes masquées du fichier que vous avez trouvé. Or celui ci est protégé par mot de passe et donc pas lisible.!!!

Il semblerait toutefois que ce ne soit pas tout à fait ce que vous demandez. Au vu des formules que l'on peut voir ,et en essayant de deviner le reste, il semblerait que le bénéficiaires puisse acquérir des droits après un temps de présence inférieur à 1 an (à priori 1/4 1/2 et 3/4)

Maison devrait pouvoir assez aisément traiter votre sujet sans ce fichier modèle si vous nous expliquez un peu plus et entre autre
_colonne E 20 heures de droit par année complète?
_cette année de référence est elle calendaire c'est à dire qu'un salarié ne peut bénéficier de 20 h que s'il est présent du 1/1/X au 31/12/X, en faisant abstraction des mois de présence antérieurs au 1/1/X??
_que ce passe t'il si moins de un an de présence pour un bénéficiaire?
_Droit utilisés dans l'année: on suppose que l'entrée est manuelle
_Report du solde des années précédentes: d'où va venir cette info?C'est à dire comment s'initialise ce tableau pour les personnes qui ont déjà cumulé des années de droits (ça c'est facile à calculer) et qui en ont déjà consommé (ça, ça ne va pas se trouver tout seul!
_Vous avez en colonne I une formule qui compte le nombre de mois de présence. Quelle est son rapport avec les fait qu'il faille un an pour avoir 20h.Cela ne veut il pas dire qu'il faut travailler avec un quotient mensuel plutôt qu'annuel?
_Votre tableau parle de droit acquis dans l'année et de droit utilisés dans l'année, mais qu'en est il des cumuls précédents?

tout ce que l'on peut calculer facilement avec votre tableau maintenant est le total des droits acquis par année complète pour chacun, ,l'année complète étant considérée du 1° janvier au 31 décembre, mais aucune colonne se semble devoir correspondre à ce calcul?

crdlmnt

Réponse 3 / 9

laureal Messages postés 62 Date d'inscription vendredi 14 septembre 2012 Statut Membre Dernière intervention 29 avril 2013
Modifié par laureal le 17/09/2012 à 14:32

Merci Vaucluse!!

En faisant plusieurs recherches, j'étais tombée sur le même tableau avec les colonnes non masquées, mais je ne l'avais pas enregistré, si je le retrouve entre temps, je le posterai !

Je réponds à vos questions :
- oui, il faut avoir une année complète pour acquérir les 20h, par exemple : un salarié embauché le 01/10/11 ne pourra pas utiliser ses heures avant le 31/12/12 puisque l'acquisition se fait au 31/12/N.

- En reprenant l'exemple précédent, le salarié en question aura au 31/12/12 : 23h de DIF (20*14 mois d'ancienneté/12 mois dans l'année).

- si le salarié a moins d'1 an d'ancienneté, il ne peut pas bénéficier de son dif. Je devrais donc effectivement ajouter une colonne avec date d'ouverture des droits au dif.

- oui, au plus simple l'entrée sera manuelle mais je pourrai aussi ajouter une feuille plus tard afin de faire une formule automatique.

- il me manque pas mal de colonnes, effectivement, comme je n'ai encore rien fait, je ne me suis pas posée toutes les bonnes questions ! J'aimerai également que cela soit automatique, sachant que l'on ne peut pas dépasser 120h de cumul.

- la colonne I était utilisée pour calculer l'ancienneté du salarié. J'avais au début utilisé la formule suivante : 20heures * mois d'ancienneté/12 = heures acquises.

- le cumul précédent était pour moi "le report du solde des années précédentes" !

J'avoue qu'il est difficile de comprendre un tableau, surtout quand l'auteur le fait mal... !! Je n'ai pas encore ciblé qu'elles sont les bonnes informations a prendre en compte... :(

En tout cas je vous remercie pour vos retours !

Laure :)

Réponse 4 / 9

laureal Messages postés 62 Date d'inscription vendredi 14 septembre 2012 Statut Membre Dernière intervention 29 avril 2013
Modifié par laureal le 17/09/2012 à 16:26

Vaucluse,

Finalement après plusieurs essais, j'ai trouvé une autre façon de faire, qui me parait bcp plus simple... J'ai tout de même un souci.

Savez vous s'il existe une formule qui indique automatiquement une date au 31/12/N+1 ? Car pour l'instant, j'ai indiqué manuellement la date d'ouverture des droits.

Je vous joins mn tableau modifié et bcp plus simple... !! https://www.cjoint.com/?BIrpYLabq0q

Merci encore.

Laure.

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 396
17 sept. 2012 à 16:48

Re Laure

pour éviter de partir sur de mauvaise base, je vous laisse avancer votre tableau en répondant à vos demandes.
pour avoir la fin d'année au 31/12 N+1 en C7
=DATE(ANNEE(B1)+1);12;31)

restera un seul problème, concernant la date d'embauche peu probable du 1/1/xxxx, car en théorie, l'ouverture des droits devrait se situer au 31/12 de la même année.

Mais d'une part c'est cas de figure sans doute rare (!!) et d'autre part on peut le contourner avec:
=DATE(SI(ET(MOIS(B1)=1;JOUR(B1)=1);ANNEE(B1);ANNEE(B1)+1);12;31)
Est ce bien utile... c'est sans doute du purisme?

Une autre remarque
en colonne E votre calcul de droit est exact et peut être même un peu trop, car si la cellule affiche bien 23 la valeur numérique que va utiliser excel ensuite et complète, à savoir 23,3333333333333 etc...

quand vous allez empiler des calculs à partir de cellules traitées de cette façon, vous risquez de voir afficher des résultats assez curieux
.
Faites par exemple pour comprendre un test: entrez en F7=C7*3 et vous verrez que trois fois 23 font 70

Il est possible que cela correspondre à vos besoins, bien sur. Si toutefois vous voulez éviter ça, vous pouvez utiliser:

=ARRONDI(20*D7/12;0)
qui arrondira sans décimale au nplus proche par rapport à 0,5
ou ARRONDI.PLUS qui arrondira toujours à l'entier supérieur
ou ARRONDI.INF qui.... ........................................inférieur, évidemment

à vous lire pour la suite selon votre avancement

bien cordialment

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 9

laureal Messages postés 62 Date d'inscription vendredi 14 septembre 2012 Statut Membre Dernière intervention 29 avril 2013
18 sept. 2012 à 11:11

Bonjour Vaucluse,

Merci encore pour votre aide précieuse! Je suis entrain d'avancer et bientôt terminer mon suivi, maintenant que je l'ai en tête, c'est plus simple.

Aussi j'aurai une petite question pour la formule "datedif". Excel considère qu'il y a 14 mois de différence entre : 08/10/07 et 31/12/08. Cela est du je pense au fait qu'au 31/12, le mois n'est pas complètement terminé.

Auriez vous par hasard un moyen pour que le résultat affiché soit de 15 mois de différence ?

Merci par avance :)

Laure

Ps : Je vous enverrai le tableau une fois terminé :)

Réponse 6 / 9

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 396
Modifié par Vaucluse le 18/09/2012 à 11:27

Bonjour Laure

Dans tous les cas, DATEDIF en mois vous renverra toujours le nombre de mois complets en excluant le premier jour, même si le premier jour tombe le 1°
Ce 'est donc pas décembre qui est exclus, mais octobre

1/10/20087 avec 31/12/2008 donne toujours 14
Si vous voulez dans tous les cas tenir compte du mois de début, même s'il n'a qu'un jour d'affecté, vous n'avez qu'à rajouter 1 au résultat:

=DATEDIF(Cell début; cell fin;"m")+1

cette solution avec votre exemple vous renvoie 15 mois si le premier jour du mois est en octobre 2007, et 16 si il est en Septembre , même s'il s'agit du 30/9/2007

crdlmnt
ne sommes nous pas seuls à comprendre ce que l'on explique?

Réponse 7 / 9

laureal Messages postés 62 Date d'inscription vendredi 14 septembre 2012 Statut Membre Dernière intervention 29 avril 2013
18 sept. 2012 à 16:41

Re-Bonjour Vaucluse,

J'ai enfin réussi a avoir mes formules correctes... https://www.cjoint.com/?BIsqNYcVcaZ

Bon, le tableau ne ressemble pas du tout a ce que je veux, mais au moins j'ai réussi a le faire fonctionner pour la concordance avec les heures de DIF par salarié.

Il n'est donc pas encore opérationnel...mais je reste sur le sujet !!

Encore merci pour votre aide, je pense que demain il sera complètement terminé!

Bonne fin de journée :)

Laure

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 396
18 sept. 2012 à 18:42

Bonsoir Laure

il y a une bizarreries dans ton tableau?
Je ne vois pas à quoi correspondent les valeurs décimales mélangées avec les horaires dans la champ J à Q
Sachant que pour excel une valeur horaire avec une virgule est considérée comme un nombre de jours, pas d'heures.
Ainsi si vous formatez J à Q en [hh]:mm:ss vous avez des résultats surprenants?

Je ne suis pas sur que ça corresponde à ce que tu veux faire . Par exemple en L3 vtu devrais avoir logiquement 36:40 au lieu de 26,4 ???
Soit je pense 16:40 +20h qui s'écrit en Excel:
=K3+20/24

Pour le reste on aurait pu automatiser un peu plus le tableau J à Q mais vu le nombre de lignes à traiter ce n'est sans doute pas utile.
A toutes fins utiles:

https://www.cjoint.com/?BIssMGLssjB

ci dessus ce que ça donne en supprimant les colonnes intermédiaires masquées que tu as utilisées pour le calcul horaire et e recalculant totalement le tableau G Q en incluant la valeur de E au bon endroit.
reste aussi la, possibilité de limiter à 120 dans la même formule lorsque les totaux dépassent.
La colonne G vide (et doit rester vide) ne sert qu'à permettre d'avoir la,même formule de G, à Q, mais on peut s'en passer avec une formule différente en G

bien cordialement

laureal
20 sept. 2012 à 12:01

Salut Vaucluse,

Excuse moi je n'ai pas pu répondre hier... oui pfff je ne m'étais pas aperçue de mon erreur, c'est bien 36,4 en I et non pas 26,4 ! Enfait j'ai rajouté manuellement les 20h partout... (en me trompant ;) pour éviter de tout repasser en numérique, ne connaissant pas ta formule !!

Merci encore vraiment pour toute l'aide apportée! Je n'ai pas le temps de my consacrer aujourd'hui mais je my remet demain, car effectivement le but est de ne pas dépasser les 120h donc je vais chercher une formule qui va bien pour cela...!!

Très bonne journée :)

Laure

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 396
20 sept. 2012 à 15:18

Bonjour Laure

à toutes fins utiles si vous avez des problèmes, voici un modèle avec le plafond à 120h

https://www.cjoint.com/?BIuprcsmPQR

bien cordialement

Réponse 8 / 9

laureal
20 sept. 2012 à 16:51

Et bien Vaucluse, encore merci pour tout!! Au risque d'abuser...serait il possible que vous m'expliquiez les formules ? Cela commence à être confus...

Et le must serait de me les expliquer en "francais" si, et alors ! Je ne sais pas si cest très clair ce que je raconte la...

Super en tout cas, le but que je recherchai est bien la, merci d'avoir pris le temps de m'aider (faire) !!

Très bonne journée :)

Laure

Réponse 9 / 9

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 396
Modifié par Vaucluse le 20/09/2012 à 18:22

Re re

en H3
=SI(ANNEE(H$1)=ANNEE($C3);$E3;SI(G3+20/24>120/24;120/24;SI(G3>0;G3+20/24;0)))
Déjà: 20/24 =20:00 pour Excel , de même 120/24 =120:00:00

conditions 1
SI(ANNEE(H$1)=ANNEE($C3);$E3
si le N° de l'année de la colonne C est égal au N° d'année en titre de la colonne H, la formule renvoie la valeur de la colonne E et on s'arrête là

si non on passe à la condition 2 suivante
SI(G3+20/24>120/24;120/24;
On prend la cellule précédente (donc la valeur de l'année d'avant) et on ajoute 20h
si le résultat est supérieur à 120, on affiche 120:00:00

Si non on passe à la dernière condition 3
SI(G3>0;G3+20/24;0)))
qui dit que si la cellule précédente n'est pas égale à 0 on ajoute 20 heures.
(D'ou l'utilité de la colonne G qui doit rester vide pour avoir la même formule dans tout le tableau et qui peut être masquée)

Le but est de détecter dans cette dernière condition que si la cellule précédente a une valeur nulle, c'est que le personnel n'était pas présent cette année et là et donc qu'il faut attendre la 1° colonne avec une valeur pour ajouter 20:

Ce n'est peut être pas très clair, excusez moi, mais ce n'est pas facile à expliquer,

crdlmt
ne sommes nous pas seuls à comprendre ce que l'on explique?

azurly Messages postés 3 Date d'inscription mardi 16 avril 2013 Statut Membre Dernière intervention 17 avril 2013
17 avril 2013 à 09:35

Bonjour,

J'ai téléchargé votre tableur pour DIF et il est top! Merci beaucoup!
Par contre j'ai une question concernant les absences qui doivent être décomptées du calcul de DIF? de même les personnes ayant une ancienneté antérieure à 2004?
Comment procédez-vous?
Merci de votre retour,
Cdt,
Azurly

DIF et excel

9 réponses

Discussions similaires

Newsletters