Discussion :

[lena66]

Bonjour,

J'ai grandement besoin de votre aide car je me trouve à l’arrêt sur la conception d'un tableau Excel. Voici de quoi il retourne : je suis en train de créer un tableau de reversement de ressources d'une personne au département. J'ai donc, dans une feuille 1, une case A1 où je renseigne la date d'entrée en maison de retraite et une case A2 où je renseigne la date de sortie (si la personne est sortie). J'ai ensuite une feuille par année : F2 pour 2013, F3 pour 2014, etc... Dans chacune de ces feuilles figure mon tableau avec les revenus de la personne mois par mois. Ligne 1 : janvier 2016, ligne 2 : février 2016, etc...
Je souhaite que pour chaque ligne / chaque mois, Excel m'indique dans une case donnée le nombre de jours que la personne a passé en maison de retraite.
Exemple pour être plus claire : monsieur Y est entré le 23/02/2013 et est sorti le 25/07/2016, je souhaite que pour chaque feuille (années 2013, 2014, 2015 et 2016) et pour chaque ligne (janvier, février, mars, etc...), Excel m'indique le nombre de jours passés en maison de retraite, soit :
Feuille 1 : 2013 : en janvier = 0, en février = 23, en mars =31, etc... jusqu'en décembre 2016 où le chiffre sera bien sûr de 0.
Quelqu'un peut-il m'aider à trouver la bonne formule ?
Un grand merci à tous pour votre aide !!!

[riaolle]

Bonjour et bienvenue sur le forum
Il faut faire un petit exercice de logique au niveau des dates

• Si date d'entrée > dernier jour du mois de l'année ou date de sortie < 1er jour du mois de l'année, alors la période na pas d'intersection avec le mois de l'année en question --> 0
• Sinon,
  
  • Si date d'entrée <= 1er jour du mois de l'année ET date de sortie >= dernier jour du mois de l'année, alors tous le mois est dans la période --> nombre de jours du mois = JOUR(DATE(C$4;$A5+1;1)-1)
  • Sinon,
    
    • Si date d'entrée <= 1er jour du mois de l'année ET date de sortie < dernier jour du mois de l'année --> date de sortie - 1er jour du mois
    • Sinon,
      
      • Si date d'entrée > 1er jour du mois de l'année ET date de sortie >= dernier jour du mois de l'année --> dernier jour du mois - date d'entrée
      • Sinon date de sortie - date d'entrée

En C5 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(OU($B$1>DATE(C$4;$A5+1;1)-1;$B$2<DATE(C$4;$A5;1));0;SI(ET($B$1<=DATE(C$4;$A5;1);$B$2>=DATE(C$4;$A5+1;1)-1);JOUR(DATE(C$4;$A5+1;1)-1);SI(ET($B$1<=DATE(C$4;$A5;1);$B$2<DATE(C$4;$A5+1;1)-1);JOUR($B$2);SI(ET($B$1>DATE(C$4;$A5;1);$B$2>=DATE(C$4;$A5+1;1)-1);DATE(C$4;$A5+1;1)-1-$B$1;$B$2-$B$1))))

Puis la formule a été tirée. A adapter si besoin de le faire sur plusieurs feuilles.

[jerome.vaussenat]

Salut,

Une variante au cas où.

Au niveau du positionnement dans le tableau, je suis parti comme riaolle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(MIN($A$2;DATE(C$4;$A5+1;1)-1)-MAX($A$1;DATE(C$4;$A5;1))<0;0;MIN($A$2;DATE(C$4;$A5+1;1)-1)-MAX($A$1;DATE(C$4;$A5;1)))

Pour explication :
cette formule renvoie un résultat négatif quand on est hors période :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MIN($A$2;DATE(C$4;$A5+1;1)-1)-MAX($A$1;DATE(C$4;$A5;1))

En espérant que cela puisse t'aider.

[lena66]

Bonsoir,

