Calcul heures de travail

Question

Après plusieurs heures de recherches infructueuses à différents endroits, je me tourne à présent vers vous. Pour plusieurs raisons, j'ai besoin depuis peu de calculer moi même mes heures de travail effectuées. J'ai réussi à faire un tableur qui calcul mon total à la journée avec plusieurs "heures d'entrée" et "heures de sortie" par jour. Mon problème : J'aimerai maintenant que dans la case O se calcule chaque jour l'excédent ou le déficit d'heure par rapport au nombre d'heure à effectuer (ici en B1). Dans l'idéal je voudrais que ce calcul s'affiche en positif comme en négatif. Pour pouvoir par la suite faire un calcul par semaine... puis par mois.... Totalement novice, j'ai essayé beaucoup de choses du plus simple au plus compliqué en partant de formules existantes et en les adaptant sans trouver la solution D'avance merci pour votre aide ! Valentine...

njhub · Accepted Answer

Essayez le fichier joint...

CHRIS1945 · Answer

Pas certain d'avoir bien compris où tu veux en venir aussi je t'ai proposé en annexe le traitement de la semaine 24 (pas touché au reste). J'ai nommé ton nombre d'heures à effectuer HAE pour faciliter le travail et j'ai enlevé ton test pour ajouter 1 qui me semble inutile (à moins qu'il y aie une raison que je n'ai pas comprise). J'ai aussi interprété que le dimanche était non presté donc j'ai utilisé la ligne pour y mettre le total de la semaine. Pour les mois, je propose de plutôt faire un autre tableau qui ferait la synthèse du premier par mois mais il faudra que tu expliques comment tu comptes séparer les semaines car, bien entendu, les mois ne s'arrêtent pas toujours avec la semaine. Dis moi si cela correspond ou pas. A bientôt Chris...

MFerrand · Answer

Salut Chris ! Le calendrier 1904 permet effectivement d'afficher des heures négatives dans Excel, cependant il décale toutes les références de dates, ce qui pourra poser des problèmes ultérieurs... Je préfère donc pour ma part rester systématiquement sur le calendrier 1900 (par défaut, et commun entre Excel et VBA à partir du 1er mars 1900), ce qui évite en plus de se poser ultérieurement la question de savoir sur quel calendrier l'on est. Il y a des méthodes pour contourner les difficultés... Formule total journée : Une différence horaire se calcule par simple soustraction : =hf-hd &#91;hf=heure fin, hd=heure début&#93; Mais l'heure étant cyclique sur 24 heures, si on atteint ou passe le point d'origine du cycle, le résultat de la soustraction sera faussé. On le rétablit en utilisant la fonction MOD : =MOD(hf-hd;1) qui donnera le bon résultat quels que soient hf et hd. Modulo 1 car les valeurs horaires varient entre 0 et 1, repassant à 0 en atteignant 1. 1=24 heures (1 jour). C'est plus élégant qu'avec plein de SI ! On a plusieurs différence à sommer pour la journée : plutôt que additionner élément par élément... : =SOMME(matrice hf-matrice hd) C'est là une formule matricielle, les matrices étant une plage de cellules contenant les valeurs hf, une autre plage de cellule de taille égale contenant les valeurs hd. On substituera SOMMEPROD à SOMME, qui peut fournir le même résultat mais ne réclame pas une validation matricielle de la formule. &#91;Une validation matricielle s'opère en appuyant simutanément sur les touches Ctrl+Maj+Entrée, au lieu de se contenter de la seule touche Entrée.&#93; L'utilisation de MOD sera également requise... On aboutit donc à : =SOMMEPROD(MOD(plage hf-plagehd;1)) Mais on est dans un cas où les plages hf et hd sont entremêlées. Prenons une ligne : hf : E3 G3 I3 K3 M3 hd : D3 F3 H3 J3 L3 Les plages sont égales mais discontinues... Devrait-on revenir à des additions d'éléments individuels ? Considérons : E3:M3 pour les hf et D3:L3 pour les hd. Les deux plages sont égales mais chacune contient 4 valeurs qu'il faut exclure, et dans les deux cas ce sont les valeurs de rang pair ! Donc en multipliant chaque plage par une matrice : {1.0.1.0.1.0.1.0.1} on obtiendrait le résultat voulu, éliminer les valeurs à exclure de chaque plage. On peut utiliser des matrices sous la forme indiquée ci-dessus : valeurs encadrées par des accolades, et séparées par des points (matrices horizontales) ou des points-virgules (matrices verticales). Mais on procède plus souvent dans Excel avec les fonctions LIGNE ou COLONNE, moyen simple de renvoyer des matrices. COLONNE(E3:M3) renverra la matrice : {5.6.7.8.9.10.11.12.13}, pas tout à fait la matrice dont on a besoin... Mais : MOD(COLONNE(E3:M3);2) renverra elle une matrice composée de 1 pour les valeurs impaires et 0 pour les valeurs paires, soit : {1.0.1.0.1.0.1.0.1} qui est bien la matrice que l'on veut. On peut passer à la formule finale : =SOMMEPROD(MOD(E3:M3*MOD(COLONNE(E3:M3);2)-D3:L3*MOD(COLONNE(E3:M3);2);1)) Formule que l'on peut étendre (recopier) sur la colonne. Ecart avec la durée journalière due : C'est tout simplement : =N3-$B$1 Mais le résultat peut être positif ou négatif (ou nul), Excel n'affiche pas les valeurs horaires négatives ! Le résultat renvoyé est cependant bien un résultat, lequel peut être réutilisé pour les calculs ultérieurs, seul l'affichage pose problème. On va donc insérer une colonne (méthode souvent utilisée) : en O, la formule ci-dessus qui donne le résultat, colonne que l'on masquera, et en P le résultat que l'on affichera sous forme texte. La valeur sera donc la valeur absolue de O3 : ABS(O3), à laquelle on applique un format "h:mm" en utilisant la fonction TEXTE. Reste à mettre un signe de l'écart, + ou - (ou rien si nul). Plutôt que de s'embarquer dans des SI SI... la fonction SIGNE renvoie 1, 0 ou -1 selon que le nombre qui lui est soumis est positif, nul ou négatif. On peut donc rechercher la valeur de SIGNE(O3) dans une matrice {1;0;-1} avec EQUIV puis appliquer la valeur renvoyée par EQUIV comme index pour récupérer la valeur cherchée dans une matrice {"+";"";"-"} avec INDEX. On pourrait également utiliser RECHERCHE(SIGNE(O2);{1;0;-1};{"+";"";"-"}) mais restons dans le classique INDEX/EQUIV... =INDEX({"+";"";"-"};EQUIV(SIGNE(O3);{1;0;-1};0))&amp;TEXTE(ABS(O3);"h:mm") Formule en P3. La suite on ne sait pas trop ou mettre ce qu'on doit calculer, mais pas de problème particulier pour le faire... Cordialement...

