XL 2016 Calcule d'horraire fais le dimanche/ jour ferier [Resolu]

[Keran]

Bonjour ,

Voila je suis entrain de finaliser un outils pour mon travail pour calculer les masses horaires, un des petits soucis qu'il me reste c'est d'indiquer combien d'heures sont effectuer par agents en dimanche ou jours féries.

J'aimerais trouver une solution qui indiquerais dans la colones "heures de dimanches " des pages "Bjanvier" a "BDecembre", en gros sa serais EX = Si (A2 : AF2 (la ou j'ai mit les dates ) = "DimFe" (correspondant au ferier et dimanche dans la pages "Données) & $A9 = "Bjanvier!$A11";Somme("Latranchedheure"*"Lenombredheurescorrepondant)

Je sais pas si c'est clair :/

Voila ci joint mon fichier https://www.cjoint.com/c/GLomqk15DQv

Merci d'avance vraiment.

 

[CISCO]

Bonjour

Je n'ai pas le temps dans l'immédiat. Je regarderai plus tard...

@ plus

 

[Keran]

il n'ya pas de soucis , du coup

=SOMMEPROD(ESTNUM(EQUIV(HJanvier!B$2:AF$2;DimFe;0))*SIERREUR((DROITE(DECALER(HJanvier!B$8:AF$8;EQUIV(A56;HJanvier!A$9:A$56;0););5)-GAUCHE(DECALER(HJanvier!B$8:AF$8;EQUIV(A56;HJanvier!A$9:A$56;0););5));0))

fonctionne mais pour les agents de nuits les met en " moins " j'editerais le message si je trouve une solution

 

[CISCO]

Bonsoir

Quels calculs est-ce que tu veux exactement, et où ?

@ plus

 

[Keran]

Bonsoir
J'aimerais que pour les horaires de nuit sa sois pas noter en "-12:00" par exemple car la du coup quand il font 21:15 7:15 sa donne "-12:00"

Et je voudrais que les heures faites après 21:30 les dimanches dans sois da dans une autre colonne (peu importe où ya la place)

 

[CISCO]

Bonjour

Bonsoir
J'aimerais que pour les horaires de nuit, ça ne soit pas noté en "-12:00" par exemple car là du coup quand ils font 21:15 7:15 ça donne "-12:00"

Où est-ce que tu fais ce calcul ?

@ plus

 

[Keran]

Bonjour,

Pour le calcul en négatif c'est quand j'ai fais glisser la formule des heures de dimanche, pour l'équipe de nuit, toute les autres cellule où les horaires sont de jour sa fonction niquel mais pour ce de nuit c'est en négatif.

Je voudrais faire un calcul comme celui des heures de dimanche mais juste que sa sois compte dans une autre colonne les heures comprise entre 21:00 et 00:00 (car horaire de nuit) . Et que du coup dans ma formule des heures de dimanche juste arrivé à rajouter une condition le hic c'est que j ai pas encore réussi mais je vais continuer de chercher aujourd'hui

J'ai fais des essais je suis sur mobile là je transmets mes essais d'ici 20 minutes.

Merci à tous à l'heure

 

[CISCO]

Bonjour

Bonjour,
Pour le calcul en négatif c'est quand j'ai fais glisser la formule des heures de dimanche, pour l'équipe de nuit, toute les autres cellule où les horaires sont de jour sa fonction niquel mais pour ce de nuit c'est en négatif.
Merci à tous à l'heure

J'avais vu cette partie il y a quelques jours, mais depuis, j'ai oublié...

Pour éviter ces valeur négatives, tu peux faire avec

SOMMEPROD((JOURSEM(HJanvier!B$2:AF$2)=1)*SIERREUR(((DROITE(DECALER(HJanvier!B$8:AF$8;EQUIV(A11;HJanvier!A$9:A$56;0););5)<GAUCHE(DECALER(HJanvier!B$8:AF$8;EQUIV(A11;HJanvier!A$9:A$56;0););5))+DROITE(DECALER(HJanvier!B$8:AF$8;EQUIV(A11;HJanvier!A$9:A$56;0););5)-GAUCHE(DECALER(HJanvier!B$8:AF$8;EQUIV(A11;HJanvier!A$9:A$56;0););5));0))

Tu peux mettre cette formule dès AE11, puis la tirer vers le bas.

