XL 2021 Deux formats de date dans un jeu de donnée volumineux

[GreMo]

Bonjour à tous,

Premier post, je me présente, je m'appelle Greg, et je travaille dans la gestion d'aires marines protégées.
Nous relevons, quand nous le pouvons, des sondes de température, qui enregistrent la température de l'eau toutes les heures. Malheureusement, chaque mois, les sondes changent de format d'écriture pour la date.
Un coup 04/30/24, qui reste immuable peu importe le format de la cellule, et le jour d'après 05/01/2024, qui devient 45296 quand on passe la cellule au format standard. S'il n'y avait que quelques entrées ça irait, mais chaque sonde a plus d'un an ou deux de données; ce qui donne plusieurs milliers d'entrées à chaque fois.
Je vous joins un petit extrait du jeu de donnée, vous allez vite comprendre...
Si quelqu'un connaissait une formule ou une manip pour harmoniser toutes ces dates, je lui en serais grandement reconnaissant.

Merci d'avance et excellente journée à toi qui lis ce message,
Greg

 

[JHA]

Bonjour à tous,

A essayer

=SI(ESTERREUR([@Date]*1);DATE("20"&DROITE(B2;2);GAUCHE(B2;2);STXT(B2;4;2));[@Date])

JHA

 

[fanfan38]

Bonjour et bienvenu sur le forum
Ci joint ma solution

A+ François

 

[mapomme]

Bonjour à tous 🙂,

@fanfan38 , @JHA que je salue,

Selon ma pomme, on n'a pas assez d'éléments pour fournir une réponse pertinente.
Il faut en fait voir les données données d'entrée brutes. Il sembleraient que ces données soient au format USA :

• dates sous la forme mm/dd/yy (mm/jj/aa traduit en français)
• nombres avec comme séparateur décimal le point (et non pas la virgule comme en France)

Si c'est cela (et je le crois) quand Excel a importé le fichier texte :

• si le texte censé représenter une date est une date valable en français, alors l'importation aboutit à une interprétation de date mais cette date sera fausse
• si le texte censé représenter une date est une date incorrecte en français, alors l'importation aboutit à un texte
• le texte représentant un nombre sera importé comme du texte car Excel en Français ne sait pas interpréter le point comme séparateur décimal

Exemple :
"01/05/24" sera importé comme une date 01 mai 2024 alors que c'est en fait le 05 janvier 2024 et on aura aura la date fausse 01/05/2024
"04/30/24" sera importé comme du texte car pas interprétable comme une date française et on aura donc le texte 04/30/24
On a donc soit du texte (date américaine) ou une date erronée (date mal interprétée comme une date française).

Les formules doivent donc tenir compte de ces deux cas et rétablir aussi la bonne date quand le résultat de l'importation a été interprété comme étant une date française.

Seul @GreMo connait ce qu'on importe pour savoir comment résoudre son problème.

nota : ce qui me fait pencher pour penser que le fichier texte origine est complètement au format US c'est que toutes les températures sont du texte. Donc il y a de fortes chances que les dates sources soient aussi toutes au format US.

 

[Jean-Eric]

Bonjour,
Une autre proposition !?
Cdlt.

 

