Pb convertion date excel Résolu

Question

Bonjour à tous,

J'ai un problème avec un de mes fichiers excel. Donc j'ai une date qui du style : mm/jj/aaaa hh:mm:ss pas de probleme jusqua à cette date 09/13/2007 00:00:43.

Je crois savoir d'ou vient le probleme. En fait pour excel cette date est sous cette forme jj/mm/aaaa hh:mm:ss et  il n'existe pas de 13 mois donc ce n'est pas une date.

J'ai deja essaye de faire format de cellule et de lui mettre mm/jj/aaaa hh:mm:ss mais rien n'y fait il ne change rien.
Je précise aussi que j'ai deja essayé de faire "données" puis "convertir" et de changer JMA en MJA mais cela n'a rein changé.

Merci d'avance

Vaucluse · Answer

Difficile de vous répondre avec ces éléments:
si c'est un problème chronique, vous pouvez passer par le panneau de configuration /
Langue et région / Options linguistiques pour modifier le format date.

Le plus difficile reste de savoir d'après vos info, ce qui est dans votre liste en date JJ/MM et MM/JJ car si le jour est inférieur à 12 Excel est surement induit en erreur.
On suppose que toute la liste est sur le même format
Si c'est un problème occasionnel mais complet sur une liste de date vous pouvez tenter la conversion
A toutes fins utiles:
Votre date 09/13/2007 00:00:43
Convertir / Séparateur / Autre et dans la case  rentrez /
vos données s'étalent sur A1,B1,C1 
en D1:
=DATEVAL(B1&"/"&A1&"/"&C1)
vous renverra la date "redressée", mais la date seulement, la suite étant négligé(à priori)dans la formule Dateval
pour avoir la valeur de l'horaire, il faut convertir une seconde fois sur la colonne C avec le séparateur blanc pour arriver sur C et D
les valeurs s'étalent alors sur a,b,c,d
la formule devient, en E1:
=DATEVAL(B1&"/"&A1&"/"&C1)+D1
Qui vous renverra:
13/9/2007 00:00:43
.
Crdlmnt

nicolelegio · Answer

Je vais tester ça dans un quart d'heure mais en ce qui concerne ce qu'il y a à l'intérieur de ma liste en fait ce fichier contient plus de 600000 lignes de date et heure et je voudrais changer le format qui est 
de jj/mm/aaaa hh:mm:ss en mm/jj/aaaa hh:mm:ss. au début les dates sont : 

09/03/2007 23:54:51 et où ca plante 09/13/2007 00:00:43 ce qui est normal car en fait excel comprend la deuxieme colonne comme la colonne des mois donc des le "13eme mois" il m'indique que ce n'est plus une date et donc je voudrais lui faire comprendre que la colonne des mois c'est la première.

En espérant que j'ai été plus clair cette fois ci ....

je teste votre solution et je vous répond d'ici 15 minutes environ

merci pour votre réponse

nicolelegio · Answer

J'ai essayé de faire ce que vous m'avez indiquer mais je n'ai pas compris certaines de vos étapes.
par exemple Convertir / Séparateur / Autre et dans la case rentrez /  dans cette case je peux mettre un seul caractère
Mais par contre j'ai essayé la première étape dans le panneau de config mais ça ne marche pas cela n'a rien changé.

Je vous ai joint un imprim.ecran pour que se soit plus clair de comment est formé mon fichier.

http://nsa11.casimages.com/img/2009/10/17/091017054643295492.jpg

J'ai oublié de préciser que je suis sous excel 2007 fr plus.

Vaucluse · Answer

Re
Vous n'avez donc que la partie basse du fichier à traiter
Il faut la copier sur une autre feuille, la corriger et la réintroduire après.
Seulement.... moi... je vous parle 2003, ce n'est peut être pas tout à fait pareil, et il y a peut être d'autres solutions .

Quant à convertir, si cela fonctionne de la même façon:
si vous avez A1:
9/13/2007 00:00:43
vous convertissez effectivement avec un seul / dans la case "autre," excel vous renvoie:
En A1: 9
En B1: 13
En C1: 2007 00:00:00
vous reprennez la colonne C1 et à nouveau convertir:, mais cette fois en choisissant l'espace.
la colonne C vous renvoie :2007 La colonne D vous renvoie 00:00:43
la formule: en E1=DATEVAL(B1&"/"&A1&"/"&C1)+D1 vous renvoie donc le regroupement soit:
9/13/2007 00:00:43
tout ça bien sur sur 2003... à voir sur 2007
Bonne chance

Vaucluse · Answer

PS
 en complément du message précédent, exemple sur une ligne au lien ci dessous:
http://www.cijoint.fr/cjlink.php?file=cj200910/cijYwi2ZAG.xls

REPS: je veins d'examiner de plus près la barre de formule dans votre image. La formule est DATEVAL avec un E et non pas DATAVAL
Crdlmnt

