[RÉSOLU] Date inversé et aussi, dans un format en dur qui résiste.

[Gen Rose]

[Je n'ai pas trouvé un meilleur titre, sérieusement]

Bonjour le Forum

Ok, alors des tonnes de fils sur le sujet et j'ai trouvé 2 formules à Tibo qui semblait être sur tous ces fils (clairement, il a eu ce problème et s'est lancé à la rescousse d'autres âmes sur le forum).

J'ai donc retenu une de ses formules sur ce fil:

=SI(ESTNUM(A3);DATE(ANNEE(A3);JOUR(A3);MOIS(A3)))

qui fonctionne pour la très grande majorité de mes dates sauf, pour celles qui sont écrites ainsi:

5/21/2009

J'ai tenté un Rechercher/Remplacer les " / " par "-" mais sans succès. Je dit que ces dates sont dans un format en dur car lorsque l'on tente d'en modifier le Format de cellule, peu importe ce que l'on choisi, rien ne bouge dans l'aperçu.

J'ai à traiter 47 045 précisemment et je suis certaine que l'un d'entre vous sait exactement quoi faire...c'est peut-être même anodin comme problème!

Un GROS merci à l'avance.
Je vous aimes!

 

[Gen Rose]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Ces données proviennent d'une timbreuse...et je crois avoir trouvé les fichiers originaux...
Ok, je vous reviens avec dans pas long.

 

[Gen Rose]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Enfin, voici l'original : ci-joint

 

[Efgé]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonjour à toutes , tous
Si le VBA n'est pas "non grata":

Sub test()
Dim i&
With Sheets("Valeur_1900_2014_2015")
    For i = 2 To .Cells(.Rows.Count, 1).End(3).Row
        .Cells(i, 1) = .Cells(i, 1).Formula
    Next i
End With
End Sub

Cordialement

EDIT Cela semble fonctionner sur le fichier "Original"

 

[Gen Rose]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonjour Efgé,

Cela fonctionne très bien sur le fichier original.

Comment adapter cette formule sur mon autre fichier si la colonne à traiter est en C (3e colonne)?

 

[Efgé]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Re
Bonsoir genevieve
.CELLS(Ligne, Colonne)

.Cells(.Rows.Count, 1).End(3) est la dernière cellule remplie en colonne 1 en partant de la dernière ligne (.Rows.Count) et en remontant vers le haut ( (3) = même chose que Xlup)

Il suffit de changer le 1 par le numéro de colonne utilisée ;-) , ainsi que dans les autres .Cells

Cordialement

 

[job75]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Re Geneviève, hello Efgé,

Sur ton fichier du post #32 il est clair que toutes les dates, textes ou nombres, sont inversées.

Donc utilise cette macro :

Sub Convertir()
Dim P As Range, tablo, i&, t$, v1$, v2$, v3$
Set P = Range("A2", Range("A" & Rows.Count).End(xlUp))
If P.Row = 1 Or P.Count = 1 Then Exit Sub 'sécurité
tablo = P 'matrice plus rapide
For i = 1 To UBound(tablo)
  t = P(i).Text
  v1 = Val(t)
  v2 = Val(Mid(t, InStr(t, "/") + 1))
  v3 = Mid(t, InStrRev(t, "/"))
  tablo(i, 1) = CDate(v2 & "/" & v1 & v3)
Next
P.Offset(, 1).Resize(UBound(tablo)) = tablo
End Sub

Ton fichier joint.

A+

 

[ROGER2327]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Suite...

Dans la foulée de job75 et un gros poil plus rapide...​

Sub Convertir()
Dim DtHr(), Conv(), i&, n&, s

    With [A1] 'Entête de la colonne de données

        n = .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp).Row
        If n > .Row Then
            DtHr = .Parent.Range(.Cells, .Parent.Cells(n, .Column)).Value
            n = n + 1 - .Row
            ReDim Conv(2 To n, 0)
            On Error Resume Next
            For i = 2 To n
                s = Split(DtHr(i, 1), "/")
                Conv(i, 0) = CDate(Join(Array(s(1), s(0), s(2)), "/"))
            Next

            With .Offset(1, 1)
                .Resize(n - 1, 1).Value = Conv
                .EntireColumn.AutoFit
            End With

        End If

    End With