C'est la même méthode, avec un test en plus, (DROITE(DECALER(HJanvier!B$8:AF$8;EQUIV(A11;HJanvier!A$9:A$56;0) ; ) ;5)<GAUCHE(DECALER(HJanvier!B$8:AF$8;EQUIV(A11;HJanvier!A$9:A$56;0) ; );5)). Celui ci renvoie 1 (=24:00) par ex pour 21:15:07:15, parce que 07:15 < 21:15, et 0 dans le cas contraire. Ce qui fait que la formule fait comme calcul 31:15 - 21:15 au lieu de 7:15 - 21:15.

@ plus

P.S : Tu peux rendre la formule plus compréhensible en l'écrivant sous la forme SOMMEPROD((JOURSEM(HJanvier!B$2:AF$2)=1)*SIERREUR(((Hdroite<Hgauche)+Hdroite-Hgauche);0))
et en définissant les deux noms correspondants, Hdroite et Hgauche, dans le gestionnaire de noms.

 

[Keran]

rebonjour
oki j'etait vraiment pas loin j'avais tester sa juste j'avais mis

=SOMMEPROD(ESTNUM(EQUIV(HJanvier!B$2:AF$2;DimFe;0))*SIERREUR((DROITE(DECALER(HJanvier!B$8:AF$8;EQUIV(A25;HJanvier!A$9:A$56;0););5)<GAUCHE(DECALER(HJanvier!B$8:AF$8;EQUIV(A25;HJanvier!A$9:A$56;0););5));DROITE(DECALER(HJanvier!B$8:AF$8;EQUIV(A25;HJanvier!A$9:A$56;0););5)-GAUCHE(DECALER(HJanvier!B$8:AF$8;EQUIV(A25;HJanvier!A$9:A$56;0););5));0))

Comme quoi les petit detaille compte x)
je fais le teste du coup le 21 : 00 et je reviens te dire merci
bonne journée

 

[CISCO]

Bonjour

Est-ce que tu veux bien voir si c'est bon dans la colonne Bjanvier!AP pour ce qui est des heures de travail entre 21:00 et 24:00, quel que soit le jour considéré ?

Si oui, regardes la définition des noms Hdroitesimple, Hdroite, Hgauche et durée dans le gestionnaire de noms.

@ plus

P.S : Tout cela ne peut correctement fonctionner que si les horaires sont bien donnés avec 11 caractères. 07:15:17:30 fonctionnera, mais pas 7:15:17:30 car Hgauche ramènera 7:15: qu'Excel ne considérera pas comme une heure.

P.S2 : Il faut mieux remplacer "24:00" par 1, et "21:00" par (21/24) dans la formule de durée.

 

[Keran]

Impeccable j'admire ton niveau
c'est imppecable pour les heures apres 21:00 sur tous le mois , pour les heures entre dimanche 00:00- 07:00 , et 21:00 - 00:00 je pense que j'ai juste reprendre les definition et mettre l'equiv a Dimfe non ? (enfin plus moins ^^' )
tu pense que je pourrais faire les fonction Hdroite et Hgauche avec un indirect pour quelle sois valable pour les autres mois ? ou il faut que je refasse une fonction pour chaque mois?

 

[CISCO]

Rebonjour

Essaye en remplaçant la définition de Hdroitesimple par

=DROITE(DECALER(INDIRECT("H"&Bjanvier!$A$1&"!B8:AF8");EQUIV(Bjanvier!$A11;INDIRECT("H"&Bjanvier!$A$1&"!A9:A56");0););5)

et celle de Hgauche par

=GAUCHE(DECALER(INDIRECT("H"&Bjanvier!$A$1&"!B8:AF8");EQUIV(Bjanvier!$A11;INDIRECT("H"&Bjanvier!$A$1&"!A9:A56");0););5)

Cela ne fonctionnera que si le nom du mois est écrit dans la cellule A1 de la feuille considérée exactement comme dans le nom du fichier.

@ plus

 

[Keran]

Parfait , au debut je m'enteter pas vouloir mette les "bjanvier" mais juste indirect("'B"&$A$1&! etc et je comprennais pas pourquoi sa marcher pas dans les definition mais apres j'ai compris quand tu ma montrer xD ... je suis un boulet je sais merci infiniment pour toute ton aide precieuse ,ta patience avec le boulet que je suis .. pour le reste je vais essayer de me debrouiller il me restera juste a mettre seulement les heures comprise entre 7:00 et 21:00 pour les dimanches de jour , et dans une autre colonne les heures comprise entre 00:00 - 7:00 et 21:00 - 00:00 faite le dimanche mais avec la fonction durée je pense que sa sera pas impossible .

