Répartir une valeur entre deux dates et heures

Bonjour à tous

Je joint le modèle de fichier que j'utilise actuellement : celui-ci me permet de repartir une valeur sur plusieurs jours en la divisant par le nombre de jour.

J'aimerai affiner le résultat de cette formule en répartissant ces valeurs etre deux dates et heures. je m'explique :

j'aimerai que la valeur 900 soit réparti entre le 01/02/11 20:00 et le 06/02/11 5:00 par exemple. Je sais qu'il y a 105 heure entre les deux dates. Je fais donc 900/105 ce qui fait 8,571428571428571 par heure. Il devrait donc y avoir 4x8,571428571428571 (les 4h du 01/02), 24x8,571428571428571 (pour chacun des jours entre le 2 et le 5) puis enfin 5x4x8,571428571428571 pour le dernier jour (si tous mes calculs manuels sont bons)

Je suis incapable de le traduire et surtout de l'automatiser simplement sur tout un tableau sous excel via une formule...

Quelqu'un a une idée ?

Dans l'idéal j'aimerai une formule plutot qu'une macro, mais si macro il doit y avoir... soit

Merci d'avance de votre aide !

Bonjour Zozio

donc si j'ai bien compris... tu devrais avoir...

Les valeurs présentées en grisée sont réellement des calculs... je vais tenter de simplifier la formule et j'envoie dès que tu valides !

andrea73 a écrit :

Bonjour Zozio

donc si j'ai bien compris... tu devrais avoir...

Les valeurs présentées en grisée sont réellement des calculs... je vais tenter de simplifier la formule et j'envoie dès que tu valides !

C'est tout à fait ça et je seche pour trouver la formule qui va bien et duplicable à toute les cases.

Bonjour Zozio

Parfait ! Voici donc le fichier

Quelques explications avant :

Voici la formule simplifiée :

=SI(estJourFaux;"";SI(L2C-LC1<0;estJourPremier;SI(L2C(1)>LC2;estJourDernier;SI(L2C(1)="";"";24))))

Simplifiée en utilisant des noms à la place de certaines conditions et simplifiée par qu'elle devient lisible

Le nom estJourDernier contient la formule

=(Feuil1!LC2-Feuil1!L2C)*24

Le nom estJourPremier contient la formule

=(Feuil1!L2C(1)-Feuil1!LC1)*24

Le nom estJourFaux contient la formule (un peu plus compliquée)

=DATE(ANNEE(Feuil1!LC1);MOIS(Feuil1!LC1);JOUR(Feuil1!LC1))>DATE(ANNEE(Feuil1!L2C);MOIS(Feuil1!L2C);JOUR(Feuil1!L2C))

Bonjour,

Je suis pas certains que le fichier fonctionne... En effet lorsque je change la valeur 900 ou la valeur 105 rien ne se passe ni ne se recalcule. Faut-il activer quelque chose avant de l'utiliser ?

Merci beaucoup en tout de te pencher sur mon cas !

En fait je voulais répartir la valeur 900 (qui est un nombre quelconque) sur l'ensemble des jours cité (cf. fichier en PJ) mais ce que tu as fait reviens au même donc je pourrai transposer après à mon cas quand on saura pourquoi la formule ne fonctionne pas chez moi. En attendant ta réponse je vais essayer de recopier tes formules de manière séparées pour voir si ça fonctionne.

EDIT : je viens de voir qu'il faut modifier les dates pour que la formule fonctionne. Du coup ça ne règle pas mon souci (mais du coup ça m'a appris une façon de gérer les dates directement !merci au passage)... Tu comprendras mieux ce que je cherche à faire dans le fichier joint Je me suis mal exprimé je pense...

(re)

j'ai compris l'erreur... je croyais que tu voulais voir figurer des heures...

