Nombre de jours dans un mois spécifique entre deux dates

Bonjour à tous !

Je m'en remets à ce super forum, car je n'arrive vraiment pas à trouver de solution de mon côté.

Je dois calculer un "budget" de personnel salarié en équivalent temps plein (ETP) à l'année, ETP devant être répartis au mois le mois.

Pour ce faire, j'ai un classeur avec deux feuilles (cf. PJ) :

• Data : pour saisir les dates, avec un calcul automatique de la durée en mois et jours (col. C), et en ETP (col. D)

• Budget : pour indiquer combien d'ETP sont alloués chaque mois, sur la base de la colonne D de la feuille Data

Mon problème est que, dans la feuille Budget, je ne parviens pas à ventiler les ETP pour chaque mois dans la colonne B.

Merci d'avance pour vos idées ingénieuses ! Je vous ai mis quelques exemples dans la feuille Data

Spigawino,

Pour être sûr d'avoir bien compris le problème, peux-tu également indiquer ce que tu voudrais obtenir dans "Budget":

01/2019 :

02/2019 :

03/2019 :

04/2019 :

05/2019 :

bonjour

a bien verifier manuellement pour confirmer

cordialement

mince ;alors j'ai calculé le nb de mois

Bonjour,

Une autre proposition.

Avec des résultats un peu différents !...

A te relire.

Cdlt.

Bonjour à tous

Ma contribution !

edit : fichier supprimé, voir en fin de discussion

Merci à tous pour vos contributions, c'est ultra rapide comme d'habitude !

Un peu de feedback par rapport à vos idées :

@tulipe_4, les arrondis ne seront malheureusement pas suffisants, je ne l'ai pas précisé mais j'ai besoin d'un calcul en mois décimaux.

@Jean-Eric, l'idée de Power Query est tentante mais tout le monde ne saura hélas pas s'en servir, et c'est une matrice qui devra être utile à environ 70 personnes et c'est pourquoi je veux la rendre la plus simple possible !

@Steelson, c'est très proche voire exactement ce que je recherchais, avec les décimales, c'est génial

Mais je me suis rendu compte en essayant vos solutions que j'avais des erreurs dans ma propre formule ! Par exemple, si je saisis une date après le 25 dans un mois, il ne me fait plus de décompte décimal. Ma formule est la suivante : =SI(ESTERREUR(DATEDIF([@[Date début (SAISIE)]];[@[Date fin (SAISIE)]];"m")+DATEDIF(DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1);[@[Date fin (SAISIE)]];"d")/JOURS(FIN.MOIS([@[Date fin (SAISIE)]];0);DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1)));"";DATEDIF([@[Date début (SAISIE)]];[@[Date fin (SAISIE)]];"m")+(DATEDIF(DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1);[@[Date fin (SAISIE)]];"d")+1)/(JOURS(FIN.MOIS([@[Date fin (SAISIE)]];0);DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1)-1)))

Quelqu'un aura peut-être encore une idée brillante ?

Bonjour,

Pour la solution Power Query, il suffit d'ajouter des données dans le tableau, puis dans le Ruban, Données, Actualiser tout…

la requête est alors mise à jour.

Cdlt.

Jean-Eric a écrit :

Bonjour,

Pour la solution Power Query, il suffit d'ajouter des données dans le tableau, puis dans le Ruban, Données, Actualiser tout…

la requête est alors mise à jour.

Cdlt.

Entièrement d'accord ! Mais rien que la logique d'actualisation n'est pas acquise pour tout le monde, il y a un vrai déficit de formation dans mon entreprise sur Excel. C'est bien dommage...

Merci en tout cas pour ta solution qui répondait aussi à ma problématique

[quote=Spigawino post_id=784800 time=1564464781 user_id=24440]

Merci à tous pour vos contributions, c'est ultra rapide comme d'habitude !

Un peu de feedback par rapport à vos idées :

@tulipe_4, les arrondis ne seront malheureusement pas suffisants, je ne l'ai pas précisé mais j'ai besoin d'un calcul en mois décimaux.

@Jean-Eric, l'idée de Power Query est tentante mais tout le monde ne saura hélas pas s'en servir, et c'est une matrice qui devra être utile à environ 70 personnes et c'est pourquoi je veux la rendre la plus simple possible !

@Steelson, c'est très proche voire exactement ce que je recherchais, avec les décimales, c'est génial

Mais je me suis rendu compte en essayant vos solutions que j'avais des erreurs dans ma propre formule ! Par exemple, si je saisis une date après le 25 dans un mois, il ne me fait plus de décompte décimal. Ma formule est la suivante : =SI(ESTERREUR(DATEDIF([@[Date début (SAISIE)]];[@[Date fin (SAISIE)]];"m")+DATEDIF(DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1);[@[Date fin (SAISIE)]];"d")/JOURS(FIN.MOIS([@[Date fin (SAISIE)]];0);DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1)));"";DATEDIF([@[Date début (SAISIE)]];[@[Date fin (SAISIE)]];"m")+(DATEDIF(DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1);[@[Date fin (SAISIE)]];"d")+1)/(JOURS(FIN.MOIS([@[Date fin (SAISIE)]];0);DATE(ANNEE([@[Date fin (SAISIE)]]);MOIS([@[Date fin (SAISIE)]]);1)-1)))

Quelqu'un aura peut-être encore une idée brillante ?

[/quote]