End Sub

(Les secondes sont conservées.)​

ℝOGER2327
#7252

Mercredi 25 Clinamen 141 (Exit Saint Goya, alchimiste - fête Suprême Quarte)
27 Germinal An CCXXII, 0,2613h - anémone
2014-W16-3T00:37:37Z

 

[job75]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonjour Geneviève, Roger, le forum,

Oui ma macro était trop lente parce que j'allais chercher - inutilement - P(i).Text.

Celle-ci est un tout petit poil plus rapide que celle de Roger :

Sub Convertir()
Dim tablo, i&, t$, v1$, v2$, v3$
tablo = Range("A1", Range("A" & Rows.Count).End(xlUp)(2))
For i = 2 To UBound(tablo) - 1
  t = tablo(i, 1)
  v1 = Val(t)
  v2 = Val(Mid(t, InStr(t, "/") + 1))
  v3 = Mid(t, InStrRev(t, "/") + 1)
  t = v2 & "/" & v1 & "/" & v3
  tablo(i, 1) = CDate(t) 'pour vérifier toutes les lignes
  'If IsDate(t) Then tablo(i, 1) = CDate(t) 'évite les erreurs
Next
'---restitution---
tablo(1, 1) = [B1]
[B1].Resize(UBound(tablo)) = tablo
End Sub

J'ai aussi ajouté 2 choses :

- avec End(xlUp)(2) on est sûr que le tableau contient au moins 2 éléments

- avec le test If IsDate(t) Then on évite les erreurs, mais ne pas l'utiliser si l'on veut vérifier toutes les lignes.

Fichier (2)

A+

 

[job75]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Re,

Une solution par formule avec en B2 :

=SI(ESTNUM(A2);DATE(ANNEE(A2);JOUR(A2);MOIS(A2))+MOD(A2;1);--(STXT(REPT(0;ESTERR(-GAUCHE(A2;2)))&A2;4;3)&GAUCHE(REPT(0;ESTERR(-GAUCHE(A2;2)))&A2;3)&STXT(REPT(0;ESTERR(-GAUCHE(A2;2)))&A2;7;20)))

Fichier joint.

A+

 

[job75]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Re,

Du coup la solution par formule m'inspire cette macro, plus simple et un peu plus rapide :

Sub Convertir()
Dim tablo, i&, t$, v1$, v2$, v3$
tablo = Range("A1", Range("A" & Rows.Count).End(xlUp)(2))
For i = 2 To UBound(tablo) - 1
  t = tablo(i, 1)
  If Not IsNumeric(Left(t, 2)) Then t = 0 & t
  v1 = Left(t, 3)
  v2 = Mid(t, 4, 3)
  v3 = Mid(t, 7)
  t = v2 & v1 & v3
  tablo(i, 1) = CDate(t) 'pour vérifier toutes les lignes
  'If IsDate(t) Then tablo(i, 1) = CDate(t) 'évite les erreurs
Next
'---restitution---
tablo(1, 1) = [B1]
[B1].Resize(UBound(tablo)) = tablo
End Sub

Fichier (3).

A+

 

[Modeste geedee]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonsour®

Enfin, voici l'original : ci-joint

il aurait été préférable un fichier original c'est à dire un fichier avant import dans EXCEL (.csv, .Txt, .pic,.dat, .log etc...)

