Calcul d'écart entre 2 dates et pénalités

[rx.stephane]

Bonjour,
Dans le fichier joint, je voudrai dans la colonne D, inscrire une formule qui me calcule l’écart entre la colonne C et la colonne B en s’appuyant sur la valeur de la colonne A :
• Si A = 0 alors pas de calcul d’écart et dans la colonne E, le montant des pénalités est de 0€
• Si A = 1 alors on calcul l’écart entre C & B et le montant de la pénalité s’inscrit dans la colonne E. Le montant de la pénalité répond aux règles suivantes :
o Si C-B > à 30mn mais < ou égal à 1heure alors 1000€ de pénalité
o Si C-B > à 1 heure et plus alors 2000€ de pénalité
Je voudrai que cette formule prenne en compte :
• Si B > C alors on inscrit rien dans la colonne D et dans I la pénalité est de 0€
• Si C-B = 30mn alors pénalité = 0€
• Si C-B = 1h alors pénalité = 1000€
La formule que j’ai trouvé et inscrit dans la colonne D ne résout pas tous les cas de figure, j’ai surligné en rouge les cas qui me posent problème.
Je ne comprends pas comment à la ligne 15 le calcul de la pénalité se fait bien alors qu'il est faux à la ligne 7.
Merci par avance pour votre aide.
rx.stephane

 

[JBARBE]

Re : Calcul d'écart entre 2 dates et pénalités

Bonjour à tous,

Si j'ai bien compris et pas fait d'erreur !

Bonne soirée

 

[renyeu]

Re : Calcul d'écart entre 2 dates et pénalités

Salut,
idem JBARBE
A suivre
Cdlt
Renyeu

 

[JBARBE]

Re : Calcul d'écart entre 2 dates et pénalités

Bonjour renyeu, il semble que notre Excel fait des siennes pour le calcul des 30 mn qui devrait être 0 et non 1000 !

Cela fonctionne sur une cellule sur deux !

Un problème que je ne comprend pas !!!!!

 

[renyeu]

Re : Calcul d'écart entre 2 dates et pénalités

Oui étrange...
Mon 1er fichier du post précedent était retouché sous LibreOffice.
Le fichier joint est retouché sous Excel 2007.
Lorsque je conserve le format d'origine-date avec heures-le résultat du traitement de la pénalité est différent qu'en faisant le calcul après extraction des heures seules du format d'origine
En fait avec le format d'origine l'heure est traitée comme un nombre très grand (depuis le 01/01/1900) alors que l'extraction des heures calent celles-ci au 01/01/1900...
A mon sens, c'est la seule explication de cette distorsion.
A suivre.
Cdlt
Renyeu

 

[chris]

Re : Calcul d'écart entre 2 dates et pénalités

Bonjour

Il y a 2 problèmes : des secondes dans 2 des horaires qui font que le format affiché n'est pas pile 1/2h ou 1h plus des problèmes d'arrondi de calcul d'Excel. Il faudrait arrondir les soustractions et les temps de référence à 6 décimales.

 

[rx.stephane]

Re : Calcul d'écart entre 2 dates et pénalités

Bonjour JBARBE et renyeu

Merci pour vos réponses qui résolvent mon souci sur les lignes 7, 9 & 13, mais les problèmes perdurent sur les lignes 12 & 20.

Dans mon fichier Test.xls ci-joint :
• Ligne 12 => Le résultat dans la cellule D12 devrait être = 25h et dans E12 = 2000€
• Ligne 20 => Le résultat dans la cellule E20 devrait être 0€ car A20 = 0


Si vous avez une idée, je suis preneur.

Merci à vous.

rx.stephane

 

[JCGL]

Re : Calcul d'écart entre 2 dates et pénalités

Bonjour à tous,

Peux-tu essayer avec ce format :



Pour les heures négatives : dans ton exemple la colonne B est inférieure à la colonne C sauf en ligne 13.

A+ à tous

 

[renyeu]

Re : Calcul d'écart entre 2 dates et pénalités

Bonsoir,
@Chris:
Ligne 2, sauf erreur de ma part il n'y a pas de secondes...
@rx.stephane:
Ligne 12 par contre il ya des secondes qui générent l'erreur.
Ligne 20 voir le fichier de mon post 5
Cdlt
Renyeu

 

[JBARBE]

Re : Calcul d'écart entre 2 dates et pénalités

Mettre le format [h]:mm colonne D

=SI(A6=1;SI(D6<$F$4;0;SI(ET(D6<$G$4;D6>$F$4);1000;SI(D6>$G$4;2000;"")));0) colonne E

 

[rx.stephane]

Re : Calcul d'écart entre 2 dates et pénalités

JBARBE,

Le format [h]:mm (ou "[hh]:mm;@", merci également à JCGL) colonne D résout effectivement le problème.

J'ai reporté ta dernière formule dans ton fichier joint, il y a toujours un souci sur les cellules 7, 9, 13 de la colonne E. C'est ce souci sur les 30mn et 1h alors que les secondes sont à 00.

Bonjour chris,
Comme tu le suggère : "Il faudrait arrondir les soustractions et les temps de référence à 6 décimales". peux-tu m'en dire plus

Merci à vous pour votre aide.

rx.stephane

 

[rx.stephane]

Re : Calcul d'écart entre 2 dates et pénalités

renyeu,

Ta solution en faisant le calcul sur les heures résout les problèmes de résultat aléatoire pour les 30mn et 1h.
Le problème est qu'il peut y avoir des différences de temps sur 2 jours différents comme dans le cas de la ligne 12 et si B = 22/04/2014 10:00:00 et C = 23/04/2014 10:01:00 alors la pénalité est nulle alors quelle devrait être de 2000€.

Merci à toi.

rx.stephane

 

[ROGER2327]

Re : Calcul d'écart entre 2 dates et pénalités

Bonsoir à tous.

Un autre essai :

• En D6 :

  =SI((A6<>1)+ESTVIDE(B6);"";ARRONDI(C6-B6;5))

• En E6 :

  =SI(A6=1;CHOISIR(1+(D6<=ARRONDI($F$4;5))+(D6<=ARRONDI($G$4;5));2000;1000;0);"")

Bonne nuit.


ℝOGER2327
#7283

Samedi 7 Palotin 141 (Saint Fénéon ès Liens - fête Suprême Quarte)
7 Floréal An CCXXII, 9,1695h - muguet
2014-W17-6T22:00:24Z

 

[rx.stephane]

Re : Calcul d'écart entre 2 dates et pénalités

Bonjour ROGER2327,

BRAVO, MERCI, SUPER, c’est complètement ça !!!

Si tu as un petit moment, juste pour m’expliquer cette partie en rouge du format :
[h]:mm:ss;"*** C < B ***";@

Bonne journée.

rx.stephane

 

[ROGER2327]

Re : Calcul d'écart entre 2 dates et pénalités

Re...

(...) m’expliquer cette partie en rouge du format :
(...)

Faites comme moi : plongez-vous dans l'aide fournie par Microsoft. (Pas de scrupule à avoir, vous l'avez payée en achetant votre licence.)

Un petit coup de touche magique (i.e. F1), saisissez Formats et vous y trouverez, par exemple, cela :

​

Personnaliser le format de cellule, 1ère partie (2)...​

Bonne journée.


ℝOGER2327
#7284

Dimanche 8 Palotin 141 (Saint Bougrelas, prince - fête Suprême Tierce)
8 Floréal An CCXXII, 4,3207h - champignon
2014-W17-7T10:22:11Z