Je me rends compte que l'usage de DATEDIF n'est pas forcément le meilleur choix, j'essaye avec FRACTION.ANNEE...

Spigawino a écrit :

Mais je me suis rendu compte en essayant vos solutions que j'avais des erreurs dans ma propre formule ! Par exemple, si je saisis une date après le 25 dans un mois, il ne me fait plus de décompte décimal.

Quelqu'un aura peut-être encore une idée brillante ?

Spigawino a écrit :

Je me rends compte que l'usage de DATEDIF n'est pas forcément le meilleur choix, j'essaye avec FRACTION.ANNEE...

J'avoue ne pas avoir bien saisi ton problème ... peux-tu expliciter ce que tu souhaites (notamment avec le 25 du mois) ?

Steelson a écrit :

Spigawino a écrit :

Mais je me suis rendu compte en essayant vos solutions que j'avais des erreurs dans ma propre formule ! Par exemple, si je saisis une date après le 25 dans un mois, il ne me fait plus de décompte décimal.

Quelqu'un aura peut-être encore une idée brillante ?

Spigawino a écrit :

Je me rends compte que l'usage de DATEDIF n'est pas forcément le meilleur choix, j'essaye avec FRACTION.ANNEE...

J'avoue ne pas avoir bien saisi ton problème ... peux-tu expliciter ce que tu souhaites (notamment avec le 25 du mois) ?

Et bien je te donne un exemple simple : en utilisant ma formule DATEDIF, j'arrive au résultat suivant :

Date de début : 27/08/2019

Date de fin : 31/08/2019

ETP : 1,00

Alors que je devrais être aux alentours de 0,16 (ce qui correspond au résultat de ta formule de calcul du prorata de mois d'entrée). Ma formule est donc fausse !

Bonjour,

Une petite mise à jour !...

Petit update : vos idées m'en ont donné une autre !

Je viens de me rappeler que mon budget est évalué en annuel, donc je vais utiliser la formule FRACTION.ANNEE pour lisser l'effet du nombre de jours par mois.

Je vous envoie une nouvelle version de mon fichier d'origine, et mon objectif est de faire en sorte que les chiffres dans la colonne D soient ventilés par mois dans la colonne G, et que le total en D8 soit égal au total en G14.

J'ai essayé en combinant SOMMEPROD et FRACTION.ANNEE, mais pas de résultats concluants...

Merci encore énormément pour votre aide à tous !!

Spigawino a écrit :

Je viens de me rappeler que mon budget est évalué en annuel, donc je vais utiliser la formule FRACTION.ANNEE pour lisser l'effet du nombre de jours par mois.

Dans ce cas ...

edit : fichier supprimé, voir en fin de discussion

a priori la formule FRACTION.ANNEE ne fonctionne pas comme je voudrais ... donc nouvelle version

edit : fichier supprimé, voir en fin de discussion

Steelson a écrit :

Spigawino a écrit :

Je viens de me rappeler que mon budget est évalué en annuel, donc je vais utiliser la formule FRACTION.ANNEE pour lisser l'effet du nombre de jours par mois.

Dans ce cas ...

Merci Steelson, je pense que je n'ai pas été clair, désolé Quand je parlais de lisser l'effet des mois sur l'année, ça veut dire que même si - par exemple - une période dure du 01/01 au 31/01, ça pourra dépasser 1 avec l'utilisation de FRACTION.ANNEE, je suis plus clair ?

Pour simplifier, je cherche à faire en sorte que quelles que soient les formules utilisées, le total ETP soit le même dans les deux tableaux. L'idéal en utilisant la formule FRACTION.ANNEE

Merci encore de prendre du temps pour m'aider !

Steelson a écrit :

a priori la formule FRACTION.ANNEE ne fonctionne pas comme je voudrais ... donc nouvelle version

Je comprends mieux ce que tu as voulu faire. Quand je disais que le budget est évalué à l'année, je voulais dire que je ne suis pas sur un réel par mois (ex : 31 jours en janvier, 28 en février) mais plutôt une pondération pour faire en sorte qu'un salarié qui sera présent du 01/01 au 31/12 sera valorisé à hauteur de 12 ETP.

Ce qui donne ça avec la formule =FRACTION.ANNEE([@[Début mois]];[@[Fin mois]]+1;1)*12

Re,

Fraction.Année ?

Spigawino a écrit :

Quand je disais que le budget est évalué à l'année, je voulais dire que je ne suis pas sur un réel par mois (ex : 31 jours en janvier, 28 en février) mais plutôt une pondération pour faire en sorte qu'un salarié qui sera présent du 01/01 au 31/12 sera valorisé à hauteur de 12 ETP.

J'obtiens donc ceci ...

edit : fichier supprimé, voir en fin de discussion

Steelson a écrit :

Spigawino a écrit :

Quand je disais que le budget est évalué à l'année, je voulais dire que je ne suis pas sur un réel par mois (ex : 31 jours en janvier, 28 en février) mais plutôt une pondération pour faire en sorte qu'un salarié qui sera présent du 01/01 au 31/12 sera valorisé à hauteur de 12 ETP.

ok

J'obtiens donc ceci ...

Merci, je pense que je vais me baser là-dessus, c'est ce que je recherchais ! La solution est élégante et propre, je suis ravi je passe le thread en validé si tout rentre bien dans les cases

@Jean-Eric, merci pour ton aide, j'ai demandé à ma responsable pour le Power Query mais c'est un non catégorique et c'est bien dommage...

Merci à tous pour votre aide !