Calcul d'heures de prises de congé avec contraintes
Bonjour à tous, j'aurais besoin d'un coup de mains (mon niveau d'Excel est extrêmement basique).
Je voudrais créer un classeur pour calculer mes heures de prises de congé, en fonction du type d'heures utilisées (heures supplémentaire : HE, vacances annuelles : VA et vacances annuelles de l'année passée : VA-1) et en tenant compte de contraintes (j'ai mis des couleurs pour pouvoir s'y retrouver).
Pour les heures VA-1 et VA, normalement c'est déjà bon.
Mon contexte :
Des heures HE sont faites chaque mois (encodées moi-même pour chaque mois : janvier, AP5 ; février, AP6 ; etc... ; décembre, AP16). Ces heures sont utilisables maximum 3 mois après le mois où elles ont été faites (exemple : des heures faites en janvier peuvent être prises en février, mars ou avril, mais pas au-delà ; des heures faites en février peuvent être prise en mars, avril ou mai, mais pas au-delà ; etc...). Au-delà de 3 mois, elles sont perdues.
J'ai une colonne (AH) où sont calculé les heures totales récupérées sur un mois en fonction du type d'heure :
Janvier : - total des heures HE récupérée en AH4
- total heures VA-1 en AH5
- total heures VA en AH6
Février : - total des heures HE récupérée en AH8
- total heures VA-1 en AH9
- total heures VA en AH10 ; etc...
Je voudrais que dans la colonne AQ soit inscrit les heures HE qui restent à prendre avant la date limite pour chaque mois (exemple : janvier, maximum le 30 avril ; pour février, maximum le 31 mai ; pour mars, maximum le 30 juin etc...) qui tient compte des heures déjà récupérées dans le/les mois précédant et qui tient compte de la date limite pour chaque mois (c'est-à-dire que si des heures n'ont pas été reprisent avant la date limite, celles-ci sont perdues)
Exemple pour les heures de juillet :
- 20h30 d'heures HE sont encodée en AP5 pour le moi d'avril
- 8h00 d'heures HE sont encodés en AP6 pour le mois de mai
- 8h00 d'heures HE sont encodés en AP7 pour le mois de juin
Au mois de juillet, au total, 34h12 HE ont été récupérées. Dans la colonne AQ, pour le mois :
- d'avril, il sera indiqué 0h00 (car toute les heures ont été récupérées)
- de mai, il sera indiqué 0h00 (car toute les heures ont été récupérées)
- pour le mois de juin, il sera indiqué 2h18 (car il reste 2h18 à récupérés pour maximum le 30 septembre)
Je suis sous Excel 2021 FR.
J'espère avoir été claire et précis.
En vous remerciant grandement d'avance
Bonjour,
comment savoir que les heures d'avril ont été récupérées ?
C'est un peu confus parce que les lignes HE de chaque mois ne renvoient pas les mêmes heures que la colonne HE en AP.
Je rajouterais 2 colonnes : une qui donne les heures à prendre et une 2ème pour les heures restantes cumulées (en plus des heures restantes du mois en cours), histoire d'y voir clair.
Exemple avec des heures fictives ;
Mais si vous expliquez où prendre les heures à prendre et prises, je peux remplacer ces chiffres par des formules.
La formule en AT8 est simplement ;
=SOMME(AR5:AR8)Bonjour Doux Rêveur,
merci pour ta réponse.
Je n'ai peut-être pas été assez précis. Je précise également que pour moi 1 journée de congé = 7h36
Si je reprends mon fichier d'origine :
- dans les lignes oranges HE du calendrier (ligne 4, 8, 12, etc...), j'encode moi-même les heures que je reprends (par exemple : en juillet, 1 semaine).
Dans la colonne AH, le total des heures prises par mois est calculé (je ne sais pas si ça peut-être utile pour mon problème).
- dans la colonne AP, j'encode moi-même les heures HE que j'ai fait/presté/généré pour chaque mois (ça peut-être 8h00 pour un mois, 6h53, pour un autre mois, ou 10h30 pour un 3ème mois, etc...)
- dans la colonne AQ, je voudrais que soit calculé le nombre d'heures restant à prendre pour chaque mois, en fonction des heures déjà reprises (par exemple : j'ai généré 7h00 de HE en janvier (encodé moi-même en AP8) et j'ai pris 4h00 de congé en février (encodé moi-même en ligne 8), donc en AQ8, il faut qu'il soit calculé qu'il me reste 3h00 à prendre)
MAIS avec la contrainte suivante :
- les heures faites/prestées/générées pour un mois donné sont utilisables jusqu'à 3 mois au maximum (par exemple pour janvier : les HE peuvent être reprises en février, mars et/ou avril (30 avril max) ; février : mars, avril et/ou mai (31 mai max), etc...). Passé 3 mois, les HE sont perdues.
Exemple :
1°) J'ai des heures à reprendre qui ont été faites/prestées/générées en janvier (7h00), février (8h00), mars (7h36) et avril (8h00). J'ai repris 4h00 en février. En AQ8, il est calculé qu'il reste 3h00 à reprendre avant le 1 mai.
2°) À la suite de cela, je prends 3 jours de congé HE en mai. Les heures doivent être décomptées comme suit : les HE de janvier ne sont pas décomptées ; 3h00 reste donc afficher en AQ8 (les HE sont perdues car il fallait les prendre pour max le 30 avril). Les HE sont décomptées des mois de février, mars et avril (pour février, AQ9 = 0h00, pour mars, AQ10 = 0h00, pour avril AQ11 = 00h48 (23h36 - 22h48)).
S'il faut utiliser des feuilles, des lignes et/ou colonnes supplémentaires (quitte à les cacher) ou utiliser du VBA ou autre, cela ne me dérange pas tant que mon problème est résolu.
Comme il y a cette contrainte de 3 mois, je ne sais pas bien comment organiser ma feuille de calculs.
En espérant être plus clair et en remerciant grandement d'avance tous ceux qui pourront m'aider.
Salut,
j'avais bien compris que les heures n'étaient valables que 3 mois, et la formule que j'ai donnée pour AT8 fait ce calcul en additionnant les heures restantes du mois en cours et des 3 précédents.
Mais les intitulés des colonnes ne sont pas assez explicites ("Nbre d'heures", il faut préciser lesquelles), c'est pour ça que j'ai rajouté une colonne et changé les intitulés.
Bonjour,
j'ai essayé d'être plus explicite dans le nom des colonnes.
Pour la colonne "H. prises", la colonne AH peut-elle faire l'affaire ? (Vu qu'elle est liée directement au calendrier)
Merci d'avance.
Il manque toujours une colonne qui indiquerait les heures prises à déduire des heures à prendre pour pouvoir calculer les heures restantes.
As-tu testé la version que j'avais proposée avec 2 colonne en plus ? Il me semble que la formule en AT8 répond à ton besoin (à condition de savoir où chercher les heures prises).
En gros je suppose qu'il faut déduire la colonne AP de la colonne AH pour avoir les heures restantes par mois, puis rajouter une colonne qui fait le total sur 4 mois comme mis en AT8.
Si c'est bien ça je peux faire une formule qui va chercher le contenu de chaque mois en AP pour le mettre dans le récap à droite.
Le souci est qu'il n'y a que 2 lignes remplies en colonne AP dont une seule concernant les HE (dans ton 1er fichier), ce n'est pas suffisant pour tester la formule, c'est pour ça que j'avais mis des heures fictives.
Autre possibilité ;
La formule en AU8 convient ?
Voir aussi celles en AQ8 et AR5.
Bonjour,
la formule en AQ8 ne fonctionne pas. En AR5 non plus. AU8 non plus.
Je ne comprends pas pourquoi il faut une colonne "H. restantes cumulées".
J'ai renommé les colonnes pour essayer d'être le plus clair possible. J'ai également encodé des vraies heures (dans le calendrier 2023) que je suis certain de leurs calculs (pour la période d'avril 2023 à janvier 2024 inclus). La feuille ainsi doit représenter la situation de mes heures au 01/02/2024.
Je penses que ce qui perturbe, c'est que tout est sur une même ligne.
Exemple : - en AP8, c'est les HE accumulées au cours du mois de janvier (indépendant pour chaque mois)
- en AQ8, c'est les HE de janvier qui ont été récupérée au cours des mois de février, mars et/ou avril (validité de 3 mois)
- en AR8, c'est les HE accumulées au cours du mois de janvier MOINS ce qui à déjà été récupéré au cours des mois de février, mars et/ou avril
Ce que j'ai du mal à trouver, c'est par exemple :
- j'ai des HE accumulées en janvier (8h00) et en février (8h00)
- je prends 2 jours de congé (à 7h36)
- la feuille me calcul et m'indique en colonne AR :
1°) pour janvier : 0h00
2°) pour février : 0h48
Je penses que avoir des années en plus est plus facile pour la gestion des HE (et des formules à encodées), j'ai donc ajouté 3 années dans le fichier.
J'ai ajouté des notes en colonne AQ et AR pour plus de compréhension (j'espère).
Bien à toi
Bonjour,
je vais regarder mais d'abord je voudrais savoir quel est le souci avec les formules en AQ8, AR5 et AU8.
Chez moi ça donne ça ;
En toute logique, au moins une des 2 entre AT8 et AU8 doit correspondre à la demande, plutôt AU8 je pense.
Bonjour,
quand j'encode directement les heures à obtenir en colonne AQ, cela semble fonctionner (le calcul se fait correctement en colonne AR). J'obtient également les mêmes résultats dans les 2 colonnes AT et AU.
Reste à voir comment extraire les heures de la colonne AH pour qu'elles soient utilisées en AQ.
Bonjour,
j'ai regardé ton dernier fichier et je ne comprends pas comment les chiffres sont obtenus pour la colonne AQ, je pensais que les heures prises étaient une copie conforme de ce qui est indiqué à la fin de chaque mois dans le tableau pour la ligne HE.
D'où viennent les 07:41 et 07:31 pour les mois de février et mars alors que le tableau indique 00:00 pour ces mois ?
Bonjour,
Les heures calculées en AQ sont la somme des heures déjà récupérées (encodées moi-même dans le calendrier) pour chaque mois de la colonne AO. Et dans la colonne AR, c'est la différence entre AP et AQ (cela me permettra de voir directement en AR le nbre de HE à prendre avant la date limite en AS).
Exemple si je reprends mon dernier fichier joint :
- J'ai encodé moi-même dans le tableau en AP, les HE que j'ai fait.
- Dans le calendrier, en mai, j'ai pris 2 journées HE (encodé moi-même).
1°) La feuille va d'abord aller décompter (toute seule dans le tableau) les heures accumulées pendant le mois de février.
La feuille va faire 7h41 (colonne AP février) - 15h12 et m'afficher le résultat en AR.
Mais comme 15h12 > 7h41, la feuille m'indique 0h00 en AR pour le mois de février (car toute les heures ont été consommées) et va ensuite aller décompter le restant des HE en mars.
2°) La feuille va ensuite faire 8h00 (colonne AP mars) - 7h31 et m'afficher le résultats en AR, soit 0h29.
Ça c'est pour 1 congé. La fois suivante où j'ai pris congé, c'est en juillet. Comme les HE de mars ne peuvent se prendre que jusqu'au 30 juin max, les 0h29 sont perdues (elle reste cependant affichées). Donc, quand j'encode des HE dans le calendrier en juillet, ces HE là sont décomptées d'abord en avril, puis en mai, puis en juin.
Pour moi, je pense que la feuille doit fonctionner de la manière suivante :
0°) Dans le tableau, en colonne AP, les HE générés au cours des différents mois sont encodé par moi-même.
1°) Dans le calendrier, j'encode moi-même des HE que j'ai récupéré (par exemple en mai).
2°) Dans le tableau, la feuille vérifie s'il reste de HE à prendre au mois de février (car les HE de février peuvent être prise max le 31 mai).
3°) S'il reste des HE, la feuille décompte d'abord celles du mois de février jusqu'à épuisement.
Puis la feuille décompte des HE du mois de mars jusqu'à épuisement.
Puis la feuille décompte des HE du mois d'avril jusqu'à épuisement.
Ensuite, la feuille ne va pas plus loin (car je ne peux pas reprendre des HE d'un mois en cours).
- La feuille doit avoir le même comportement pour tous mois (si je prends des heures en juin, la feuille vérifie s'il reste des HE à prendre au moi de mars (car les HE de mars peuvent être prise max le 30 juin) etc...).
Ce que j'ai du mal à visualiser et à trouver, c'est comment faire en sorte que la feuille considère qu'on a déjà décomptés les heures ; comment faire en sorte de ne pas décomptés 2 fois les même heures ?
Un peu compliqué tout ça, il me semble que ça pourrait être plus simple.
Pourrais-tu récapituler et simplement dire d'où viennent les chiffres dont je parlais plus haut ?
Le tableau de gauche devrait servir à nourrir au moins une des colonnes du récap à droite, et pourtant aucun de tes chiffres ne vient de ce tableau, donc pour moi c'est pas clair.
Si c'était simple, je ne demanderais pas de l'aide
En AQ, 7h41 et 7h31 sont les nombres d'heures récupérées pendant le mois de mai (encodé moi-même dans le calendrier et qui doivent venir se calculer d'une manière ou d'une autre dans la tableau de droite).
7h41 + 7h31 = 15h12 (= 2 x 7h36)
7h41 en AQ est = au 7h41 en AP car toute les heures ont été reprises pour le mois de février.
Encore une fois, mon niveau d'Excel est vraiment très basique (+, -, *, /) et un peu de graphique. Niveau formule, c'est 1/100.
Ce n'est pas grave d'avoir un niveau basique, le mien l'est à peine moins, mais il faut pouvoir partir sur une base claire pour faciliter le choix des formules.
Pour moi, ceci n'est pas logique ;
En AQ, 7h41 et 7h31 sont les nombres d'heures récupérées pendant le mois de mai (encodé moi-même dans le calendrier et qui doivent venir se calculer d'une manière ou d'une autre dans la tableau de droite).
Si c'est pour le mois de mai, que font-elles sur les lignes de février et mars dans le récap' ?
Il faut que les heures prises et à prendre concernent le même mois, sinon on ne va pas s'en sortir.
Et j'ai bien saisi l'histoire de l'échéance de 3 mois, c'est ce que font les formules que j'ai proposées pour les heures restantes.
Là où on n'a pas la même approche, c'est que tu considères que la colonne AQ doit être le résultat d'un calcul ; personnellement je partirais sur une simple copie des valeurs du total du tableau (colonne AH) et le calcul se ferait uniquement dans la dernière colonne, histoire que tout soit clair. Sinon on ne comprend pas comment on est arrivé aux heures restantes.
Bonjour,
Pour moi, ceci n'est pas logique ;
En AQ, 7h41 et 7h31 sont les nombres d'heures récupérées pendant le mois de mai (encodé moi-même dans le calendrier et qui doivent venir se calculer d'une manière ou d'une autre dans la tableau de droite).
Si c'est pour le mois de mai, que font-elles sur les lignes de février et mars dans le récap' ?
ce sont des HE de février qui sont prises pendant le mois de mai.
Je comprends que ce ne soit pas logique au 1er abord car ce n'est pas le même mois, mais c'est logique de commencer par épuiser les HE les plus anciennes avant d'épuiser les HE les plus récentes (il faut voir ça comme une date de péremption sur des yaourts : on commence par manger les plus anciens (car on les a achetés avant), avant de manger les plus récents).
Après, on peut faire un 2ème tableau (dans une autre feuille ou décalé de 10 colonnes à droite (en BA par exemple)), où les calculs se feraient de manière plus compréhensibles/logiques pour toi. Ensuite, on va rechercher ces résultats dans ce nouveau tableau pour les afficher en AQ/AR. C'est un peu de travail en plus, mais pour moi c'est bon. Il suffira de masquer ces colonnes/feuille et le tour est joué.
Qu'en penses-tu ?
Là où on n'a pas la même approche, c'est que tu considères que la colonne AQ doit être le résultat d'un calcul ; personnellement je partirais sur une simple copie des valeurs du total du tableau (colonne AH) et le calcul se ferait uniquement dans la dernière colonne, histoire que tout soit clair.
Si tu penses qu'il y a une manière plus simple de procéder, pas de souci je te suis. Tant que le problème est résolu (après tout, tous les chemins conduisent à Rome
Bonjour,
c'est logique de commencer par épuiser les HE les plus anciennes avant d'épuiser les HE les plus récentes (il faut voir ça comme une date de péremption sur des yaourts : on commence par manger les plus anciens (car on les a achetés avant), avant de manger les plus récents).
En fait ça n'a pas d'importance, ce qui compte c'est de savoir combien d'heures il reste à prendre (en tenant compte de la validité de 3 mois), et ça on les trouve avec les formules que je t'avais proposées plus haut.
C'est un peu comme si tu mettais de l'argent sur un compte tous les mois puis que tu voulais retirer de l'argent en demandant à ta banque de te donner en priorité les premiers Euros que tu as déposés.
Ça pourrait fonctionner si tu déposais des billets numérotés et que la banque gardait tes billets sans les donner à quelqu'un d'autre, alors elle pourrait te les rendre dans l'ordre où tu les as déposés, mais ça ne marche pas comme ça.
Si tu mets bien des heures prises dans le mois où elles ont été prises, tu verras que mes formules renvoient le bon solde.
Mais s'il y a un souci on peut voir ce qui coince.
Bonjour,
C'est un peu comme si tu mettais de l'argent sur un compte tous les mois puis que tu voulais retirer de l'argent en demandant à ta banque de te donner en priorité les premiers Euros que tu as déposés.
Ça pourrait fonctionner si tu déposais des billets numérotés et que la banque gardait tes billets sans les donner à quelqu'un d'autre, alors elle pourrait te les rendre dans l'ordre où tu les as déposés, mais ça ne marche pas comme ça.
il faut le voir ainsi : dans une entreprise normal, il y a un seul et unique pot pour les heures HE. Dans ce pot, on y ajoute des HE et quand on a besoin d'heures, on va les reprendre (on ne fait que des + et des - dans ce pot unique).
Là où je travail, on a 12 pots différents (1/mois) indépendants les uns des autres. On ajoute des HE dans le pot du mois en cours (par exemple, actuellement on est en avril, et bien j'accumule des HE dans le pot du mois d'avril). Quand on sera au mois de mai, le pot d'avril est clôturé (plus possible de rajouter des HE). C'est le pot de mai qui est ouvert et où les HE seront accumulées. Et ainsi de suite touts les mois.
Et chaque pot de chaque mois est valable 3 mois (donc le pot d'avril est valable jusqu'au 31 juillet). Après, s'il reste des HE dans le pot d'avril qui n'ont pas été prises, c'est perdu (c'est super facile de gérer ses heures comme ça, très pratique
Pour en revenir à mon fichier, la formule que tu avais donnée en AQ (avec INDEX) ne fonctionne pas (et je ne la comprends pas). Sinon, tous le reste fonctionne.
Comment lier la colonne AH avec la colonne AQ ?
Bonjour,
j'ai bien compris le principe des 3 mois et je le redis, ma formule en AU en tient compte puisqu'elle additionne les heures restantes du mois en cours aux heures restantes des 3 mois précédents, et chaque mois ça se décale d'un mois.
La formule en AQ va simplement chercher le contenu des lignes HE de la colonne AH pour le mois correspondant à celui écrit à côté (AO). Elle revient au même que d'écrire =AH4 en AQ8, =AH8 en AQ9 ...
On voit qu'on passe de 26:36 à 00:00 entre avril et mai parce qu'on déduit les 07:00 de janvier qui ne sont plus valables en mai (ou plutôt on n'en tient pas compte, pas besoin de les déduire, puisque l'addition du mois de mai commence à février), ça fait donc 19:36 dont on déduit les 22:48 heures prises en mai, ce qui fait moins de 0 mais j'ai fait la formule de sorte que ça affiche au minimum 0.
Par contre je n'ai pas compris comment on pouvait prendre 22:48 alors qu'il ne restait que 19:36 de disponibles.
Bonjour,
Par contre je n'ai pas compris comment on pouvait prendre 22:48 alors qu'il ne restait que 19:36 de disponibles.
c'est parce que ce ne sont pas des heures "réelles". Du coup, j'ai encodés des vraies HE pour voir si les formules fonctionnent correctement.
La formule en AQ va simplement chercher le contenu des lignes HE de la colonne AH pour le mois correspondant à celui écrit à côté (AO). Elle revient au même que d'écrire =AH4 en AQ8, =AH8 en AQ9 ...
Malheureusement, ça ne fonctionne pas. Que ce soit la formule en colonne AQ ou prendre AH4=AQ8, cela ne calcule pas correctement les heures.
Voilà ce qu'il faudrait obtenir :
