Convertir texte en date-heure

Le 14/05/2010 à 19:41

Bonjour forum,

j'ai des fichiers archivés dont les cellules dates et heures ont été inscrites en format texte. Je voudrais convertir ces cellules en format date-heure de façon à ce que Excel puissent les interpréter. Je fournis un échantillon d'un fichier en question:

332ww-obs-tsh-2007-08.zip (9.26 Ko)

Tout un défi dont je suis sûr que quelqu'un sur ce forum saura résoudre... et certainement , plusieurs jours en économie de temps puisque je compte appliquer la recette à plus de 20 ans de données.

Merci d'avance

Émil

Le 14/05/2010 à 19:59

Bonjour

Donc

WW_obs_TSH_2007_08.xls correspond à 2008, selon tes exemples en rouge ?

Cordialement

Le 14/05/2010 à 20:29

Bonjour,

En réalité, un fichier comprte 12 feuillets qui débute en septempre et se termine en août de l'année suivante. Dans l'exemple donné, j'ai inclus les mois de novembre à février. Pour les feuillets: nov et dec, l'année est 2007 tandis que pour les feuillets: jan et fev, l'année est 2008.

Votre promptitude est grandement appréciée

Émil

Le 14/05/2010 à 21:52

Bonsoir

Deux questions :

Quelle réponse faut-il mettre quand "débuté" est inscrit ?

Est-ce une erreur ou peut-il y avoir 2 formats de saisie différents dans une colonne ?

A te relire

Amicalement

Nad

Le 14/05/2010 à 22:13

Bonsoir à tous, Salut Nad,

Autres questions

1) Peut-on compter sur les noms d'onglets (sont-ils toujours libellés pareil) dans tous les fichiers ?

exemple: Déc ou Dec ou Décembre ou 12

2) la structure des fichiers est-elle identique sur tous les fichiers ? (1ère lignes, colonnes, formats etc...)

3) combien de lignes par onglet (environ, fourchette) ?

4) combien de fichiers ou années ?

Amicalement

Claude

Le 14/05/2010 à 22:29

Bonsoir

Un peu long, mais sujet intéressant

Dans un premier temps, renommer les onglets avec une orthographe compatible avec Excel.(garder les anciennes orthographes est faisable en compliquant avec une autre formule)

Pour ne pas se tromper, dans une cellule, taper Janv et incrémenter. Excel mettra les accents et les majuscules et pourra par la suite identifier correctement ces mois.

Extraction de l'année du Nom du fichier WW_obs_TSH_2007_08.xls

Pour trouver le nom du fichier, j'ai utilisé la formule issue des Macros XL4

=LIRE.CELLULE(66;Févr!$A$1)

que j'ai Nommée Class (Voir commande de menu "Insertion" "Nom" "Définir")

donc, en entrant dans une cellule la formule

=Class

j'obtiens le nom du Classeur WW_obs_TSH_2007_08.xls

Pour trouver l'année de base, une formule classique d'extraction de texte

=GAUCHE(DROITE(Class;11);4)*1

renvoie 2007

Compte tenu de l'explication fournie. Pour ce classeur, les mois de Septembre à Décembre sont ceux de 2007 et ceux de Janvier à Août sont ceux de 2008.

Il nous faut donc le mois pour avoir l'année.

Le mois est renvoyè en texte à partir du nom de l'onglet qui lui est renvoyé par une autre formule nommée Doc (pour document)

=LIRE.CELLULE(32;!$A$1)

=Doc dans n'importe quelle cellule du classeur renvoie le nom du Classeur suivi du nom de la Feuille active sous la forme

[WW_obs_TSH_2007_08.xls]Févr

Nouvelle formule d'extraction

=STXT(Doc;TROUVE("]";Doc)+1;32)

qui renvoie Févr

et dont le No est renvoyé par

=MOIS("1/"&STXT(Doc;TROUVE("]";Doc)+1;32))

formule qui est Nommée Mois

Pour avoir l'année par rapport au mois la formule suivante est nommée Année

=CHOISIR(EQUIV(MOIS("1/"&STXT(Doc;TROUVE("]";Doc)+1;32));{1;9});(GAUCHE(DROITE(Class;11);4)*1)+1;GAUCHE(DROITE(Class;11);4)*1)

Avec une formule nommée Année, une formule nommée Mois, en utilisant ces noms dans la formule DATE, nous pouvons reconstituer les dates sous la forme

DATE(Année; Mois; Jour) puisque les jours sont les 2 premiers chiffres de chaque cellule.

Donc, Feuille Févr, cellule B2

