Calcul d'heures avant 8 h et après 19 h

Bonjour,
On me demande de calculer les heures écrêtées. Cela correspondant à la somme des heures ou les agents badgent avant 8h et après 19h.
L’idéal, serait, par ligne, de pouvoir calculer directement dans une seule cellule la somme des heures avant 8h et après 19h.
Au départ j’ai tenté une formule du genre : =SI($E2<=“08:00”;“08:00”-$E2;"") ce qui a semblé fonctionner mais lorsque j’essaye d’étirer ma formule cela n’abouti pas.
Mais il faudrait une formule dans ce genre la en considérant la ligne entière sur un seul total par ligne.
Je dois ensuite effectuer un sous total par agent, et effectuer ensuite un total général.
Je dois également effectuer un sous total par Homme et Femme correspondant à la colonne A.
Pouvez-vous m’aider à consolider une formule efficace que je pourrai réutiliser chaque année lorsqu’on me donne ce fichier important avec plus de 6000 lignes à traiter ?

Merci d’avance à tous, j’ai fait plein d’essais, mais infructueux. Peut_être mon fichier de départ n’a pas la bonne forme ?Heures écrétées BILAN SOCIAL 2017.xlsx (869,5 Ko)

Bonsoir,

Le problème avec les formules et les heures sur des conditions, c’est le résultat des “Négatifs”

Donc, je pourrais faire une solution (avec du VBA) qui te rassemblerais en colonne L et M les durées Av 8h00 et Ap 19h00 par un clic bouton
Sinon par formule, c’est possible, mais :slight_smile:soit avec des formules à rallonge (4 colonnes à gérer ) pour un double calcul.

Après, à voir le classeur:
Le Nb employé est il plus important par rapport au classeur actuel
Je suppose que c’est basé sur une année

Bonjour @Rachel_Grellier,
Salut @Mimimathy, :wink:

@Rachel_Grellier, bon je vois que tu es en de bonnes mains, alors je ne vais pas lutter.

Toutefois, comme j’ai commencé, pour remplacer les colonnes “L & M” et ne faire qu’une seule formule, je te propose ceci:

Dans la colonne de ton choix, admettons “N”

En “N2”

=SI(E2>1/3;0;MOD("08:00"-E2;1))+SI(H2<4/5;0;MOD(H2-"19:00";1))

Tirer vers le bas autant que nécessaire.

Cordialement.

2 J'aimes

Salut MDO :wink:

C’est pas mal, pour les colonnes E et H,

Mais le mais, c’est les colonnes F et G :thinking:, il manque des heures

Peut-être pour cela que je ne mange que des “coquillettes” non farcies au foie gras :grinning:

Re @Mimimathy,

Moi je vois la question telle quelle est posée, et donc pour moi les colonnes “F & G” n’interviennent pas dans la demande, mais sans doute interprétais-je mal le problème, ce ne serai pas la première fois :upside_down_face: n’est-ce pas !

Nous aurons sans doute des nouvelles de @Rachel_Grellier, qui éclairera d’un jour nouveau tout ceci.

Amicalement.

Hello,
Je vois que tu as des reponses.
J’ai placé les heures en L1 et M1.
Je te propose une formule par cas : <8:00 et >19:00
Exemple ligne 2 : Somme (matricielle) de la difference avec 8:00 de toutes les cellules (de E2 à J2) non vides et <8:00. L’autre sera pareil pour >19:00.

{SOMMEPROD((E2:J2<$L$1)*1;((E2:J2)<>"")*1;SI(($L$1-E2:J2)<0;0;$L$1-E2:J2))}

Exemple : Heures écrétées BILAN SOCIAL 2017.xlsx (1,0 Mo)

Re @Rachel_Grellier, @Mimimathy,
Salut @WildDigiSoft,

Ça y est je vois ce que tu veux dire @Mimimathy,

Alors on peut:

En “N2”

=SI(E2>1/3;0;MOD("08:00"-E2;1))+SI(MAX(F2:J2)<4/5;0;MOD(MAX(F2:J2)-"19:00";1))

Tirer vers le bas autant que nécessaire.

Cordialement.

Joli mdo ,

attention au modulo en comparaison aux fractions , ca joue des tours sur les valeur proches.
Exemple il va rater 19:03 > 19:00 H12 ou 19:07 > 19:00 en H14.

1 J'aime

Re @WildDigiSoft,

Oui, tu as raison, c’est étrange ça, car 19:00 est bien = à 4/5 mais aussi à 0,791666666666667

Du coup en “N2”

=SI(E2>1/3;0;MOD("08:00"-E2;1))+SI(MAX(F2:J2)<0,791666666666667;0;MOD(MAX(F2:J2)-"19:00";1))

Et si il y avait un erreur sur 8:00 = à 1/3, alors on peut mettre dans la formule 0,333333333333333

Ce qui donnerait en “N2”

=SI(E2>0,333333333333333
;0;MOD("08:00"-E2;1))+SI(MAX(F2:J2)<0,791666666666667;0;MOD(MAX(F2:J2)-"19:00";1))

Ce qui évite quand même une formule matricielle.

Cordialement.

:+1: yosh
C’est pour cela que j’utilise les données date heure dans des cellules directement.
Ca vient du fait que 4/5 est arrondi a 0,8.

Re à toutes et tous,

Une dernière pour la route, accompagnée de “coquillettes” farcies au foie gras :blush:

En “N2”

=SI(E2>MOD("08:00";1);0;MOD("08:00"-E2;1))+SI(MAX(F2:J2)<MOD("19:00";1);0;MOD(MAX(F2:J2)-"19:00";1))

Tirer vers le bas autant que nécessaire.

Bonne nuit les petits :zzz: :zzz: :zzz:

2 J'aimes

Salut MDO,

Je te nomme “ROI DU MODULO:grinning::+1:

1 J'aime

Ouai Mod(Mdo100;1) à 00h = ZZZZ ^^
Il doit ronfler encore :slight_smile:

Mille mercis à vous tous !!!

Je vais reproduire tout cela et tâcher de comprendre vos différentes propositions.

Merci encore et bonne fin de journée