car comme déjà dit, EXCEL effectue "by design"(sans intervention) une interprétation automatique des données ressemblant à des dates...
ex : 05/02/2014
sera interprété :
2 mai 2014 ou 5 Fév 2014
selon les options locales par défaut (US ou FR, MJA#JMA)

si la mise en forme initiale est incompatible avec le format local (mois >12 ou bien jour >31)
alors la date n'est pas traduite et reste en format standard (Texte : alignement à gauche, date ou nombre : alignement à droite)

on se retrouve alors avec des données sous différents format !!!

 

[ROGER2327]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonjour à tous.

(...)

il aurait été préférable un fichier original c'est à dire un fichier avant import dans EXCEL (.csv, .Txt, .pic,.dat, .log etc...)

(...)

En attendant, une formule qui semble donner le même résultat que les procédures de #37 et #40 :​

=SI(ESTNUM(A2);SI(JOUR(A2)>12;A2;DATE(ANNEE(A2);JOUR(A2);MOIS(A2))+MOD(A2;1));
DATE(DROITE(GAUCHE(A2;TROUVE(" ";A2&" ")-1);4);
GAUCHE(A2;TROUVE("/";A2)-1);
SUBSTITUE(STXT(A2;NBCAR(GAUCHE(A2;TROUVE(" ";A2&" ")-1))-6;3);"/";""))
+TEMPSVAL(DROITE(A2;1+NBCAR(A2)-TROUVE(" ";A2&" "))))

À job75 : votre dernier code est effectivement très-rapide. Bien vu !
La solution par formule donne parfois des résultats suprenants ; un exemple parmi 18 468 :
19/04/2010 12:31:21 -> 04/07/2011 12:31:21.
​

Bonne journée.


ℝOGER2327
#7253

Mercredi 25 Clinamen 141 (Exit Saint Goya, alchimiste - fête Suprême Quarte)
27 Germinal An CCXXII, 4,1516h - anémone
2014-W16-3T09:57:50Z

 

[MJ13]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonjour à tous

Ce sujet est intéressant mais j'aurais quand même une question sur le fichier Original, que doit-on obtenir?

Tout est de format M/J/A ou autre ?

 

[Abel]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Bonjour le fil, le forum,

Je m'invite 5 minutes.
Juste le temps d'aller dans le sens de Modeste.
J'ai eu le cas dans une autre vie.
Un export depuis une base de données (je sais plus laquelle) nous donnait des fichiers texte (txt).

Les informations dans ce fichier texte étaient bonnes. C'est à dire que les dates étaient toutes correctes.
C'est l'import sous Excel qui posait problème.
Les dates exportées au format français (jj/mm/aaaa) dans le fichier texte se retrouvaient, après import, au format anglais (mm/jj/aaaa) sauf pour les jours supérieurs à 12.

On retrouvait ce que modeste décrit quelques post plus haut.

Exemple :
le 03/01/2000 (3 janvier) correct dans le fichier texte devenait après import Excel le 01/03/2000 (1er mars).
le 15/03/2000 (15 mars) correct dans le fichier texte restait le 15/03 après import dans Excel.
Le tout étant compris et interprété par Excel comme étant des dates.

Belle cochonnerie. A l'époque, et comme c'était systématique, j'avais pondu une usine à gaz en macro pour tester le jour et inverser le jour et le mois si nécessaire après import dans Excel.

XLD ne m'ayant malheureusement pas été d'un grand secours sur ce coup là (c'est quand même bien rare).

Alors si quelqu'un a une solution, je suis preneur.

Abel.

 

[job75]

Re : Date inversé et aussi, dans un format en dur qui résiste.

Re,

La solution par formule donne parfois des résultats suprenants ; un exemple parmi 18 468 :
19/04/2010 12:31:21 -> 04/07/2011 12:31:21

Oui c'est normal puisque dans ce cas la date n'est pas inversée.

On peut y remédier en utilisant cette formule dans le fichier (2) joint :

=SI(ESTNUM(A2);SI(JOUR(A2)>12;A2;DATE(ANNEE(A2);JOUR(A2);MOIS(A2))+MOD(A2;1));--(STXT(REPT(0;ESTERR(-GAUCHE(A2;2)))&A2;4;3)&GAUCHE(REPT(0;ESTERR(-GAUCHE(A2;2)))&A2;3)&STXT(REPT(0;ESTERR(-GAUCHE(A2;2)))&A2;7;20)))

Mais du coup se pose la question de savoir si les dates comme 01/05/2009 sont inversées ou pas...

Je suis bien d'accord avec Modeste geedee, il faudrait se servir du fichier texte d'origine.

A+