=DATE(Année;Mois;GAUCHE(A2;2)*1)+TEXTE(STXT(SUBSTITUE(A2;"/";"");3;2)&":"&STXT(SUBSTITUE(A2;"/";"");5;2);"hh:mm")

Le substitue a du être rajouté, quelques cellule affichant un / et d'autres non.

Formule en F2

=DATE(Année;Mois;GAUCHE(E2;2)*1)+TEXTE(STXT(E2;4;2)&":"&"00";"hh:mm")

Formule en H2

=DATE(Année;Mois;GAUCHE(G2;2)*1)+TEXTE(STXT(SUBSTITUE(G2;"/";"");3;2)&":"&"00";"hh:mm")

Formule en K2

=DATE(Année;Mois;GAUCHE(J2;2)*1)+TEXTE(STXT(SUBSTITUE(J2;"/";"");3;2)&":"&STXT(SUBSTITUE(J2;"/";"");5;2);"hh:mm")

idem pour les autres feuilles.

Les valeurs d'erreurs dues à la présence du texte "débuté' peuvent êtres supprimées si besoin avec

SI(LaCellule="débuté";"";La formule)

Les explications sont en feuille Fév

Cordialement

119ww-obs-tsh-2007-08.zip (15.99 Ko)
Le 14/05/2010 à 23:24

Re

Ma solution (en attendant les réponses aux questions posées)

127copie-de-ww-obs-tsh-2007-08.zip (26.43 Ko)

Nad

Le 14/05/2010 à 23:33

Bonjour à vous tous,

Tout d'abord, je dois avouer que je suis en train d'analyser la solution de Amadéus (je devrais dire me familiariser car une bonne part du code est nouveau pour moi). À première vue, la réponse semble me convenir.

Néanmoins, j'aimerais répondre à Nad et Claude qui ont eu l'amabilité de se pencher sur mon problème.

À Nad:

Quelle réponse faut-il mettre quand "débuté" est inscrit ?

---- On laisse la cellule avec "débuté", dons un SI devrait faire l'affaire.

Est-ce une erreur ou peut-il y avoir 2 formats de saisie différents dans une colonne ?

---- Oui, il est possible que 2 ou plusieurs exceptions puissent survenir. Tant pis, la conversion de ces exceptions sera faite manuellement.

À Claude:

1. Tu as bien deviné car effectivement j'ai remarqué que le nom des onglets peut parfois avoir une légère variante d'une année à l'autre.

2. La structure des fichiers est toujours identique d'une année à l'autre.

3. Il y a envriron 50 lignes par onglet en été puis, ça augmente graduellement jusqu'à environ 500 lignes en hiver.

4. Les fichiers débutent en 1988-89.

Voilà, je tiens encore à vous remercier. Si je rencontre des problèmes avec la solution de Amadéus, je reviens sinon j'inscrirai que mon problème est résolu.

Émil

Le 15/05/2010 à 09:12

Bonjour à tous,

Solution VBA, c'est un bon entrainement !

pour faciliter le problème des noms feuilles, ici renommées de 1 à 12

il peut y avoir d'autres feuilles qui ne seront pas traitées.

Sub ConvertirDates()
'Macros par Claude Dubois pour "Emil" Excel-Pratique le 15 Mai 2010
Dim Lg%, i%, J%, Jr%, Moi%, An%, H, Mn
Dim Wb$, Ws As Byte, CL As Byte
    Application.ScreenUpdating = False
    Wb = ThisWorkbook.Name
    For Ws = 1 To Worksheets.Count
        Worksheets(Ws).Activate
        If IsNumeric(ActiveSheet.Name) Then
            Moi = ActiveSheet.Name
            Lg = Range("A65536").End(xlUp).Row
            If Moi > 8 Then     'contrôle année
                An = Left(Right(Wb, 11), 4)
            Else
                An = Left(Right(Wb, 11), 4) + 1
            End If
            '------ dates Emis et Terminé -----------
            CL = 1
            For J = 1 To 2
                For i = 2 To Lg
                    If IsNumeric(Left(Cells(i, CL), 2)) Then
                        Jr = Left(Cells(i, CL), 2)
                        H = Left(Right(Cells(i, CL), 5), 2)
                        Mn = Left(Right(Cells(i, CL), 3), 2)
                        Cells(i, CL + 1) = Format(CDate(Jr & "/" & Moi & "/" & _
                        An), "m/d/yyyy") & " " & H & ":" & Mn
                    End If
                Next i
                CL = CL + 9
            Next J
            '------ dates début et fin -----------
            CL = 5
            For J = 1 To 2
                For i = 2 To Lg
                    If IsNumeric(Left(Cells(i, CL), 2)) Then
                        Jr = Left(Cells(i, CL), 2)
                        H = Left(Right(Cells(i, CL), 3), 2)
                        Mn = 0
                        Cells(i, CL + 1) = Format(CDate(Jr & "/" & Moi & "/" & _
                        An), "m/d/yyyy") & " " & H & ":" & Mn
                    End If
                Next i
                CL = CL + 2
            Next J
        End If
    Next Ws