[C'est papy Mougeot !]

Boujour tout le monde,
Avec un petit code

Sub ConvertirDates()
    Dim cell As Range
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Feuil1")
   
    For Each cell In ws.Range("B1:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
        If IsDate(cell.Value) Then
            cell.Value = Format(cell.Value, "dd/mm/yyyy")
        Else
            On Error Resume Next
            cell.Value = Format(CDate(cell.Value), "dd/mm/yyyy")
            On Error GoTo 0
        End If
    Next cell
   
    MsgBox "Conversion terminée !"
End Sub

Ce n'est qu'une proposition qui semble bien fonctionner

 

[mapomme]

Re,

Une formule pour traiter les dates US importées.
Elle devrait fonctionner dans les cas où :

• la date US été interprétée comme du texte
• la date US été interprétée comme une date FR (mais date FR erronée)
• le mois et/ou le jour peuvent ne comporter qu'un seul caractère
• l'année peut être sur deux ou quatre caractères

La formule en B2 (à copier vers le bas) :

=SIERREUR(SI(ESTNUM(A2);DATE(ANNEE(A2);JOUR(A2);MOIS(A2));DATE(1*STXT(A2;CHERCHE("/";A2;4)+1;9);1*GAUCHE(A2;CHERCHE("/";A2)-1);1*SUBSTITUE(GAUCHE(STXT(A2;CHERCHE("/";A2)+1;9);2);"/";"")));"")

 

[GreMo]

Bonjour à tous,
Et merci énormément de vous donner du mal pour m'aider à résoudre ce problème Quel accueil!
Mauvaise nouvelle en revanche, aucune des solutions ne semble fonctionner. J'en viens à penser qu'il s'agit peut-être un bug avec ma version d'Excel, qui sait.
Pour vous JHA, quand j'ouvre votre fichier, la formule renvoie 01/01/1904.
Et pour vous François, ce que votre fichier renvoie semble bon, mais une fois la formule retranscrite dans un mon fichier Excel, ça me redonne un nombre entier 45296 (ou autre), pareil pour la solution de Jean-Eric ou MaPomme.
Cependant je pense qu'il y a bel et bien des élements de réponse dans ton message @mapomme . Les sondes de températures sont effectivement originaires des US.

On a donc soit du texte (date américaine) ou une date erronée (date mal interprétée comme une date française).

Serait il possible qu'Excel ait importé les dates un coup en français, un coup en américain? Plutôt que "ou," il se pourrait que ce soit un "et". Car maintenant que tu le soulignes, la fracture se fait entre le 05/12/2024 (date logique au format Fr) et le 05/13/2024 (date impossible au format Fr). L'effet inverse se produit entre 05/31/2024 et le 06/01/2024.
Je vais essayer de changer la langue d'interprétation des données en mettant Anglais (Etats-Unis) par défaut. Je redémarre et je vous tiens au courant.
La solution semble proche!

Greg

 

[fanfan38]

Il suffit de mettre ta colonne au format date
A+ François

 

[GreMo]

Il suffit de mettre ta colonne au format date
A+ François

J'aimerais beaucoup que ce soit aussi simple, mais malheureusement ce n'est pas le cas. Même au format date, je ne peux extraire le mois, l'année, etc qu'avec les dates plausibles au format "français". Les autres me renvoient un message d'erreur. 🙁

 

[mapomme]

Re @GreMo,

Un fichier démo à partir de votre fichier de départ. Voir les formules en D2, E2, F2, G2, H2 et les formats de ces deux colonnes.

Que donne ce fichier quand vous l'ouvrez sur votre bécane ?
Êtes-vous sur Mac ou PC ?
Dans les options Excel de votre classeur, êtes- vous en calendrier "1904" ?

 

[GreMo]

Pour les options, j'ai globalement les mêmes que toi. Et le doc me renvoi ça:

Cependant! C'est grâce à toi si j'ai pu bidouiller un truc et avoir une solution.
Je mets une capture d'écran ici au cas où quelqu'un aurait le même problème à l'avenir.
Pour avoir les dates au même format, il m'a fallut utiliser l'outil convertir sur la colonne des dates uniquement, et spécifié qu'il s'agissait d'une date, mais au format MJA (mois/jours/année). En appuyant sur la commande terminer, elles sont toutes passées au format "date" françaises, et répondent aux opérations type =mois() = annee() etc.

Merci à tous, énormément, pour vos retours!
J'espère que celle/celui qui aura le même problème tombera sur cette page, ou sur des gens aussi bienveillants que vous!
Greg

 

[mapomme]

Serait il possible qu'Excel ait importé les dates un coup en français, un coup en américain? Plutôt que "ou," il se pourrait que ce soit un "et". Car maintenant que tu le soulignes, la fracture se fait entre le 05/12/2024 (date logique au format Fr) et le 05/13/2024 (date impossible au format Fr). L'effet inverse se produit entre 05/31/2024 et le 06/01/2024.

Attention ! Selon mon expérience, il n'est jamais anodin de changer la langue, le pays, les formats des nombres, dates, heures, le séparateur décimal décimal et le séparateurs des milliers à la volée pour les classeurs existants.

Serait il possible qu'Excel ait importé les dates un coup en français, un coup en américain? Plutôt que "ou," il se pourrait que ce soit un "et". Car maintenant que tu le soulignes, la fracture se fait entre le 05/12/2024 (date logique au format Fr) et le 05/13/2024 (date impossible au format Fr). L'effet inverse se produit entre 05/31/2024 et le 06/01/2024.

Oui, c'est ce que j'ai essayé d'expliquer. Suivant la manière dont vous importez les données (importer, ouverture d'un fichier texte, conversion d'un fichier CSV, Copier/coller, etc) , les résultats peuvent différer.
Dans votre cas, j'ai l'impression que même les dates qui semblent être importées comme de vraies dates sont erronées (mois et jours intervertis).

 

[mapomme]

utiliser l'outil convertir

Je suis vexé 😠. D'habitude j'y pense tout de suite mais là je suis parti bille en tête sur des formules ☹️.
Salut 😉

nota : c'est le classeur de JHA qui est en option "calendrier 1904".

 

[GreMo]

Je suis vexé 😠. D'habitude j'y pense tout de suite mais là je suis parti bille en tête sur des formules ☹️.
Salut 😉

nota : c'est le classeur de JHA qui est en option "calendrier 1904".

Haha ne te vexe surtout pas! C'est sur tes conseils que j'ai pu fouiller au bon endroit!
Merci!