Discussion :

[facteur]

Bonjour a tous et toute,

Je travaille dans un service transport.
Mon support de travail est un tableau excel où est indiqué les heures prévues d'arrivée et de départ des remorques et les heures réelles d'arrivée et de départ de ses remorques.

Exemple :

Remorque 1
Heure d'arrivée prévue : 22h00
Heure d'arrivée réelle : 21h00
Ecart = + 1 (remorque en avance d'1 heure)

Remorque 2
Heure d'arrivée prévue : 21h00
Heure d'arrivée réelle : 1h00 le jour suivant
Ecart = +4h00 (remorque en retard de 4 heures)

Remorque 3
Heure d'arrivée prévue : 2h00
Heure d'arrivée réelle : 23h30 le jour précédent
Ecart = +2h30 (remorque en avance de 2h30)

Remorque4
Heure d'arrivée prévue : 2h00
Heure d'arrivée réelle : nc (Non Communique)
Liaison non effectuée

A partir de ces infos, je recherche la formule pour calculer le retard entre l'heure réelle et l'heure prévue en sachant que la plupart des camions arrivent la nuit et que les heures peuvent être à cheval sur deux jours.

Mon souhait est de mettre une formule qui me dit : "Si la remorque est en retard de plus de 20 minutes, le résultat est égal à 1 sinon résultat = 0".

J'ai bien des formules conditionnelles qui calculent ces retards mais celles ci ne fonctionnent pas lorsque l'heure réelle se trouve dans la journée du lendemain (exemple du camion 2) ou dans la journée précédente (exemple du camion 3).

La difficulté est de trouver une formule qui fonctionne aussi bien avec des heures réelles et prévues insérées dans une même journée que des heures réelles et prévues à cheval sur deux journées. Choses que je n'arrive pas.

La formule qui prend en compte la différence d’heure de 24 heures fonctionne uniquement dans certaines situations :
Formule :SI(G3="nc";0;G3-F3+SI(G3<F3;1))
G3= heure réelle
F3 = heure prévue

Remorque 2
Heure d'arrivée prévue (F3) : 21h00
Heure d'arrivée réelle (G3) : 1h00 le jour suivant
La formule fonctionne bien : retard de 4heures

Mais cette formule ne fonctionne plus dans les autres cas :
Remorque 3
Heure d'arrivée prévue (F3) : 2h00
Heure d'arrivée réelle (G3) : 23h30 le jour précédent
La formule m'indique que la remorque est en retard de 21h30 alors qu'elle est en avance de de 2h30

Remorque 1
Heure d'arrivée prévue (F3) : 22h00
Heure d'arrivée réelle (G3) : 21h00
La formule m'indique que la remorque est en retard de 23h00 alors qu'elle est en avance de de 1h00

je suis perdu !!!!!!!!

Merci pour votre aide !

PS : je vous joins quelques lignes de mon tableau afin que vous voyiez mes formules de calcul concernant les retards et que vous ayez une vision plus claire du problème...
Sur ce tableau, j'ai masqué quelques colonnes afin d'alleger le tableau
La cellule A1 contient la date du début des liaisons des remorques
La colonne AH a été rajouté pour les besoins de ma requete (elle n'existe pas sur mon tableau original)

[marc56]

Bonjour,
Dans Excel, une journée de 24h est égale à 1. Donc si l'heure d'arrivée est inférieure à l'heure de départ, c'est donc qu'il y a changement de jour, ce dont tu ne tiens pas compte dans ton tableau entre les heures prévues et les heures réelles. Regarde mon exemple joint.
Dans ton cas, le plus simple serait peut-être d'indiquer dans ton tableau la date du jour prévu et du jour réel.

[facteur]

Avant tout , merci d em'aider marc.

Ta formule fonctionne mais elle presente deux anomalies:
Pour bien comprendre le probleme, j'ai t'ai renvoyé ton tableay en remplacant : debut par "heure prévue" et fin par " heure réelle".
SI sur une meme journee ; l'heure réelle est inferieur à l'heure prévue ..la formule m'indique un retard alors que c'est une avance.
Sur des journées à cheval; si l'heure réelle est infereieur à l'heure prévue..l

Bonjour,
Dans Excel, une journée de 24h est égale à 1. Donc si l'heure d'arrivée est inférieure à l'heure de départ, c'est donc qu'il y a changement de jour, ce dont tu ne tiens pas compte dans ton tableau entre les heures prévues et les heures réelles. Regarde mon exemple joint.
Dans ton cas, le plus simple serait peut-être d'indiquer dans ton tableau la date du jour prévu et du jour réel.

[facteur]

Sur des journées à cheval; si l'heure réelle est inferieur à l'heure prévue..la formule m'indique également un retard alors que c'est une avance ...
pas si simple que ca....

je te joins ton tableau avec les deux cas qui posent problème

Avant tout , merci d em'aider marc.

Ta formule fonctionne mais elle presente deux anomalies:
Pour bien comprendre le probleme, j'ai t'ai renvoyé ton tableay en remplacant : debut par "heure prévue" et fin par " heure réelle".
SI sur une meme journee ; l'heure réelle est inferieur à l'heure prévue ..la formule m'indique un retard alors que c'est une avance.
Sur des journées à cheval; si l'heure réelle est infereieur à l'heure prévue..l

[marc56]

Je te propose ceci comme solution et c'est plus parlant que des heures négatives non gérées par Excel et donc bricolées.
En jaune en cas de retard mais sans dépasser les 20 minutes, en rouge au delà.

[facteur]

J'exploite ta formule marc et je te tiens au courant si cette formule fonctionne sur mon tableau excel..
Et merci encore.....

Je te propose ceci comme solution et c'est plus parlant que des heures négatives non gérées par Excel et donc bricolées.
En jaune en cas de retard mais sans dépasser les 20 minutes, en rouge au delà.

[MaxPCK]

Bonjour à tous,

J’essaye d’expliquer au mieux mon problème :

A partir des heures universelles (heure TU), Je dois ajouter ou soustraire le décalage horaire pour obtenir les heures en locales.

Je sais additionner et soustraire les heures sur Excel par contre je n’arrive pas à soustraire des heures quand il y a un changement de jour.
Comme par exemple 01h00 avec un décalage horaire de - 2h devrait afficher 23h00, Excel affiche ########

Merci d'avance pour votre aide,
Maxence.

Heure excel.xlsx

[Philippe Tulliez]

Bonjour,

Je sais additionner et soustraire les heures sur Excel par contre je n’arrive pas à soustraire des heures quand il y a un changement de jour.
Comme par exemple 01h00 avec un décalage horaire de - 2h devrait afficher 23h00, Excel affiche ########

Effectivement Excel ne peut afficher les heures négatives mais il y a des astuces pour y palier
Voir mon billet titré Comment afficher les heures négatives dans Excel