End Sub

Ce qui n'empêche pas de tirer mon chapeau à Amadéus pour ses formules

Amicalement

Claude

65emil-2007-08.zip (25.14 Ko)
Le 15/05/2010 à 18:03

Bonjour à tous,

Pour Amadéus,

Génial comme solution. Après plusieurs heures consacrées à saisir le cheminement, il y a encore 1 petit détail qui m'échappe:

tu dis: "Pour trouver le nom du fichier, j'ai utilisé la formule issue des Macros XL4

=LIRE.CELLULE(66;Févr!$A$1)

qu'en est-il des macros XL4 comment trouver de la documentation là-dessus?

Que veut dire le: 66 dans la formule ci-haut?

J'ai appliqué ta solution au fichier complet et tout a bien fonctionné.

Pour Claude,

Je suis encore novice avec VBA mais j'apprécie la simplicité de ta VBA.

J'ai tenté d'appliquer ta VBA sur un autre fichier nommé: WW_obs_TSH_1988_89. Pour ce faire, j'ai copié le bouton de démarrage de la macro dans ce dernier fichier. Tout a bien fonctionné sauf que les années indiquées sont demeurées en 2007 et 2008. Pour corriger le problème, dois-je aussi copier la macro du fichier: Emil2007_08 dans le fichier WW_obs_TSH_1988_89 (et donc dans tous les fichiers à convertir) pour que l'année soit bien indiquée ou il y a un moyen de faire autrement?

Dans les deux cas, je dois dire que j'ai encore énormément appris des 2 approches complètement différentes.

Merci

Émil

Le 15/05/2010 à 18:31

Bonjour

tu dis: "Pour trouver le nom du fichier, j'ai utilisé la formule issue des Macros XL4

=LIRE.CELLULE(66;Févr!$A$1)

qu'en est-il des macros XL4 comment trouver de la documentation là-dessus?

Que veut dire le: 66 dans la formule ci-haut?

Claude t'a envoyé une macro VBA.

Avant VBA, il existait pour nous une écriture des Macros en bon vieux français, bien compréhensible. (il existe toujours, mais tombe en désuétude, Microsoft préférant développer les codes VB dans sa langue native)

Toutefois, le langage macro XL4 (Contraction d' Excel 4.0) est toujours accepté par les versions actuelles d'Excel et certaines fonctionnalités comme la macro-fonction LIRE.CELLULE offrent un raccourci intéressant et une bonne alternative.

Le panel des possibilités de la fonction LIRE.CELLULE est sur

https://forum.excel-pratique.com/cours-astuces/lire-cellule-excel4-t15442.html

=LIRE.CELLULE(66; Fév!$A$1)

ou

 =LIRE.CELLULE(66; !$A$1)

renvoie le nom du classeur.

ce qui est tout de même plus court que les formules actuelles à disposition comme

=STXT(CELLULE("filename");CHERCHE("[";CELLULE("filename");1)+1;(CHERCHE("]";CELLULE("filename");1)-CHERCHE("[";CELLULE("filename");1)-1))

ou

 =STXT(CELLULE("filename");TROUVE("[";CELLULE("filename"))+1;SOMME(TROUVE({"[";"]"};CELLULE("filename"))*{-1;1})-1)

qui font la même chose.

Quant à la documentation générale des macros XL4, elle n'offre plus réellement d'intèrêt, sauf à comprendre certaines macros pour les traduires en VBA, plus concis et plus puissant sauf quelques cas exceptionnels.

A ta disposition (Comme tout le forum du reste) si tu as d'autres questions.

Cordialement

Le 15/05/2010 à 19:32

Bonsoir,

VBA (suite)

Tout a bien fonctionné sauf que les années indiquées sont demeurées en 2007 et 2008.

erreur de ma part ! en début de macro, remplace cette ligne

    Wb = ThisWorkbook.Name

par

    Wb = ActiveWorkbook.Name

inutile de copier la macro, Exemple sur ce fichier 88_89, il n'y a pas de macro mais le bouton active

le fichier 2007_08

Amicalement

Claude

31emil-1988-89.zip (22.14 Ko)
Le 15/05/2010 à 23:38

Bonjour,

La VBA de Claude fonctionne parfaitement et les explications supplémentaires de Amadéus sont super!

Encore merci

Émil

Rechercher des sujets similaires à "convertir texte date heure"