en réalité il faut donc multiplier ces heures par un ratio (ici dans l'exemple 900/105:00:00)

le problème c'est que 105:00:00 n'est pas réellement 105 mais 4.375 si tu le représente en nombre ! et oui les heures c'est pas simple avec les méthodes de calculs d'Excel - mais rassure toi les autres ne sont pas mieux !

donc comment faire ?

Il faut convertir le fameux 105:00:00 en texte, en extraire les heures, puis le reconvertir en nombre pour pouvoir calculer le ratio

en conséquence il faut ajouter un nom (appelons-le ratio ) dont la formule est

=Feuil1!LC3/CNUM(GAUCHE(TEXTE(Feuil1!LC4;"[hh]:mm:ss");CHERCHE(":";TEXTE(Feuil1!LC4;"[hh]:mm:ss");1)-1))

je préfère créer un nom pcq la formule déjà un peu longue ne fera pas des km de plus à cause de cet ajout (ratio) relativement long

et ensuite dans la formule il faut intégrer ce ratio

=SI(estJourFaux;"";SI(L2C-LC1<0;estJourPremier*ratio;SI(L2C(1)>LC2;estJourDernier*ratio;SI(L2C(1)="";"";24*ratio))))

Salut,

Je viens de tester ça fonctionne Je vais essayer de comprendre le formule complète en la décomposant maintenant.

Par contre je viens de tester sur mon fichier complet et cela m'affiche des nombre négatif lorsque je tire les colonnes pour l'année 2016. voir fichier joint. Une idée ?

En tout cas tu as réussi à faire ce que je n'arrivais pas à faire

Tous le monde me disait qu'il fallait une macro mais j'étais sûr qu'une formule permettait de le faire. Mais j'avoue que j'ai énormément de mal sous excel dés qu'il y a des heures ou des dates...

Bonjour

Voir fichier

Cordialement

Merci Amadeus,

Ton fichier fonctionne mais que dans certains cas apparemment, pour des heures type 15h il affiche des ref. Une erreure de ma part ?

Je vais essayer de décortiquer ta formule pour voir comment elle fonctionne.

Bonjour

L'erreur venait de ce que le résultat en colonne D n'était pas toujours un nombre entier.

c'est corrigé sur ce fichier.

Cordialement

Une fois encore merci Amadeus

Je ne l'ai pas précisé en début de poste mais la finesse dont j'ai besoin doit pouvoir aller à 30 minutes près (inutile pour moi que ce soit à la minute près). Donc quand je met une date de fin par exemple qui se termine à 20h30 et que je fais le total reparti je n'obtiens pas ma valeur mais une valeur en dessous de celle-ci.

Une fois ce problème réglé je pense que je pourrai l'utiliser avec mes autres formules dans mon tableau !

Je joins le fichier pour que ce soit plus clair.

Merci encore de te pencher sur mon cas,

Bonjour

Fichier et formules par 1/2 heure

Cordialement

C'est impecc Amadéus

Je viens de l'intégrer dans mon tableau et ça fonctionne sans bug. Me reste plus qu'à décortiquer ta formule pour essayer de la comprendre car en dehors de arrondi.sup ce sont toutes des formules que je n'ai jamais utilisées jusqu'à présent

Merci d'avoir réglé mon problème en tout cas, je vais pouvoir avancer sur le reste de mon fichier. J'edit le titre en "Resolu".

Rebonjour,

Après avoir tenté de décortiquer ta formule j'avoue que je ne la comprend pas

Est-ce que quelqu'un pourrait me l'expliquer pas à pas ?

=SOMMEPROD((LIGNE(INDIRECT(1&":"&$H2*2))>((J$1*24)-($E2*24))*2)*(LIGNE(INDIRECT(1&":"&$H2*2))<=((K$1*24)-($E2*24))*2))*$G2/$H2/2

(Cf. fichier ci dessus pour l'application réelle)

Merci d'avance à celui qui prendre un peu de son temps pour me l'expliquer ::B

Surtout que j'aimerai la convertir en VBA car j'ai bien trop de ligne et de colonne pour pouvoir tirer cette formule à toute les cellules... du coup il faudrait d'abord que je la comprenne bien.