CHRIS1945 · Answer

L'application m'intéresse et je me suis permis de la peaufiner un peu. A toi de voir si cela t'intéresse et si elle répond vraiment à ce que tu attends. Il y a un mode d'emploi à l'intérieur qui explique comment utiliser le classeur et je te joins également un petit mémo sur le calcul des numéros de semaine selon les normes ISO européennes. En ce qui concerne les remarques de MFerrand : pour ma part, je préfère travailler avec le paramétrage de calcul de date sur 1904 qui est la méthode préconisée par Microsoft et qui est plus moderne et plus souple (calcul négatif permis). Je n'ai jamais eu de mauvaise surprise jusqu'à maintenant. en ce qui concerne l'usage de la fonction MOD, il est vrai que cela permet de calculer des différences horaires quand on est à cheval sur les jours mais ce n'est manifestement pas le cas dans l'application concernée aussi j'ai préféré rester simple et je pense plus compréhensible pour Edit. Comme d'habitude, Excel permet plusieurs solutions... J'espère que cela t'aidera A+ Chris...

MFerrand · Answer

J'aimerais bien que tu m'indiques où tu as vu que Microsoft préconisait l'utilisation du calendrier 1904 ! Tout ce que j'ai pu lire sur la question, sous la plume de Microsoft, se borne à la description des deux options de calendrier : 1904 fait pour la version Mac qui démarre au 2 janvier 1904 (parce que le système de Mac était basé sur un tel calendrier à l'époque) et 1900 pour la version PC qui démarre au 1er janvier 1900 (parce que les autres applications sur PC &#91;soit les concurrents !&#93; faisaient ainsi), et à indiquer la différence constante de 1462 jours entre les deux calendriers, indiquant que les deux sont respectivement les options par défaut sur Mac et PC, et comment modifier l'option pour un classeur donné (seulement), sans donner de préférence particulière ! Déjà si Microsoft avait une préconisation en la matière, elle devrait se matérialiser par la possibilité de modifier l'option au niveau de l'application, et non seulement par classeur ! Microsoft ne rentre d'ailleurs pas dans les détails de la gestion des dates et des problèmes que l'on peut rencontrer, ce qui n'est pas vraiment surprenant dans la mesure où les problèmes sont liés à sa gestion calamiteuse des dates ! Il faut noter par ailleurs que VBA utilise un calendrier commun avec Excel (version PC) à partir du 1er mars 1900, pour les dates antérieures, il ne reproduit pas l'erreur d'Excel, et il gère les dates antérieures au 1er janvier 1900 (ce qui n'est pas le cas d'Excel toutes versions). Si donc on travaille sur des dates avec VBA, on peut penser que l'on risquera fort d'avoir quelque problème à un moment ou un autre en utilisant simultanément le calendrier 1904. Je ne sais comment gèrent cet écueil les utilisateur de Mac qui ont cette option par défaut mais en tant qu'utilisateur PC j'aime autant ne pas me créer de difficulté et conserver systématiquement la correspondance Excel-VBA. Il faut d'ailleurs dire que sans le plus qu'apporte VBA à Excel, on serait tenté de basculer sur OpenOffice pour cette question : la gestion des dates y est faite sur un calendrier identique à celui de VBA, les dates avant 1900 sont gérées, et l'affichage des dates bascule en calendrier Julien lorsqu'on remonte au-delà de l'application de la réforme grégorienne (soit : si on tape 15 octobre 1582 en A1, en A2 on met =A1-1, il s'affichera 4 octobre 1582 !)... Le 'modernisme' en la matière n'est donc pas du côté d'Excel ! Je reconnais que c'est tentant de choisir l'option 1904 pour pouvoir afficher directement des heures négatives, cependant le problème que pose Excel sur PC à cet égard se limite à un problème d'affichage, les résultats sont calculés et réutilisables dans d'autres calculs, ce pourquoi il me paraît un peu hâtif de choisir l'option 1904 sur ce seul critère horaire. C'est toujours un choix personnel (que pour ma part je ne conseille pas...) mais qu'il me semble logique de faire en étant éclairé sur toutes les incidences qu'il aura... Pour le calcul avec MOD, je ne l'ai utilisé et explicité que parce que VaVaCDR avait initialement une formule avec des SI inférieur... +1... laissant penser qu'elle calculait des durées chevauchant minuit. Mais en tout état de cause, il est bon de connaître cette formulation, à la fois simple et efficace pour tout calcul de durée horaire... Je me suis aussi penché sur ton calcul du numéro de semaine qui m'a paru quelque peu compliqué (je n'avais pas le temps, et je n'ai pas décortiqué jusqu'au bout...). Mais j'ai tapé 3 dates au hasard, pour les deux premières la réponse était bonne mais pour la 3e, soit : 31/12/1912, ta fonction me renvoie 53 ! Or 1912 n'a que 52 semaines, et il s'agit d'un mardi appartenant à la semaine 1 de 1913... Il me semble que tu devrais revoir... Je livre à toutes fins utiles la formule que j'utilise (sans macro) d'une part, et la fonction personnalisée d'autre part, toutes deux basées sur la définition de l'ISO 8601, aboutissant à ce que la première semaine de l'année est celle qui contient la date du 4 janvier. La semaine 1 commence donc le 4 janvier si c'est un lundi et le lundi qui précède dans le cas contraire. Il est judicieux de décaler la recherche à partir du 3 janvier (si le 4 est un lundi, le 3 sera un dimanche), car utilisant JOURSEM (sans second argument) pour cibler le lundi, il suffit alors de déduire la valeur renvoyée par JOURSEM (1 à 7) et ajouter 2 sans autre correctif. =ENT((A1-RECHERCHE(A1;DATE(ANNEE(1)+{-1;0;1};1;3)-JOURSEM(DATE(ANNEE(A1)+{-1;0;1};1;3))+2))/7)+1 A1 contenant une date quelconque dont on cherche le numéro de semaine. Function NSEM(d As Date) As Integer Dim dref Application.Volatile dref = DateSerial(Year(d + (8 - Weekday(d)) Mod 7 - 3), 1, 3) dref = dref - Weekday(dref) + 2 NSEM = (d - dref) \ 7 + 1 End Function La ligne de calcul définissant la date du 03/01 de l'année à prendre en référence, est reprise pour ce qui est de la méthode d'une fonction émise par Laurent Longre (pas plus compact pour cibler la bonne année). A la ligne suivante, ce dernier renvoie le résultat en une ligne en remontant au dernier lundi de l'année précédente, ce qui évite d'ajouter 1. Je suis resté sur le calcul du premier lundi et du numéro, décomposé en 2 lignes (car ça facilite ma compréhension ! ). On ne devrait cependant plus avoir à recourir à la composition d'une formule ou d'une fonction personnalisée, à terme : 2016 dispos d'une fonction dédiée au renvoi du numéro de semaine ISO, 2013 aussi semble-t-il (ou la fonction NO.SEMAINE dotée d'un paramètre adéquat), et pour 2010 la fonction NO.SEMAINE avec le paramètre 21 en 2e argument semble renvoyer la réponse exacte (bien que sa définition dans l'Aide, semaine contenant le 1er mardi, soit fausse au regard de la norme). Je ne l'ai pas testée exhaustivement (soit au moins 4 dates critiques sur une période de 3 siècles) mais sur les dates où la fonction VBA DatePart est en erreur, cette fonction renvoie la bonne réponse... Cordialement...