nicolelegio · Answer

ok je teste tout ça et je vous tien au courant 

merci encore de votre réponse

nicolelegio · Answer

je viens de tester et il me marque #VALEUR! je vous ai posté un sreen.

http://nsa11.casimages.com/img/2009/10/17/091017063143899464.jpg

Vaucluse · Answer

Normal
avec votre formule, vous remettez les valeurs dans leur ordre d'origine.Excel ne peut donc pas trouver de date.
essayez en inversant F et G dans votre formule!
Bonne chance

nicolelegio · Answer

je l'ai modifié et ça me donne pour le premier par exemple 00:00:43 il ne m'affiche pas la date 

merci encore de vos réponses

Vaucluse · Answer

Suite:
c'est ce qui se passe chez mloi quand la cellule est formatée en hh:mm:ss
formatez là en jj/mm/aaa_hh:mm:ss
A+
Ps si vous foramlez en Date, la cellule ne vous renvoie que la date
si vous formatez en heure, la cellule ne vous renvoie que les heures.
Il faut  les deux.

nicolelegio · Answer

Ça à l'air de marcher je vais continuer à faire tout ça pour le fichier et je vous re contact ce soir pour vous dire si ça marche ou si je rencontre d'autres problèmes.

ça faisait un moment que je séchait dessus

UN GRAND MERCI

Vaucluse · Answer

Eh bien très bonne soirée!
pour moi, ce sera demain matin!

nicolelegio · Answer

Pour l'instant ca marche rien à redire si ça marche jusqu'à demain je passerai ce poste en RESOLU

ENCORE UN GRAND MERCI A VOUS

Mike-31 · Answer

Salut tout le monde,

Peut être une formule directe, la date au format anglais 09/13/2007 00:00:43 en A1

mettre cette formule dans une cellule
=(DROITE(GAUCHE(A1;5);2)&"/"&GAUCHE(A1;2)&DROITE(A1;14))*1

et formater la cellule avec ce format personnalisé
jj/mm/aaaa hh:mm:ss

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Vaucluse · Answer

