Calcul de différences entre 2 formats complexes
Bonjour à toutes et tous,
Je suis tout nouveau sur ce forum, pour habitude je consulte simplement les autres pages pour trouver réponses à mes questions mais là je bloque sur toute la ligne.
Sapeur Pompier de profession, mon chef de service m'a chargé de calculer les délais de départ des engins entre l'heure de l'alerte et l'heure de départ du véhicule d'intervention. Jusque là rien de bien compliqué, je pensais qu'une simple soustraction entre ma cellule par exemple A1 (heure d'alerte) et B1 (heure de départ) pour me donner le délai entre les deux. Mais cela était trop simple.
Le souci est que la requête de notre serveur me donne le format de la cellule en (année, mois, jour, heure, minutes, secondes, centièmes de seconde) soit par exemple : 2015-01-01 12:05:19:000. (tout cela dans le même cellule).
Si en A1 j'ai 2015-01-01 12:05:19:000
et en B1 j'ai 2015-01-01 12:08:35:000
je voudrais connaître en C1 que mon engins à mis 00:03:16 à partir.
hors afin de faire cela par B1-A1 il me faut remettre mes cellules en
A1 12:05:19
B1 12:08:35
= C1 00:03:16
pour arriver à cela il me faut modifier environ 100 000 format de cellule pour supprimer ce marquer en gras 2015-01-01 12:05:19:000
Ma ou plutôt mes questions sont les suivantes:
- Existe-t-il un formule qui me permet d'avoir ce même résultat sans modifier mes formats de cellules comme noter ci-dessus ?
ou
- Existe-t-il un moyen de faire supprimes l'ensemble des caractère que je ne souhaite pas avoir dans les cellules pour arrivée au format hh:min:sec sans modifier les cellules une par une.
Merci d'avance à toutes celles et ceux qui prendrons le temps de se pencher sur mon problème.
Dans l'attente.
Bonjour Bruno !
Il te suffit d'appliquer la formule suivante en C1 :
=STXT(B1;12;8)-STXT(A1;12;8)
Et Magie
La fonction STXT() permet d'isoler une partie des caractères de ta chaine de caractères.
Ici pour la cellule A1, on pourrait traduire la formule comme ça :
=ISOLE LE TEXTE (DE LA CELLULE A1 ; COMMENCE AU 12è CARACTERE ; DONNE MOI LES 8 CARACTERES A PARTIR DU 12è)
Les 8 caractères à partir du 12ème caractère correspondent en effet à la partie "heures" de ta date-heure.
Bon courage et MERCI les pompiers !
Génial!!! Merci beaucoup GazOline,
Chose n'est pas courante mais tu as sauvé "la vie" d'un pompier !!! n'exagérons pas, disons que tu me l'a superbement simplifiée.
C'est super, je me voyais déjà traiter les cellule une à une, grrrrrr.
Il ne m'a fallu que 3 minutes pour recopier la formule et l'étendre sur 50 000 ligne de ma requête serveur, presque trop simple pour moi maintenant lol.
Par contre je rencontre un nouveau souci, j'aperçois des case qui n'accepte pas la formule, et oui évidement le changement de date!!!! quelle idée de faire sortir les pompier entre 23:59 et 00:01, je rigole bien évidement le devoir avant tout.
En effet je remarque cela :
heure de l'alerte : 2014-12-23 23:55:13.000 heure de départ : 2014-12-24 00:02:31.000 la fonction ne prend pas en compte et l'heure de départ se retrouve < à l'heure de l'alerte j'ai bien essayer d'y ajouter un -24:00:00 par ci par là mais rien.
A la fois cela me fait que 251 lignes à reprendre au lieu de 100 000 une bagatelle !!!
Crois tu que tu pourrais arriver à résoudre également ce problème pour prendre en compte le passage d'un jour à un autre ?
Merci d'avance et merci encore pour cette aide que tu m'a apporté GazOline.
bruno89 a écrit :heure de l'alerte : 2014-12-23 23:55:13.000 heure de départ : 2014-12-24 00:02:31.000 la fonction ne prend pas en compte et l'heure de départ se retrouve < à l'heure de l'alerte j'ai bien essayer d'y ajouter un -24:00:00 par ci par là mais rien.
Lorsqu'il y a changement de jour, pour calculer la différence d'heure il faut alors simplement ajouter le jour correspondant à chaque "pointage". Ci-joint un fichier explicatif
Les heures comme les jours sont des nombres pour excel, entier pour les jours et décimaux pour les heures. Donc cela s'additionne et cela se soustrait.
Rebonjour Bruno !
Voici la formule qui prends en compte cet aspect
=(STXT(B1;9;2)&"/"&STXT(B1;6;2)&"/"&STXT(B1;1;4)&" "&STXT(B1;12;8))-(STXT(A1;9;2)&"/"&STXT(A1;6;2)&"/"&STXT(A1;1;4)&" "&STXT(A1;12;8))
Cette formule reconstitue la date "2015-01-01" en un format qu'Excel peut comprendre, à savoir "21/01/2015".
Du coup le calcul prends en compte la date et l'heure dans le calcul, même pas besoin de reprendre tes 250 lignes à la main
Pour une fois qu'on peut rendre service à un pompier, on va pas se priver
En C1 tu peux donc mettre
=STXT(B1;12;8)+(STXT(B1;9;2)&"/"&STXT(B1;6;2)&"/"&STXT(B1;1;4))*1-STXT(A1;12;8)-(STXT(A1;9;2)&"/"&STXT(A1;6;2)&"/"&STXT(A1;1;4))*1... sans objet, Gazoline a aussi répondu une minute auparavant !
Bonjour,
Gaz0line a écrit :Cette formule reconstitue la date "2015-01-01" en un format qu'Excel peut comprendre, à savoir "21/01/2015".
Cela veut-il dire que Excel ne reconnait pas le format ISO pour les dates & heures ?
Avec Calc :
=GAUCHE(B1;19)-GAUCHE(A1;19)A+
OOoiste a écrit :Bonjour,
Gaz0line a écrit :Cette formule reconstitue la date "2015-01-01" en un format qu'Excel peut comprendre, à savoir "21/01/2015".
Cela veut-il dire que Excel ne reconnait pas le format ISO pour les dates & heures ?
Avec Calc :
=GAUCHE(B1;19)-GAUCHE(A1;19)A+
OOoiste : Eh bien figure-toi que si ! Merci à toi car du coup j'ai testé et si si ça marche ^^ Donc...
Bruno on arrête tout y a une formule vachement plus simple !!
=STXT(B1;1;19)-STXT(A1;1;19)
Tadaaaam
PS : ou bien la formule de OOoiste, qui marche également ! --> =GAUCHE(B1;19)-GAUCHE(A1;19)
Tous les chemins mènent à Rome !
hé bien voilà, merci beaucoup car là j'ai aussi appris quelquechose !
Bonjour et merci, merci, merci à tous.
Je suis sur les fesses de voir autant de personnes qui s'active à essayer de régler mes petits soucis de néophyte en Excel. En effet c'est pour moi la première fois que j'interviens sur un forum (tout type confondus). Ce qui m'impressionne le plus c'est les formules de "dingues" que vous me sortez en 2 coups de cuillère à pot.
Je vous avoue que les formules de Steelson et GazOline m'ont fait un peut peur, mais une fois insérer dans mon tableau tout paraît si simple...
Je ne resterais pas sur la simplicité et je vais apprendre à déchiffrer ces formules magiques pour devenir moi aussi un "AS" .
Bravo "OOoiste" plus simple, cela fais moins peur et tout aussi efficace.
Etant entouré de "Ténor", j'abuserais encore de votre disponibilité, de votre gentillesse et surtout votre savoir. Vous comprendrez que tout ces résultats sont destinés à calculer la moyenne de délai de départ (Et oui c'est pas en roulant comme des fous sur la route que l'on gagne du temps mais plus en courant dans les couloirs des casernes!!!).
Une fois mes délais calculés avec les formules, il m'est impossible de faire un moyenne, voir en pièce jointe les délai de départ d'un VSAV (Véhicule d'Assistance Aux Victimes) sur une année. Même quand j'étend ma formule sur 2 cellules cela me donne un moyenne dérisoire.
Pourquoi ?
Merci beaucoup
Oups j'ai réussis, j'avais pas vu que 2 cellules ne contenait pas les bonnes données.
Désolé.
Cela donne un moyenne de 3min11sec.
Vous m'avez donné le tournis avec toutes ces formules !!!
3mn11s ... CHAMPIONS
Eh bien 3min11 c'est pas mal du tout ! j'espère que ton chef sera content
Je t'explique ma formule :
STXT comme je l'a dit permet d'extraire une partie de ta donnée contenue dans une cellule.
Exemple avec des lettres, c'est moins embrouillant que des chiffres :
Imagine en A1 la phrase "Je suis pompier"
En B1, je veux récupérer uniquement le mot "pompier", j'applique la formule suivante :
=STXT(A1;9;7)Cette formule dit "dans ma chaine de caractères en A1, commence au 9ème caractère, et donne-moi les 7 caractères suivants.
Le 9ème caractère dans "Je suis pompier" est le "p" et les 7 caractères à partir du 9ème sont bien "pompier".
Imagine, je veux récupérer le mot "Je" et le mot "pompier"
Je vais donc écrire :
=STXT(A1;1;2) & STXT(A1;9;7)le premier "STXT" correspond à "Je" (je récupère 2 caractères à partir du 1er)
le second "STXT" correspond au mot "pompier" (je récupère 7 caractères à partir du 9ème)
Tu peux voir qu'entre les deux j'ai mis un signe &
Ce signe permet d'associer les deux éléments. Je ne veux pas une addition ou une soustraction, je veux que les deux mots apparaissent côte à côte. Je mets donc un &.
Ma formule va donc donner comme résultat : "Jepompier"
Oui mais ! Je voulais un tiret entre les deux mots ! alors au lieu de mettre simplement &, je vais mettre &"-"&. (les morceaux qui ne sont pas des formules sont à mettre entre des guillemets, je mets donc mon tiret entre deux guillemets).
J'ai maintenant 3 éléments : la partie de ma formule qui me donne "Je", la partie de ma formule qui me donne un tirer, la partie de ma formule qui me donne "pompier", tu vois qu'entre chacune de ces éléments il y a un & pour les relier.
=STXT(A1;1;2) &"-"& STXT(A1;9;7)Cela donnera : "Je-pompier"
Avec tes dates, j'ai fait la même chose, j'ai récupéré le jour, le mois, et l'année. Et pour lier tout ça, j'ai mis &"/"& entre chaque élément. Ainsi on a :
=STXT(A1;9;2) <--- Le jour (2 caractères à partir du 9ème caractère)
&"/"& <--- le séparateur
STXT(A1;6;2)<--- Le mois (2 caractères à partir du 6ème caractère)
&"/"&<--- le séparateur
STXT(A1;1;4)<--- L'année (4 caractères à partir du 1er caractère)
&" "&<--- le séparateur (cette fois c'est un espace !)
STXT(A1;12;8) <--- l'heure sans les centièmes (8 caractères à partir du 12ème caractère)
Voilà j'espère que cela t'aidera à comprendre
oups, bug !
Merci je vais me pencher dessus à tête reposé pour étudier et enfin comprendre.
Une dernière chose dans la pièce jointe je souhaiterais extraire de ma colonne "C" le créneaux horaire en format hh:mm dans ma colonne "F" afin de filtrer par la suite en ordre croissant et mettre en colonne "F" le format horaire arrondis en colonne "G" pour afin connaitre combien d'interventions je réalise entre 00:00 et 1:00, 1:00 et 2:00 ...
Ensuite j'étire et je peut connaitre la sollicitation horaire de mon département, Centre de Secours ou Engins avec cette fois ci un simple tableaux croisé dynamique.
Merci
Eh bien en F2 tu peux mettre la formule :
=STXT(C2;12;8)et en G2, la formule suivante te permettra d'extraire l'heure concernée. cette formule te donne le résultat "04"
=STXT(F2;1;2)Si tu tiens absolument au format "00:00", voici une formule en G2 qui te donnera le résultat "04:00"
=TEXTE(STXT(F2;1;2)*100;"00:00")*1(le *1 permet de repasser d'un format "texte" à un fomat numérique, comme ça tu peux refaire des calculs dessus si besoin)
Je te joints un fichier
bruno89 a écrit :Une dernière chose dans la pièce jointe je souhaiterais extraire de ma colonne "C" le créneaux horaire en format hh:mm dans ma colonne "F" afin de filtrer par la suite en ordre croissant et mettre en colonne "F" le format horaire arrondis en colonne "G" pour afin connaitre combien d'interventions je réalise entre 00:00 et 1:00, 1:00 et 2:00 ...
Au passage ... une fois l'horodatage extrait comme on a vu ci-avant, pour avoir le jour, il suffit de prendre la partie entière. Et pour l'heure, la partie décimale.
Je viens juste de voir ton fichier.
Tu écrivais dans ton premier message :
bruno89 a écrit :Si en A1 j'ai 2015-01-01 12:05:19 : 000
et en B1 j'ai 2015-01-01 12:08:35:000
je voudrais connaître en C1 que mon engins à mis 00:03:16 à partir.
alors que tu as 2015-01-01 12:05:19 . 000
Comme quoi c'est toujours mieux de joindre un fichier dès le début.
Oublies toutes les formules et sélectionnes ta colonne de dates-temps,
puis dans l'onglet Données, cliques sur convertir.
Dans l'assistant, à l’étape 3, cliques sur "avancé" et choisis le séparateur décimal . (point)
Tes données seront alors transformés en vraies dates.
Bonjour,
Si tu as bien 2015-01-01 12:05:19.000 (et non 2015-01-01 12:05:19:000 comme indiqué au 1er message) excel reconnait ce format
=D2-C2 suffit
Il y a juste le format heure à mettre sur la colonne résultat.
Et pour t'affranchir du pb de changement de date :
=mod(D2-C2;1)
eric
PS: je n'avais pas vu la 2nde page de réponse, ooiste avait déjà relevé. Mais la conversion n'est pas nécessaire.
Bonjour Eriiic !
Eh bien figure-toi que non ! J'ai repris le dernier fichier envoyé par Bruno89, j'ai fait D2-C2, et ça me donne #VALEUR.
Il faut vraiment isoler la donnée sans les centièmes, comme le fait la formule la plus simple des propositions, à savoir celle de OOoiste : =GAUCHE(D2;19)-GAUCHE(C2;19)
Bonjour,
Eh bien figure-toi que non !
Si si...
C'est parce que tu dois avoir la ", " en séparateur décimal sur ton système. Changer seulement ce caractère sur la plage est suffisant et largement plus rapide à faire que des formules de traitement de chaine. Et plus rapide en exécution
(ou bien changer le séparateur décimal dans excel, ou le panneau de config)
Oooiste proposait la même chose sous une autre forme.
Et =GAUCHE(D2;19)-GAUCHE(C2;19) ne traite pas correctement les délais à cheval sur 2 dates.
=mod(D2-C2;1) oui. Plus complet et plus simple encore à mon avis
eric