Problème calcul d'heures
Bonjour mesdames, bonjour messieurs,
je suis chauffeur routier et j'ai crée un tableau Excel afin de calculer mon temps de travail journalier, mon temps de travail hebdomadaire, le nombre d'heures supplémentaires effectuées le cas échéant, le nombre d'heure de nuit effectué ainsi que le nombre d'heure de nuit hebdomadaire.
Je joins mon fichier Excel à ma demande afin que vous puissiez visualiser mon problème plus facilement.
Par exemple de S3 à S7 il y a mes heures de travail journalier et en S8 le total de la semaine, mais je ne sais pas pourquoi le résultat est majoré de 144 heures soit 6 jours. Les heure de travail journalier sont au format heure 13:30 et la somme des heures au format [HH]:mm. Je rencontre d’ailleurs le même problème avec la somme de mes heures de nuit.
Lorsque je rentre mes heures de travail et de pause, c'est à ce moment là que le total des heures augmentent.
J'espère avoir été assez clair, n’hésitez pas à me demander des précisions et merci par avance de l'attention que vous porterez à mon problème.
Cordialement.
Bonjour,
J'ai regardé en bref, mais pour voir où cela coince, passe tes colonnes N O P S au format perso [h:mm] et tu verras le résultat et la ou les corrections à faire
Bonjour,
si tu mets les cellules S3:S7 au format [HH]:mm
tu verra que la cellule S3 = 56:59
Bonsoir,
Tes formules en N et P sont inadéquates...
=SI($K$38<C3<=$M$38;(C3-B3);(C3+(($M$38-B3)+$C$38)))
Ce qui est en rouge est une erreur de formulation, on n'enchaîne pas ainsi des opérateurs de comparaison... l'expression renvoie toujours FAUX. Et ce qui est en bleu majore la durée de 24 heures. Comme pareil pour les 2 cellules : 48 heures de trop !
La bonne formule de calcul des différences horaires (inférieures à 24 heures) :
=MOD(C3-B3;1)C'est simple et sûr, et ça devrait te simplifier toutes tes formules !
Cordialement.
Merci M12 pour ta réponse,
j'ai essayé ta proposition mais Excel me dit "Microsoft Excel ne parvient pas à utiliser le format de nombre que vous avez tapé", donc pas efficace mais merci quand même.
Merci sabV,
effectivement ce que tu dis est vrai mais cela ne règle pas le problème. merci encore de t'y être intéressé.
Merci MFerrand pour ta solution,
elle semble fonctionner avec mon total heures semaine.
Je rencontre le même problème avec mon total heures de nuit (colonne U), dois-je appliquer la même formule ?
Sache que le nombre d'heure de nuit se calcule entre 21h00 et 06h00 et qu'il faut retirer les temps de pause (colonnes O et Q) et déduire les heures non comprises entre 21h00 et 06h00. Merci par avance de vos réponses.
Cordialement, Christophe.
Bonsoir,
Pour les heures de nuit, avec 3 périodes potentielles, pauses non comprises, le mieux est sans doute d'additionner les 3 périodes en les traitant séparément...
=SI(N3>0;MIN(C3+(C3<B3);1+$F$38)-MAX(B3;$L$38);0)+SI(P3>0;MIN(E3+(E3<B3);1+$F$38)-MAX(D3+(D3<B3);$L$38);0)+SI(R3>0;MIN(G3+(G3<B3);1+$F$38)-MAX(F3+(F3<B3);$L$38);0)J'ai cru comprendre que la prise de service n'intervenait pas avant 19h (dans le cas où tu notais les heures), et qu'elle intervenait avant minuit. J'en ai tenu compte, et par contre, j'ai traité les autres horaires pour toutes périodes comme pouvant se situer le soir même ou le lendemain. S'il y a des impossibilités selon les périodes, on pourrait peut-être simplifier la formule...
J'ai regardé aussi les formules de N à R. Pour N3, il me semble que :
=SI(C3<>"";MOD(C3-B3;1);0)La formule est recopiable sur les colonne, de N à R, et sur les lignes suivantes.
Je n'ai modifié que N à R, et U, uniquement sur les lignes 3 à 7.
Sous réserve que je n'ai pas loupé au passage l'un ou l'autre de tes paramètres...
Cordialement.
Merci beaucoup MFerrand,
après application de toutes tes modifications il semble que tous les cas de figure possibles soient couverts. Je vais continuer à tester pour être certain qu'il n'y ait pas de cas conflictuels.
La prise de poste peut intervenir avant 19h00 (minimum 18h30) mais après plusieurs tests il semble que cela fonctionne.
Je sais que j'en demande beaucoup mais pourrais tu m'expliquer la réflexion pour la formule des heures de nuit, comment dois-je la traduire en langage réel. Mon problème vient du fait que je ne connait pas les applications MOD, MIN, MAX et ce qu'elles impliquent. De plus, que représente les 1 et les 0 après les points virgules ? Que veut dire E13<>"" ?
En tout cas, un grand merci à toi pour le temps que tu as pris pour résoudre mon problème, à charge de revanche mais vu ton niveau sur Excel j'ai peu de chance de t'apprendre quelque chose.
Bonsoir,
Rapidement, car je n'ai guère de temps pour m'étendre (hospitalisation demain, et j'ai encore quelques préparatifs à faire...mais on pourra reprendre ensuite...).
La question des heures de nuit est un cas particulier de la recherche de la part de durée horaire comprise dans une plage horaire déterminée. 4 paramètres interviennent donc : l'heure de début et l'heure de fin et les bornes début et de la plage. J'ai une formule générale pour traiter tous les cas de figure qui peuvent se présenter (élaborée en grande partie dans une discussion de forum d'ailleurs, et que j'ai finalisée par la suite, qui est assez longue du fait qu'elle envisage tous les cas sans idée préconçue. Il est évident que si l'on peut d'emblée éliminer un certain nombre de cas, sachant qu'ils ne peuvent intervenir, on peut simplifier le calcul... Dans ton cas par exemple on sait que l'heure de début ne peut intervenir n'importe quand sur une plag de 24h, ce qui limite les possibilités.
Je prends un morceau de la formule pour explication (la formule complète n'est que la justaposition de 3 morceaux semblables).
SI(P3>0;MIN(E3+(E3<B3);1+$F$38)-MAX(D3+(D3<B3);$L$38);0)Il s'agit de la 2e période horaire : P3 est la cellule où l'on a totalisé la durée de cette période. Si P3 = 0 [0:00], il n'y a pas eu de 2e période, donc le résultat sera 0 (le 0 de la fin, soit 0h !)
Si la 2e période existe, on fait heure de fin - heure de début en tenant compte des bornes horaires Nuit.
L'heure de fin est en E3. On doit considérer qu'elle peut se situer avant minuit ou après. On va aligner tous les horaires sur le jour de début de travail, donc ajouter 1 jour si on passe minuit.
(E3<B3) est une expression booléenne, c'est à dire qui renverra VRAI ou FAUX (ce pourquoi des parenthèses). Comme on l'additionne VRAI sera comptabilisé pour 1 et FAUX pour 0.
Si E3 est inférieur à B3, soit l'heure de début de période inférieure à l'heure de début de vacation de travail, c'est qu'on a passé minuit, l'expression vaudra 1, et on ajoute ainsi un jour. Si on est le même jour, l'expression sera fausse et vaudra 0.
F38 c'est 06:00 : là on sait qu'on sera le lendemain, on ajoute donc 1 d'office.
MIN permet de retenir l'heure de fin si elle se situe avant 06:00, et 06:00 si elle se situe après.
(On présuppose qu'on n'aura jamais le cas où l'heure de fin de la 2e période se situerait avant 21:00... sans quoi ces tests seraient insuffisants.)
On opère de même pour l'heure de début : pour y ajouter 1 si elle se situe après minuit.
L38 c'est 21:00 : MAX permet de retenir l'heure de début si elle est postérieure à 21:00, ou 21:00 dans le cas contraire.
(On présuppose aussi de même que l'heure de début de 2e période ne pourait se situer après 06:00... !)
Ce dispositif de correction en fonction des bornes de la plage Nuit ayant fait ajouter 1 aux heures situées au delà de minuit, on n'a que la simple soustraction à faire pour déterminer la part des horaires de la période incluse dans la plage Nuit.
Cordialement.
Merci beaucoup pour tes explications que je vais étudier en profondeur.
j’espère que ton hospitalisation n'est pas grave et que tu seras rapidement de retour en pleine forme.
Mon défunt Papa me disait toujours qu'il vaut mieux un qui sait que dix qui cherchent, et bien tu es celui qui sait.
Merci pour ton investissement.
Cordialement, Christophe.
Rien de grave
J'ai quelques connaissances... mais il y en a bien plus que je n'ai pas
Bonne continuation à toi.
Heureux que tout aille bien pour toi et encore une fois merci, après une semaine de travail il semble que tous les calculs soient bon. D'ici un mois je pense que j'aurais connu tous les cas de figure possibles et si je rencontrais un problème je me permettrai de te le soumettre.
A bientôt peut être pour une autre énigme
Cordialement, Christophe.
et si je rencontrais un problème je me permettrai de te le soumettre.
Ok ! J'ai un petit faible pour les problèmes de dates et heures...
Bonne journée.
Suite à notre discussion en MP, je remets une nouvelle formule, un peu allongée mais plus sécurisée au niveau des conditions...
=SI(N3>0;SI(ET(B3>=$F$38;C3<=$L$38;C3>B3);0;MIN(C3+(C3<B3);1+$F$38)-MAX(B3;$L$38));0)+SI(P3>0;SI(ET(D3>=$F$38;E3<=$L$38;E3>D3);0;MIN(E3+(E3<B3);1+$F$38)-MAX(D3+(D3<B3);$L$38));0)+SI(R3>0;SI(ET(F3>=$F$38;G3<=$L$38;G3>F3);0;MIN(G3+(G3<B3);1+$F$38)-MAX(F3+(F3<B3);$L$38));0)J'essaierai de la raccourcir, mais sans présupposer des cas ne devant en principe pas se produire, c'est un peu compliqué... j'espère déjà ne pas avoir laissé de cas de côté !
Cordialement.
Bon retour MFerrand, je suis contente de te lire à nouveau
Bonjour Isabelle,
Merci de ta sollicitude. Je suis quelque peu occupé à des travaux en ce moment, mais je reviens toujours... A+