Bonjour Mike
J'avais hier cette solution près de la tienne,   qui avait l'air de marcher, mais je l'ai gardé car elle exige que le format du mois au début de la date comporte deux chiffres te ce ne serait pas le cas d'après les tableaux transmis.
=DATEVAL(STXT(A1;4;2)&"/"&(GAUCHE(A1;2)&"/"&STXT(A1;7;4)))+DROITE(A1;8)*1
Elle est près de la tienne car je pense que ta proposition a besoin de quelques ajustements dans les nombres de caractères(par exemple GAUCHE(A1;14) renvoie /2007 00:00:43 et donc ne peut pas être multiplié par 1
Par ailleurs je ne pense pas que tu puisses inclure la date et l'heure dans le même item avec slash et accessoires.Ca ne marche pas sur 2003
Mais c'est essentiellement pour ne pa perturber la conversion selon le nombre de chiffre avdu jour et du mois que j'ai gardé cette proposition pour moi.
Enfin, on peut trouver une formule identique qui détaille avnt le 1° slash, ce qu'il y a entre les deux, et l'année, mais la c'est un peu plus complexe
je te la donne ci dessous:
=STXT(A1;TROUVE("/";A1;1)+1;TROUVE("/";A1;1)+2-TROUVE("/";A1;1))&"/"&STXT(A1;1;TROUVE("/";A1;1)-1)&"/"&STXT(A1;TROUVE("/";A1;TROUVE("/";A1;1)+1)+1;4)&" "&+DROITE(A1;8)*1
et encore avec ça excel sur un format jj/mm/aa hh:mm:ss me renvoi:
 13/09/2007 0,000497685185185185 impossible à formater
Excel 2003 je précise.
Bon dimanche, bon casse tête
Bien amicalement

Mike-31 · Answer

Salut mon ami,

Au saut du lit, j'adore les défis, je suis sur 2003 et pour régler le problème des mois qui pourrait être exprimé aléatoirement avec un ou deux caractères
je propose

=SI(A1<>"";SI(TROUVE("/";A1;1)=2;(DROITE(GAUCHE(A1;4);2)&"/"&GAUCHE(A1;1)&DROITE(A1;14))*1;(DROITE(GAUCHE(A1;5);2)&"/"&GAUCHE(A1;2)&DROITE(A1;14))*1);"")

il est possible d'avoir le même raisonnement pour les jours, combiné au mois exemple 1/1/2009 ou 01/01/2009 etc ... et la même chose pour les années 09

avec une formule usine à gaz on doit y arriver pour l'apéro

Bon dimanche

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Vaucluse · Answer

Bonjour Mike
Je crois que le problème majeur n'est pas de savoir si le jour et le mois ont un ou deux chiffres,mais d'expliquer à excel que lorsque le jour (au milieu ) est inférieur à 12, il ne s'agit pas d'une date standard. 
Car dans un exemple tel que:
9/12/2007 00:00:53
excel prend cela comme une date standard et ne travaille plus sur le format affiché, mais sur le format numérique de la date ce qui fout en l'air toutes les info des formules sur la position des caractères et ce qui en ressort.
Bon courage;
je n'insiste pas plus pour ce qui me concerne compte tenu de ma conclusion ci dessus mais rassure toi je boirais l'apèro quand même (aujourd'hui c'est dimanche) en pensant à toi.
Bien amicalement

Mike-31 · Answer

Re,

Dans ce genre de discussion vaut mieux avoir une vue externe, et je compte sur toi pour relever les disfonctionnements

testes cette formule qui systématiquement met la cellule en format texte, le problème est de savoir s'il peut y avoir dans une colonne des format anglais et des format standard avec des mois inférieurs à 12 et là effectivement le problème reste entier, sauf si les données en format Anglais sont déjà en format texte

=(DROITE(GAUCHE(TEXTE(A5;"jj/mm/aaaa hh:mm:ss");5);2)&"/"&GAUCHE(TEXTE(A5;"jj/mm/aaaa hh:mm:ss");2)&DROITE(TEXTE(A5;"jj/mm/aaaa hh:mm:ss");14))*1

Les glaçons sont prêt, le verre aussi et le bonhomme n'en parlons pas 

Tchin tchin !

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Vaucluse · Answer

Re
Je ne sais pas chez toi, ( mais chez moi il y a aussi l'apéro) mais sur mon test, ta formule marche quand la date du jour, au milieu donc, est inférieure ou égale à 12, mais au dela, elle renvoie Valeur.
Parce que à mon sens, là tu as le problème contraire, tu tentes de transformer l'info en date, mais excel n'avale plus quand le mois est supèrieur à 12
Sous toutes réserves.
A la tienne
Crdlmnt

nicolelegio · Answer

Après lecture de vos postes, je dois en déduire qu'il n'y a pas d'autres techniques plus rapide ?

Sinon la méthode de Vaucluse est fonctionnel mais elle prend du temps sur tout que j'ai 50 fichiers à faire.
Si vous avez une méthode plus rapide et fonctionnel je suis preneur !


Merci encore à vous tous de vos réponses

Mike-31 · Answer

Re,

Ah ça va mieux les idées sont de suite plus claires, apparemment cette formule prend en compte les mois inférieurs à 12 et inversement à contrôler !

=SI(ESTTEXTE(A1);SI(ESTERREUR((DROITE(GAUCHE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");5);2)&"/"&GAUCHE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");2)&DROITE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");14))*1);(DROITE(GAUCHE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");5);2)&"/"&GAUCHE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");2)&DROITE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");15))*1;(DROITE(GAUCHE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");5);2)&"/"&GAUCHE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");2)&DROITE(TEXTE(A1;"jj/mm/aaaa hh:mm:ss");14))*1);A1)


Souvent les formules complexes en passant par le forum sont parasitées par des symboles ou des intervalles s’ajoutent, un fichier pour tester avec ce lien il sera possible d'alléger la formule

https://www.cjoint.com/?ksplqNhSIR

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Vaucluse · Answer

Effectivement l'apèro te fait du bien.... et je salue la performance et la patience pour écrire un truc pareil.
Ca marche... mais il faut bien que les dates ne comportant qu'un seul chiffre avant le premier slash soit affecté d'un 0 pour faire 2 caractères.
Bravo

Reste toujours la même question pour le demandeur:
8/10/ ...... est il le 10 Aout ou le 8 Octobre?
la formule décide elle même suivant l'affichage (<12 ou <>12) au 2° Item si le mois doit être issu du premier item ou du deuxiéme et c'est particulièrement performant, mais comment faire pour savoir dans une liste ce qu'il faut décider sur le sujet.

Cela marche bien si le fait que le second item est >12 suffit pour déterminer le format initial.Si cela est le cas, au 0 près à rajouter, par endroit c'est parfait.

Ceci dit, c'est quand même monumental!, dans tous les sens du terme.

A+

Mike-31 · Answer

Re,

avec deux conditionnelles je pense que l'on doit pouvoir contenir ce problème, et éventuellement mettre la formule en cascade afin qu'elle soit facilement interprétable 

Bon dimanche

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

nicolelegio · Answer

"Reste toujours la même question pour le demandeur:
8/10/ ...... est il le 10 Aout ou le 8 Octobre? "

le 8/10/2007 est bien le 10 Aout

Mike-31 · Answer

Re,

et le tes dates ont elles toujours le même format exemple 08/10/2009 ou peut il y avoir le format 8/10/2009 ou encore 8/10/09 pout le 10 août voir 8/9/09 pour le 9 août 09

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Pb convertion date excel

25 réponses

Votre réponse

Newsletters