Enfin voila je te remercie vraiment, je viendrais poster le résultat final .

 

[CISCO]

Bonjour

Je vais te laisser chercher un peu tout seul . Courage.

Au besoin, remplace dans le gestionnaire de nom durée par durée2124.
Regardes bien sa définition

=SIERREUR(SI("24:00"<SIERREUR(Hdroite;0);"24:00";SIERREUR(Hdroite;0))-SI("21:00">SIERREUR(Hgauche;0);"21:00";SIERREUR(Hgauche;0));0)

Définis deux autres noms durée0007 et durée0721 avec les définitions adéquates, faites à partir de la précédente.
Ensuite, ne te reste plus qu'à mettre dans le tableau les formules SOMMEPROD(1*SI(durée0007<0;0;durée0007)), SOMMEPROD(1*SI(durée0721<0;0;durée0721)) ou SOMMEPROD((JOURSEM(INDIRECT("H"&Bjanvier!$A$1&"!B2:AF2"))=1)*SI(durée...<0;0;durée...)).

Je ne te promet pas que cela sera bon, puisque je ne l'ai pas testé, mais cela doit être quelque chose comme ça.

@ plus

P.S 1 : Ne pas oublier de valider en matriciel.
P.S 2: Il faudrait peut être réfléchir aux symboles "< " dans la définition ci-dessus. Il faudrait peut être un "<=" à la place d'un de ces, ou des deux "<". En ne laissant qu'une valeur dans la ligne concernée dans la feuille Hjanvier!, fais des tests en mettant des périodes de travail commençant ou finissant à 21:00 ou à 24:00 et regarde si les résultats sont corrects dans la même ligne dans Bjanvier!.

 

[Keran]

bonsoir ,
Bon j'ai reussi a faire pour les heures de dimanche juste de journée j'ai par contre un petit soucis pour les heures de nuit j'i suis presque juste j'aimerais qu'il ne compte pas les heures au dessus de 7:00 j'ai essayer sa elle marche pour recuperer les heures de 00:00 a 7:00 et plus mais du coup c'est pas bon vue que sa depasse les 7:00

=SIERREUR(SI("00:00"<SIERREUR(Hdroitesimple;0);"00:00";SIERREUR(Hdroitesimple;0))+SI("07:00">=SIERREUR(Hdroitesimple;0);SIERREUR(Hdroitesimple;0);SI("07:00" >= SIERREUR(Hdroitesimple;0);SOMME(SIERREUR(Hdroitesimple;0)-("07:00"-SIERREUR(Hdroitesimple;0)));"00:00"));0)

Au passage passe de bonne fêtes


Edit : en faite j'ai trouver pas sur que ça la methode la plus simple mais sa marche

 

[CISCO]

Bonjour

Pour Dimanche, de 00:00 à 07:00, Cf. en pièce jointe dans Bjanvier!AR11. Formule matricielle à tirer vers le bas.

J'ai procédé comme précédemment, mais comme les heures, dans la définition de durée0007, écrites sous la forme "00:00" et "07:00", ne donnaient pas systématiquement les bons résultats, j'ai tout remplacé par 0 (pour "00:00"), 7/27 (pour "07:00"), et aussi dans durée2124, 21/24 (pour "21:00) et 1 (pour "24:00").

Ce qui donne :
* pour durée0007

=SIERREUR(SI((7/24)<SIERREUR(Hdroite;0);(7/24);SIERREUR(Hdroite;0))-SI(0>SIERREUR(Hgauche;0);0;SIERREUR(Hgauche;0));0)

* et pour dureée2124

=SIERREUR(SI(1<SIERREUR(Hdroite;0);1;SIERREUR(Hdroite;0))-SI((21/24)>SIERREUR(Hgauche;0);(21/24);SIERREUR(Hgauche;0));0)

@ plus
P.S : D'habitude, j'utilise 1 pour 24 h, 5/24 par ex pour 5 h. Mais comme c'est tellement plus compréhensible sous la forme "24:00", et comme il me semble avoir lu que c'était faisable, j'ai essayé dans les derniers fichiers avec "24:00". Apparemment, cela fonctionnait pour "21:00" et "24:00", pas pour "07:00".