Bonjour,

je cherche via les fonctions SI et ET a mettre en place une formule qui me permettra de suivre les effectifs d'un service sur une année de réference et ayant leur date d'arrivée et de depart.

exemple : paul est arrive dans le service le 01/02/2012 et son depart est prévu le 02/03/2014 : combien de mois Paul sera présent dans le service au cours de l'année 2013?

j'ai commencé à trouver un debut de formule, mais avec les tests imbriqués je m'emmele à la fin !! Pour jacques, j'ai trouvé =SI(ET(E9>12;C9<$F$2);DATEDIF($F$2;D9;"D")/30;DATEDIF(C9;D9;"d")/30) !car il part durant l'année de reference, mais pour pierre et paul, je n'y arrive pas car il faut ajouter en plus une condition avec la date de depart qui est > à la date de fin d'année de reference. Si vous pouvez m'aider ce serait tres sympa.

Bonsoir et bienvenue sur PCAstuces,

Si tu mettais un modèle de ton tableau en utilisant http://cjoint.com et en précisant le résultat que tu veux obtenir, selon quelle méthode, en quelle unité... on pourrra y voir plus clair et te proposer la ou les formules qui conviennent.

merci pour cette première reponse. en fait je suis debutant sur le forum est je n'ai pas bien su joindre le fichier que j'avais préparé.

le voici. En fait, dans le tableau ci joint, j'ai reussi a trouver la formule pour "pierre" en G9, mais il me manque quelque chose pour pierre et paul. le problème vient du fait que l'annee de sortie/depart est posterieur a l'annee de reference.

http://cjoint.com/?DBfaGz1m6QM

merci pour votre aide

Matiti

Bonsoir,

Je suppose que tu as voulu écrire : =DATEDIF(SI(C7<$F$2;$F$2;C7);SI(D7>$F$3;$F$3;D7);"d")/30

Ce qui va te donner un nombre supérieur à 12 (pour les 2 premiers cas) même si tu n'affiches pas les décimales et donc un % supérieur à 100%

Tes calculs successifs et tes formatages de cellules ne permettent pas d'avoir de certitudes sur la finalité de tes calculs.

Je ne suis pas sûr que DATEDIF soit la fonction la mieux adaptée à ce que tu recherches...

Tu dis au départ que tu calcules en mois : s'agit-il de mois calendaires, de mois évalués de date à date, de mois ramenés à 30 jours (sur une année de 360), ...?

Considère-t-on uniquement des mois entiers ? Ou est-ce qu'on prend en compte des fractions de mois ?

[NB- Si tu utilises DATEDIF pour calculer une différence en mois entre le 01/01/2013 et le 31/12/2013, tu obtiendras 11 (12 est la différence entre 01/01/2013 et 01/01/2014).]

Matiti Bonsoir,

En premier lieu tu nommes ta cellule E2 en An-Ref dans la zone de nom, zone de nom = zone où s'affiche les coordonnées de la cellule active

Puis pour résoudre ton Pb il faut faire 3 Si:

• le 1er test: l'année de Référence est comprise entre Année(Date Arrivée) ET Année(Date Départ) => SI(ET(Annee(date arrivée)<An_Ref;Annee(date départ) >AN_Ref) alors résultat =12
• le 2éme test: l'année de Référence est l'Année(Date Départ) => SI(Annee(date départ)=Annee(An_Ref) alors résultat= Mois(date départ)
• le 3éme test: l'année de Référence est l'Année(Date Arrivée) => SI(Annee(date arrivée)= Annee(An_Ref) alors résultat= Mois(date arrivée)
• Enfin si aucune condition n'est vraie cela signifie que la personne est déjà partie alors inscrire "" dans la cellule ("" = cellule blanche mais non vide)

Donc en G7 inscrire la formule : =SI(ET(ANNEE(C7)<An_Ref;ANNEE(D7)>An_Ref);12;SI(ANNEE(D7)=An_Ref;MOIS(D7);SI(ANNEE(C7)=An_Ref;MOIS(C7);"")))

Gestion Erreur en H7: pour ne pas avoir d'erreur écrire: =SI(SOMME(G7)=0;"";G7/12)

Puis il suffit de recopier les 2 formules.

Pour exemple tu peux modifier la cellule D7 et y inscrire 1/10/2012 alors les cellules C7 et H7 seront en blanc

Les cellules F5 à F9 ne servent à rien et peuvent être supprimées ainsi que la colonne"nb mois total"

J'espère que cela répond à ton Pb - Cordialement - Xavier

ferrand a écrit :

Bonsoir,

Je suppose que tu as voulu écrire : =DATEDIF(SI(C7<$F$2;$F$2;C7);SI(D7>$F$3;$F$3;D7);"d")/30

Ce qui va te donner un nombre supérieur à 12 (pour les 2 premiers cas) même si tu n'affiches pas les décimales et donc un % supérieur à 100%

Tes calculs successifs et tes formatages de cellules ne permettent pas d'avoir de certitudes sur la finalité de tes calculs.

Je ne suis pas sûr que DATEDIF soit la fonction la mieux adaptée à ce que tu recherches...

Tu dis au départ que tu calcules en mois : s'agit-il de mois calendaires, de mois évalués de date à date, de mois ramenés à 30 jours (sur une année de 360), ...?

Considère-t-on uniquement des mois entiers ? Ou est-ce qu'on prend en compte des fractions de mois ?

[NB- Si tu utilises DATEDIF pour calculer une différence en mois entre le 01/01/2013 et le 31/12/2013, tu obtiendras 11 (12 est la différence entre 01/01/2013 et 01/01/2014).]

Bonjour Ferrand

Datedif provoque des erreurs c'est une fonction qui beug

voir ici http://www.excel-downloads.com/forum/207853-date-dif.html#post1363948

Salutations

Gilbert

Merci de ton apport.

Pour l'instant je me suis contenté de constituer la formule que Matiti cherchait visiblement à construire, avec utilisation de SI (et sans ET, inutile dans son cas) pour retenir sous condition une ou une autre date de départ et une ou une autre date de fin. Je n'ai repris DATEDIF que parce que c'était la fonction utilisée...

Une fonction personnalisée peut éventuellement être une solution... Pour ma part, avant d'avancer autre chose, j'attends de savoir quelle méthode d'évaluation elle est tenue d'utiliser. Ce n'est pas très clair, et la façon dont les calculs sont engagés dans le classeur conduit à des résultats un peu trop "grossiers" pour me sembler compatible avec une évaluation sérieuse de ressources humaines utilisées.

A+

c'était juste une info au sujet de DATEDIF qui est vendu malheureusement avec excel

gilbert_rgi a écrit :

c'était juste une info au sujet de DATEDIF qui est vendu malheureusement avec excel

Mais cette fonction dont il était dit qu'elle était maintenue pour des raisons de compatibilité avec d'autres tableurs, dans les dernières versions n'est plus de tout documentée...

Merci pour tous vos précieux conseils. j'avoue ne pas avoir vos compétences ! je ne suis pas tenue d'utiliser une méthode plus qu'une autre. Je souhaite pouvoir mettre en place un listing des agents (selon leur statut, services etc..) qui arrivent et partent sur un même site et faire tous les ans, des calculs via un tableau croisée dynamique, concernant le nb d'ETP (equivalent temps plein) sur une année de référence. l'idée est de conserver au fil des années toujours le même fichier, et de n'avoir qu'à changer/ajouter une nouvelle année de référence, en fonction des infos que l'on me demandera de remonter en central.

je dois être capable de dresser un tableau qui donne les infos suivantes :

X ETP ont été présents sur le site au cours de l'année de ref 2013 (par exemple) en faisant ensuite des tris selon les statuts, et autres critères etc....

On m'a déjà dit que datedif beug parfois, mais je n'ai malheureusement aucune connaissances en VB ! donc je ne sais pas comment intégrer tous les elements de gilbert.

Les calculs devraient dans l'absolu être faits sur des mois de date à date.

Je suis partie initialement d'un calcul en mois, pour pouvoir calculer par la suite des ETP 'equivalent temps plein" sur une année de référence et parce que je me servais de la condition à savoir si le nb de mois est > à 12, alors, on calcul la différence entre la date d'arrivée et le début de l'année de reference etc.... Ce n'est apparemment pas la bonne méthode.

merci pour votre aide.

matiti

Bonsoir,

D'abord, problème général d'intervalle entre 2 dates : date_début...date_fin, date_début étant le premier jour de service considéré et date_fin le dernier jour de service considéré (soit les bornes sont incluses dans la durée de service), il faut faire : (date-fin+1) - date_début pour avoir la durée de service (en jours).
31/12/2013 -01/01/2013 = 364 ; c'est pourquoi =DATEDIF(01/01/2013;31/12/2013;"m" ou "d") va renvoyer 11 (avec "m", le nombre de mois entiers entre les dates) et 364 (avec "d", le nombre de jours)...

Je conseillerais donc d'appliquer directement en colonne H :

=FRACTION.ANNEE(SI(C7<$F$2;$F$2;C7);SI(D7>$F$3;$F$3;D7)+1;1)

qui va te renvoyer directement des ETC (dans ton exemple : 1,00 pour les 2 premiers cas et 0,50 pour le dernier).

Cette fonction donne 1 pour une année complète. Le dernier argument (1) indique que le calcul est fait en nombre de jours réels. Tu peux le remplacer par 4 pour faire le calcul en comptabilisant des mois alignés à 30 jours pour une année de 360 jours (selon la norme européenne).

Pour l'évaluation du nombre de mois :

En colonne E : =FRACTION.ANNEE(C7;D7+1;4)*12

En colonne G : =FRACTION.ANNEE(SI(C7<$F$2;$F$2;C7);SI(D7>$F$3;$F$3;D7)+1;4)*12

http://cjoint.com/?DBfxkjVJOr5

merci bcp ! je vais reviser mon fichier avec tes conseils.

Matiti

Bonjour Ferrand,

Je pense que si une personne est déjà partie (par exemple Pierre date départ = 1/10/2012) tu as une erreur car tu ne testes que dans l'intervalle C7<$F$2 (F2 = "Date début année" ici 1/1/2013). Dans ce cas Pierre est bien dans cet intervalle et donc le calcul est fait et l'on trouve 2.249 comme résultat alors que 0 est attendu. Pour éviter d'écrire 0 je préfère inscrire dans la cellule "" cela est plus sexy !!!

c'est pour cela qu'il faut tester sur trois intervalles :

• soit la personne est hors année indiquée en E2 que j'ai nommé An_Ref: ex: déjà partie donc "date départ" < "Année référence" ou "date début année"
• soit la personne est hors année indiquée en E2 que j'ai nommé An_Ref: ex: arrivée "après fin année" donc "Date arrivée" > "Année référence" ou"date fin année"
• soit la personne est dans l'"Année référence" ou dans l'intervalle "date début année";"date fin année"

Je préfère utilisé des zones nommées plutôt que des affichages absolus avec $ car la maintenance des formules est plus facile: en effet une zone nommée est atteignable de n'importe où dans le fichier Excel et l'écriture de la formule sans trouve facilité. La cellule nommée "An_Ref" ou tout autre cellule nommée est atteignable de n'importe où par son nom alors que $E$2 se complique si l'on met cette cellule dans une autre feuille feuil2!$E$2. Cette disposition permet de n'avoir qu'une seule feuille contenant toutes les variables de tous les tableaux.

Ceci n'est que ma réflexion, il faut que Matiti prenne la solution qu'elle sente le mieux, il n'y a pas dans le domaine de vérité absolu.

PS: dans mon message précédent mes formules sont copiables in extenso de G7/H7 à G9/H9

Bonne soiré - Cordialement - Xavier

Merci Ximi pour ces précisions.... je vais reprendre tt ca a tete reposée ce week end.

Matiti

ximi a écrit :

Bonjour Ferrand,

Je pense que si une personne est déjà partie (par exemple Pierre date départ = 1/10/2012) tu as une erreur car tu ne testes que dans l'intervalle C7<$F$2 (F2 = "Date début année" ici 1/1/2013). Dans ce cas Pierre est bien dans cet intervalle et donc le calcul est fait et l'on trouve 2.249 comme résultat alors que 0 est attendu.

En effet ... Formules révisées :

En H : =SI(ET($F$2<=D7;$F$3>=C7);FRACTION.ANNEE(SI(C7<$F$2;$F$2;C7);SI(D7>$F$3;$F$3;D7)+1;1);"")

En G : =SI(ET($F$2<=D7;$F$3>=C7);FRACTION.ANNEE(SI(C7<$F$2;$F$2;C7);SI(D7>$F$3;$F$3;D7)+1;4)*12;"")

http://cjoint.com/?DBhkpxUhWvL

Tu peux aussi comme on l'avait évoqué au départ utiliser une fonction personnalisée. Bien sûr il faut alors la contruire. Voici une proposition ci-dessous pour répondre à tous tes calculs avec la même fonction.

Je l'ai nommée SuiviEffMois. Une fonction personnalisée est une macro qui se place dans un module standard. L'onglet Développeur doit être activé dans ton application Excel, il ne l'est pas par défaut, tu l'actives dans les Options : Fichier > Options > onglet Personnaliser le ruban. Tu ouvres l'éditeur VBA en cliquant sur 'Visual Basic' dans l'onglet Développeur (ou par la combinaison de touches Alt+F11). Dans l'éditeur, menu Insertion > Module te permet d'ajouter un module standard, feuille blanche destinée à accueillir du texte constituant un code exécutable par l'interpréteur de commandes VBA.

Sur ce module, tu tapes la fonction (ou mieux, tu la copies et colles). Elle s'utilise ensuite comme les fonctions intégrées d'Excel dans ta feuille de calcul.

La fonction SuiviEffMois comporte 4 arguments : les 2 premiers sont obligatoires, il s'agit de ta date d'arrivée (début du service) et de ta date de départ (fin du service incluse dans la durée du service). Les 2 autres sont optionnels, tu les omets si tu n'en as pas besoin : le 3e est l'année de référence à indiquer si tu veux que le calcul soit fait sur cette année, le dernier te permet d'obtenir le résultat en ETP en mettant 1. Bien entendu, pour les valeurs date arrivée, date départ ou année référence fournies en arguments à la fonction tu peux indiquer les références de cellules contenant ces valeurs comme dans toute autre formule.

Function SuiviEffMois(d1 As Date, d2 As Date, Optional aref As Integer = 0, Optional etp As Integer = 0)
Dim a%, m%, j
Application.Volatile
If aref <> 0 Then
If aref < 1900 Then
SuiviEffMois = "Non défini"
Exit Function
End If
If DateSerial(aref, 1, 1) > d2 Or DateSerial(aref, 12, 31) < d1 Then
SuiviEffMois = ""
Exit Function
Else
If DateSerial(aref, 1, 1) > d1 Then d1 = DateSerial(aref, 1, 1)
If DateSerial(aref, 12, 31) < d2 Then d2 = DateSerial(aref, 12, 31)
End If
End If
a = (Year(d2 + 1) - Year(d1)) * 12
m = Month(d2 + 1) - Month(d1)
j = (IIf(Day(d2 + 1) > 30, 30, Day(d2 + 1)) - IIf(Day(d1) > 30, 30, Day(d1))) / 30
If etp = 0 Then
SuiviEffMois = a + m + j
Else
SuiviEffMois = (a + m + j) / 12
End If
End Function

Ton classeur devra être enregistré comme classeur contenant des macros (et prendra l'extension .xlsm). A la première ouverture (au moins) tu devras activer les macros pour que la fonction soit active.

Fichier joint : http://cjoint.com/?DBjsjWZ872M

Super !! merci bcp Ferrand !! je viens d'integrer ta macro dans mon fichier réel et tt fonctionne super bien !

Suis pas certaine d'avoir tt pigé mais je realise qu'une petite formation sur les fonctions avancees d'excel me fera le plus grand bien.

Bonne continuation

Matiti

Si tu as des questions, n'hésite pas à les poser.

A+

Bonjour à tous et juste une petite réflexion sur les macros.

En réalité les programmeurs en VBA se font souvent plaisir avec des macros alors qu'une utilisation judicieuse des fonctions d'Excel peut simplement résoudre le problème et être rapidement mis en œuvre.

C'est vrai qu'il y a des cas ou le passage par une macro devient nécessaire, par exemple si vous avez une succession de tableau ou de graphiques à imprimer régulièrement: faire une macro qui va appeler via un bouton les différents tableaux par leurs noms, les définir comme zone d'impression puis les visualiser/imprimer (3 lignes par tableaux) et qui imprime automatiquement ....)

Pour avoir pas mal utilisé VBA je trouve, surtout pour un débutant, qu'il n'est pas facile de réaliser simplement un programme (ou un macro); la mise au point peut se montrer laborieuse suite à des erreurs de syntaxe (";" ou "," ou ":" ou ...) et à de mauvaises utilisations des fonctions (Ex: Cell() ou Cells()). De plus il faut apprendre le nom de pas mal de fonctions (mots réservés) ainsi que leurs constructions. Beaucoup de travail d'apprentissage et d'exercices pour maitriser la bête.

Je pense qu'il faut plutôt privilégier les fonctions intégrées d'Excel à écrire dans les cellules. Souvent plus simple et plus rapide à mettre en œuvre. La seule difficulté est de savoir ce que l'on veut tester et comment le tester. Après avoir résolu ce que l'on veut faire dans la fonction le reste devient facile.

Si en plus pour écrire ces fonctions on utilise le Fx à coté de la zone du contenu de la cellule, que l'on sache lire et que l'on remplisse les bonnes cases on est sur de ne pas faire d'erreur de syntaxe sachant que c'est le remplissage des cases qui "écrit" la fonction.

Dans Excel il y a quand même plus de 250 fonctions dans tous les domaines et l'on doit pouvoir trouver sont bonheur.

Même pour faire des SI() imbriqués, jusqu'à 14 SI(), nous n'avons qu'à écrire les "conditions de test" et les résultats pour test "vrai ou faux". L'écriture de la formule se réalise toute seule avec mise en place automatique des "", (, ) et autres syntaxe. Pour compléter la formule ou peut aussi utiliser des fonctions dans d'autres fonctions c'est dailleurs ce qu'à fait Ferrand ou moi même ci dessus.

Cela n'était juste qu'une réflexion.

Cordialement - Xavier