Formule Excel pour déterminer une date de sortie

Question

Après avoir fait des recherches un peu partout je reste bloquée et je sollicite donc votre aide svp. Pour le suivi de stagiaires je dois déterminer leur date de sortie. Dans une colonne j'ai la date de démarrage, dans la colonne suivante le nombre d'heure à réaliser et dans la colonne suivante la fréquence hebdomadaire en heure. Par exemple date de démarrage le 10/09/2016, nombre d'heure à réaliser 120 heures, fréquence hebdomadaire 6 heures (soit 2 séances car chaque séance dure 3 heures) ainsi dans la colonne suivante je souhaiterai savoir si une formule existe qui calculerai directement la date de sortie. Sur une autre feuille j'ai les dates des jours fériés et congés. L'objectif étant de déduire ces jours fériés dans le calcul pour avoir une date de sortie juste. Je suis un peu perdu donc si vous pouvez m'aider, merci beaucoup à tous...

andrea73 · Answer

Il mes semble que... cela doit s'approcher du résultat mais je suis très mauvais en math et je n'ai pas eu la patience de compter sur mon calendrier PS/ pour les besoins de la formule =NB.JOURS.OUVRES j'ai modifié un peu ton "calculateur" de jours fériés, je t'expliquerais si la date est bonne, ou même si elle ne l'est pas et que le souhaite...

andrea73 · Answer

Il me semblait qu'il me restait quelques lacunes en math je te l'avais dis Je vais regarder ta "nouvelle version avec planning" et je te dirais si ma bosse des maths me fait mal ...

MFerrand · Answer

L'ouverture de ton fichier fait apparaître des références circulaires ! Il semble que cela provienne d'un élément perturbateur dans la formule de la date de Pâques... Ayant une préférence pour les formules pérennes en matière de dates, j'apprécie que tu n'utilises pas de formule simplifiée ayant une durée de validité limitée dans le temps pour calculer Pâques. Cependant au cas particulier de Pâques tous les algorithmes de calculs élaborés depuis quelques siècles et validés donnent lieu à des formules ultra-longues, pas vraiment pratiques : l'utilisation d'une fonction personnalisée règlerait plus aisément ce problème... Je n'ai d'ailleurs pas cherché à trouver la source de ta référence circulaire, car cela était vraiment trop fastidieux ! La question de ce calcul devra toutefois être résolue. La plage nommée "an", une ligne entière, n'est pas une pratique très optimale. D'autant que là le nom ne fait pas gagner grand chose par rapport à se référer à la première cellule de la colonne... Une plage nommée Fériés (utilisable dans les formules) manque par contre ! La question se pose à cet égard de sa délimitation : plage globale couvrant les 22 années de ton tableau ? (si on peut l'éviter ce serait sûrement mieux !), plage évolutive de l'année de départ et couvrant 2 ou 3 ou plus (?) années, tout dépend à cet égard de l'extension maximale des formations dans le temps (à préciser). Tes données initiales : la date de départ : ce sera le premier jour de formation si elle tombe un jour de formation, pour que ce jour soit bien comptabilisé le cas échéant, la date de départ à prendre en compte dans les formules d'évaluation de la date de fin devra être cette date -1 ; le nombre d'heures contractuelles de formation ; la durée d'une séance journalière de formation ; les jours de la semaine durant lesquels une séance de formation aura lieu. A partir de ces données qui te sont fournies, tu dois définir : - le nombre de séances journalières de formation pour remplir le contrat (et non le nombre de semaines qui ne sert strictement à rien dans le calcul !) : quotient du nombre d'heures contratuel par la durée d'une séance (et si le quotient n'est pas exact, il faut donc l'arrondir à l'unité supérieure pour obtenir un nombre de jours (non fractionables) de formation : Dans ton exemple formule : =ARRONDI.SUP(C9/D9;0) Je laisse de côté la mise sous condition éventuelle pour y voir plus clair... (mais en la matière, il peut toujours être utile de noter qu'une formulation =SI(C9&lt;&gt;"";...;"") est meilleure que =SI(C9="";"";...) car elle privilégie le calcul qui nous intéresse si la condition est vraie, recalcul sensiblement plus rapide sur un grand nombre de formules...) La formule à utiliser pour déterminer la date de fin, sera : =SERIE.JOUR.OUVRE.INTL(DateDébut-1;NbJours;codeJoursNonOuvrés;défFériés) On a la date de début en B9. On a calculé le nombre de jours en E9. Le code indiquant les jours non ouvrés est une chaîne de sept 0 (si ouvré) ou 1 (si non ouvré), définissant le statut de chaque jour de la semaine du lundi au dimanche, soit si les jours ouvrés sont le lundi et le mercredi : 0101111 . Pour les jours fériés, on aura défini une plage nommé Fériés. La formule finale serait donc : =SERIE.JOUR.OUVRE.INTL(B9-1;E9;"0101111";Fériés) En dehors des questions soulevées plus haut (délimitation des fériés), pour améliorer cette formule, il conviendrait que le code jours non ouvrés puisse être calculé de façon à rendre la formule recopiable. Cela est faisable, en modifiant un peu le tableau : éliminer la distinction matin/après-midi, sans intérêt dès lors que l'on ne s'occupe que de jours entiers, concaténer des 0 ou 1 selon que les différents jours sont marqués ouvrés ou non ouvrés dans une cellule de la ligne à laquelle pourrait se référer la formule ci-dessus. On pourrait en outre substituer à la formule de concaténation (qui s'annonce aussi un peu longue) une fonction personnalisée qui simplifierait la méthode... A toi de réfléchir aux modifications à apporter pour te simplifier l'utilisation de ton tableau. Cordialement...