Merci beaucoup à tous les deux !!!
Je suis en train d'adapter vos formules à mon tableau. Un petit détail : si je ne rentre pas de date de sortie car la personne est encore présente à la maison de retraite, la formule me renvoie 0 au lieu du nombre de jours dans le mois. Avez-vous une solution pour cela ?
Merci encore à tous les deux pour votre aide !!!

[lena66]

re bonsoir,

autre petit souci : si la personne entre le 10 avril et sort le 15, par exemple, la formule compte 5 jours de présence au lieu de 6 :-(
Il doit y avoir un + 1 à mettre quelque part
merci de votre aide !!!

[riaolle]

Bonjour,

Il doit y avoir un + 1 à mettre quelque part

En effet, dans mon exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(OU($B$1>DATE(C$4;$A5+1;1)-1;$B$2<DATE(C$4;$A5;1));0;SI(ET($B$1<=DATE(C$4;$A5;1);$B$2>=DATE(C$4;$A5+1;1)-1);JOUR(DATE(C$4;$A5+1;1)-1);SI(ET($B$1<=DATE(C$4;$A5;1);$B$2<DATE(C$4;$A5+1;1)-1);JOUR($B$2);SI(ET($B$1>DATE(C$4;$A5;1);$B$2>=DATE(C$4;$A5+1;1)-1);DATE(C$4;$A5+1;1)-$B$1;$B$2-$B$1+1))))

Il y avait aussi un -1 en trop (première partie en rouge).

Un petit détail : si je ne rentre pas de date de sortie car la personne est encore présente à la maison de retraite, la formule me renvoie 0 au lieu du nombre de jours dans le mois. Avez-vous une solution pour cela ?

Il y a très sûrement moyen de compléter la formule pour prendre cela en compte, mais ça va encore plus l'alourdir ! Ce que je te propose c'est de passer par une cellule intermediaire:

	A	B	C
1	date d'entrée	01/05/2016	=B1
2	date de sortie		=SI(ESTVIDE(B2); AUJOURDHUI();B2)

Et utiliser les dates en colonne C pour la formule.

[papouclo]

Bonjour le forum,
Jérôme ta formule peut se raccourcir en :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MAX( 0 ; MIN($A$2;DATE(C$4;$A5+1;1)-1) - MAX($A$1;DATE(C$4;$A5;1))

La structure de formule MAX(0; calcul) élimine les résultats négatifs du calcul en évitant d'avoir à le réécrire pour le tester.
Lena66, la formule de jérome (modifiée si tu le veux comme je le propose) règle la question de date de sortie non entrée puisque la fonction MIN ne prend pas en compte les cellules vides à l'inverse des opérateurs arithmétiques qui attribuent à une cellule vide la valeur 0.

riaolle t'a tout présenté sur une seule feuille pour pouvoir faire une vue d'écran.
Je joins un classeur qui tente de coller le mieux possible à la description que tu donnais du classeur dans ton premier message (une feuille par année et les mois en colonne A).
En fait, les cellules de la colonne A contiennent non pas le nom du mois suivi de l'année comme tu le demandais mais la date du début du mois ce qui permet de raccourcir la formule de calcul de la durée de présence qui utilise cette date.
La présentation désirée s'obtient à l'aide du format personnalisé mmmm aaaa. Une date est calculée aussi en A13 donnant le 1er janvier de l'année suivante mais elle n’apparaît pas grâce au format personnalisé "" (on pourrait aussi la cacher avec un format normal mais en demandant la couleur du fond de la cellule pour les caractères).

Cordialement
Claude

[jerome.vaussenat]

Salut,

papouclo, t'es trop fort. Je me doutais qu'il y avait une formulation plus simple mais mon petit cerveau n'a pas sû trouver cette solution ...

[lena66]

Cher Riallo, Jérôme et Claude,

Un immense merci pour tout ce que vous avez fait. Vous venez de me sortir d'une grosse galère !!! J'étais incapable de m'en sortir toute seule... je suis nulle en math et en informatique, mais si je peux vous aider dans tout ce qui est paperasse et juridique (en gros tout le boulot d'une assistante sociale...), n'hésitez pas cellth66@gmail.com
Excellent week-end à vous tous !!!