Impossible de faire reconnaître la date dans la colonne par Excel

Le problème: Excel ne veut pas reconnaître les dates comme des dates, même si par “Format cells - Number - Custom” vous essayez explicitement de lui dire que ce sont des dates par “mm/dd/yyyy”. Comme vous le savez, lorsqu'Excel a reconnu une date, il la stocke sous forme de nombre - comme “41004” - mais l'affiche sous forme de date selon le format que vous avez spécifié. Pour ajouter à la confusion, Excel peut ne convertir qu'une partie de vos dates comme le 04/08/2009, mais laisser les autres, par exemple le 28/07/2009, non converties.

Solution : étapes 1 puis 2

1) Sélectionnez la colonne de la date. Sous Données, choisissez le bouton Texte aux colonnes. Sur le premier écran, laissez le bouton radio sur “ delimited” et cliquez sur Next. Décliquez sur l'une des cases de délimitation (toutes les cases blanches ; pas de coches) et cliquez sur Next. Sous le format des données de la colonne, choisissez Date et sélectionnez MAI dans la zone combinée adjacente et cliquez sur Finish. Vous avez maintenant des valeurs de date (c'est-à-dire qu'Excel a reconnu vos valeurs comme étant le type de données Date), mais le formatage est probablement toujours la date locale, et non le mm/dd/yyyy que vous souhaitez.

2) Pour obtenir le format de date US souhaité affiché correctement, vous devez d'abord sélectionner la colonne (si elle n'est pas sélectionnée) puis, sous Cell Format - Number, choisissez Date ET sélectionnez Locale : Anglais (US). Vous obtiendrez alors un format du type “m/d/yy”. Ensuite, vous pouvez sélectionner Custom et là, vous pouvez soit taper “mm/dd/yyyy”, soit choisir cela dans la liste des chaînes personnalisées.

Alternative : utilisez LibreOffice Calc. Après avoir collé les données du message de Patrick, choisissez “Paste Special” (Ctrl+Shift+V) et choisissez “Unformatted Text”. Cela ouvrira la boîte de dialogue “Importer du texte”. Le jeu de caractères reste Unicode mais pour la langue choisissez English(USA) ; vous devez également cocher la case “Detect special numbers”. Vos dates apparaissent immédiatement dans le format américain par défaut et sont triables par date. Si vous souhaitez le format américain spécial MM/JJ/AAAA, vous devez le spécifier une fois par “format Cellules” - avant ou après avoir collé.

On pourrait dire - Excel devrait avoir reconnu les dates dès que je l'ai dit par “Format Cellules” et je ne pourrais pas être plus d'accord. Malheureusement, ce n'est que grâce à l'étape 1 du haut que j'ai pu faire en sorte qu'Excel reconnaisse ces chaînes de texte comme des dates. Il est évident que si vous faites cela souvent, c'est une douleur dans le cou et vous pourriez mettre en place une routine visuelle de base qui le ferait pour vous en appuyant sur un bouton.

Data | Text to Columns

Mise à jour sur l'apostrophe principale après avoir collé: Vous pouvez voir dans la barre de formule que dans la cellule où la date n'est pas reconnue, il y a une apostrophe principale. Cela signifie que dans la cellule formatée comme un nombre (ou une date), il y a une chaîne de texte. On pourrait dire que l'apostrophe de tête empêche le tableur de reconnaître le nombre. Vous devez savoir qu'il faut regarder dans la barre de formule pour cela - parce que le tableur affiche simplement ce qui ressemble à un nombre aligné à gauche. Pour résoudre ce problème, sélectionnez la colonne que vous souhaitez corriger, choisissez dans le menu Data | Text to Columns et cliquez sur OK. Vous pourrez parfois spécifier le type de données, mais si vous avez préalablement défini le format de la colonne comme étant votre type de données particulier - vous n'en aurez pas besoin. Cette commande est en fait destinée à diviser une colonne de texte en deux ou plusieurs en utilisant un délimiteur, mais elle fonctionne aussi parfaitement pour ce problème. Je l'ai testé dans Libreoffice, mais il y a le même élément de menu dans Excel aussi.

Sélectionnez toute la colonne et allez à Locate and Replace et remplacez simplement "/" par /.

J'ai constaté qu'Excel ne reconnaissait pas une colonne de dates toutes antérieures à 1900, en insistant sur le fait qu'il s'agissait d'une colonne de texte (puisque le 1/1/1900 a l'équivalent numérique de 1, et que les nombres négatifs ne sont apparemment pas autorisés). J'ai donc procédé à un remplacement général de toutes mes dates (qui étaient dans les années 1800) pour les placer dans les années 1900, par exemple 180 -> 190, 181 -> 191, etc. Le processus de tri a ensuite bien fonctionné. Finalement, j'ai fait un remplacement dans l'autre sens, par exemple 190 -> 180.

J'espère que cela aidera d'autres historiens.

J'étais confronté à un problème similaire avec des milliers de lignes extraites d'une base de données SAP et, inexplicablement, deux formats de date différents dans la même colonne de date (la plupart étant notre standard “AAAA-MM-JJ” mais environ 10% étant “MM-JJ-AAA”). L'édition manuelle de 650 lignes une fois par mois n'était pas une option.

Aucune (zéro… 0… nil) des options ci-dessus n'a fonctionné. Oui, je les ai toutes essayées. La copie dans un fichier texte ou l'exportation explicite au format txt n'avait toujours pas d'effet sur le format (ou l'absence de format) des 10 % de dates qui restaient simplement dans leur coin en refusant de se comporter. La seule façon de remédier à ce problème était d'insérer une colonne vide à droite de la colonne des dates de mauvaise conduite et d'utiliser la formule suivante, plutôt simpliste :

(en supposant que vos données de mauvaise conduite se trouvent dans Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

Je pouvais ensuite copier les résultats dans ma nouvelle colonne calculée par-dessus les dates de mauvaise conduite en collant “Valeurs” seulement après quoi je pouvais supprimer ma colonne calculée.

J'ai eu exactement le même problème avec les dates en excel. Le format correspondait aux exigences d'une date en excel, mais sans modifier chaque cellule, il ne reconnaîtrait pas la date, et lorsque vous traitez des milliers de lignes, il n'est pas pratique de modifier manuellement chaque cellule. La solution est de lancer une recherche et un remplacement sur la ligne de dates où j'ai remplacé le trait d'union par un trait d'union. Excel parcourt la colonne en remplaçant comme par comme mais le facteur résultant est qu'il reconnnaît maintenant les dates ! FIXE !

Il semble qu'Excel ne reconnaisse pas vos dates comme des dates, il les reconnaît comme du texte, d'où les options de tri de A à Z. Si vous le faites correctement, vous devriez obtenir quelque chose comme ceci :

Il est donc important de s'assurer qu'Excel reconnaît la date. La façon la plus simple de le faire est d'utiliser le raccourci CTRL+Maj+3.

Voici ce que j'ai fait à vos données. Je l'ai simplement copié de votre message ci-dessus et je l'ai collé dans Excel. Puis j'ai appliqué le raccourci ci-dessus, et j'ai obtenu l'option de tri requise. Vérifiez l'image.

Je soupçonne que le problème est qu'Excel ne peut pas comprendre le format… Bien que vous sélectionniez le format de la date, il reste sous forme de texte.

Vous pouvez le tester facilement : Lorsque vous essayez de mettre à jour le format des dates, sélectionnez la colonne et faites un clic droit dessus et choisissez le format des cellules (comme vous le faites déjà) mais choisissez l'option 2001-03-14 (en bas de la liste). Ensuite, examinez le format de vos cellules.

Je soupçonne que seuls certains des formats de date se mettent à jour correctement, ce qui indique qu'Excel le traite toujours comme une chaîne de caractères et non comme une date (notez les différents formats dans la capture d'écran ci-dessous) !

Il existe des solutions de rechange, mais aucune d'entre elles ne sera automatisée simplement parce que vous exportez à chaque fois. Je vous suggère d'utiliser le VBa, où vous pouvez simplement lancer une macro qui convertit le format de date américain en format britannique, mais cela signifierait copier et coller le VBa dans votre feuille à chaque fois.

Alternativement, vous pouvez créer un Excel qui lit les feuilles Excel exportées nouvellement créées (à partir de l'échange) et ensuite exécuter le VBa pour mettre à jour le format de date pour vous. Je suppose que le fichier Excel d'échange exporté aura toujours le même nom de fichier/répertoire et je vous donne un exemple de travail :

Donc, créez une nouvelle feuille Excel, appelée ImportedData.xlsm (excel activé). C'est dans ce fichier que nous allons importer le fichier Excel d'échange exporté !

Ajoutez ce VBa au fichier ImportedData.xlsm

Sub DoTheCopyBit()

Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)

Do While fileName <> ""

'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)

Workbooks(fileName).Worksheets("Sheet1").Copy _

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")

     Dim splitty() As String
     splitty = Split(Range(dateCol & row).Value, "/")
     Range(dateCol & row).NumberFormat = "@"
     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
     row = row + 1
Loop

End Sub

Ce que j'ai également fait, c'est mettre à jour le format de date en aaay-mm-dd car de cette façon, même si Excel vous donne le filtre de tri A-Z au lieu des valeurs les plus récentes, cela fonctionne toujours !

Je suis sûr que le code ci-dessus a des bogues mais je n'ai évidemment aucune idée du type de données que vous avez mais je l'ai testé avec une seule colonne de dates (comme vous l'avez fait) et ça fonctionne bien pour moi ! Comment ajouter VBA dans MS Office ?

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

Solution simple ici - créer une nouvelle colonne utiliser la formule =datevaleur(cellule) puis copier la formule dans vos autres lignes - quelques secondes pour corriger

J'ai trouvé !

Il y a un espace au début et à la fin de la date.

1. Si vous utilisez la fonction “chercher et remplacer” et que vous appuyez sur la barre d'espacement, cela ne fonctionnera pas.
2. Vous devez cliquer à droite avant le numéro du mois, appuyer sur shift et la flèche gauche pour sélectionner et copier. Parfois, vous devez utiliser la souris pour sélectionner l'espace.
3. Ensuite, collez ceci comme espace dans Rechercher et remplacer et toutes vos dates deviendront des dates.
4. S'il y a un espace et une date, sélectionnez Données>Aller à Données>Texte aux colonnes>Délimité>Espaces comme séparateur et ensuite terminez.
5. Tous les espaces seront supprimés.

Je n'ai pas eu de chance avec toutes les suggestions ci-dessus - j'ai trouvé une solution très simple qui fonctionne comme un charme. Collez les données dans Google Sheets, mettez la colonne de la date en surbrillance, cliquez sur format, puis sur numéro, et encore une fois sur numéro pour le changer au format numérique. Je copie et colle ensuite les données dans la feuille de calcul Excel, je clique sur les dates, puis je formate les cellules à ce jour. C'est très rapide. J'espère que cela vous aidera.

J'ai simplement copié le chiffre 1 (un) et je l'ai multiplié dans la colonne des dates (données) en utilisant la fonction PASTE SPECIALE. Il passe ensuite au formatage général c'est-à-dire 42102 Puis il va appliquer la date sur le formatage et il la reconnaît maintenant comme une date.

J'espère que cela vous aidera

Ma solution au Royaume-Uni - j'avais mes dates avec des points comme ceci :

03.01.17

J'ai résolu cela avec ce qui suit :

1. surlignez toute la colonne.
2. Allez à la recherche et sélectionnez/remplacez.
3. J'ai remplacé tous les points par un tiret du milieu, par exemple 03.01.17 03-01-17.
4. Gardez la colonne en surbrillance.
5. Formatez les cellules, l'onglet des nombres sélectionnez la date.
6. Utilisez le Type 14-03-12 (essentiel pour le mien d'avoir le tiret du milieu)
7. Locale English (Royaume-Uni)

En triant la colonne, toutes les dates de l'année sont triées.

PARTAGE D'UNE PETITE LONGUEUR MAIS SOLUTION PLUS FACILE POUR SUBIR ….. Pas besoin de lancer des macros ou des trucs geeky….formules simples ms excel et édition.

mixed dates excel snapshot :

• La date est dans le format mixte.
  
• Par conséquent, pour obtenir un format de date symétrique, des colonnes supplémentaires ont été créées en convertissant cette colonne de date “format mixte” en TEXTE.
• A partir de ce texte, nous avons identifié les positions de “/” et le texte du milieu a été extrait en déterminant la date, le mois, l'année à l'aide de la formule MID.
• Ceux qui étaient à l'origine des dates, la formule s'est terminée par le résultat ERREUR / #VALUE. - Enfin, à partir de la chaîne que nous avons créée - nous avons converti ces dates en dates par la formule DATE.
• #VALUE ont été choisis tels quels par IFERROR ajouté à la formule DATE & la colonne a été formatée comme il se doit (ici, jj/mmm/aa)

* VOIR CI-DESSUS L'EXCELLENCE DE LA FICHE (= instantané Excel de dates mixtes) *

J'ai essayé les différentes suggestions, mais j'ai trouvé que la solution la plus simple pour moi était la suivante…

Voir les images 1 et 2 pour l'exemple… (note - certains champs ont été cachés intentionnellement car ils ne contribuent pas à l'exemple). J'espère que cela vous aidera…

1. Champ C - un format mixte qui m'a rendu absolument fou. C'est ainsi que les données provenaient directement de la base de données et n'avaient pas d'espaces supplémentaires ni de caractères fous. Lorsqu'elles étaient affichées sous forme de texte par exemple, le 01/01/2016 était affiché comme une valeur de type “42504”, mélangée avec le 15/04/2006 qui était affiché tel quel.

2. Champ F - où j'ai obtenu la longueur du champ C (la formule LEN serait une longueur de 5 sur 10 selon le format de la date). Le champ est de format général pour plus de simplicité.

3. Champ G - où j'obtiens la composante mois de la date mixte - en me basant sur le résultat de la longueur du champ F (5 ou 10), j'obtiens soit le mois à partir de la valeur de la date dans le champ C, soit les caractères du mois dans la chaîne.

4. Champ H - obtention de la composante jour de la date mixte - sur la base du résultat de la longueur du champ F (5 ou 10), j'obtiens soit le jour à partir de la valeur de la date dans le champ C, soit les caractères du jour dans la chaîne.

Je peux le faire pour l'année également, puis créer une date cohérente à partir des trois composantes. Sinon, je peux utiliser les valeurs individuelles du jour, du mois et de l'année dans mon analyse. Image 1 : feuille de calcul de base montrant les valeurs et les noms des champs Image 2 : feuille de calcul montrant les formules correspondant à l'explication ci-dessus

J'espère que cela vous aidera.

Ce problème me rendait fou, puis je suis tombé sur une solution facile. Au moins, ça a fonctionné pour mes données. C'est facile à faire et à retenir. Mais je ne sais pas pourquoi cela fonctionne, mais changer de type comme indiqué ci-dessus ne fonctionne pas. 1. Sélectionnez la ou les colonnes avec les dates 2. Cherchez une année dans vos dates, par exemple 2015. Sélectionnez Remplacer tout par la même année, 2015. 3. Répétez pour chaque année. Cela change les types en dates réelles, année par année. Cela reste lourd si vous avez de nombreuses années dans vos données. Le plus rapide est de rechercher et de remplacer 201 par 201 (pour remplacer 2010 à 2019) ; de remplacer 200 par 200 (pour remplacer 2000 à 2009) ; et ainsi de suite.

Sélectionnez les dates et passez ce code dessus…

On Error Resume Next
    For Each xcell In Selection            
        xcell.Value = CDate(xcell.Value)     
    Next xcell                              
End Sub

Toutes les solutions ci-dessus - y compris les r0berts - ont échoué, mais ont trouvé cette solution bizarre qui s'est avérée être la plus rapide.

J'essayais de trier les “dates” sur une feuille de calcul téléchargée des transactions du compte.

Cela avait été téléchargé via le navigateur CHROME. Aucune manipulation des “dates” ne pouvait les faire reconnaître comme pouvant être triées de l'ancien au nouveau.

Mais j'ai ensuite téléchargé via le navigateur INTERNET EXPLORER - incroyable - je pouvais trier instantanément sans toucher une colonne.

Je ne peux pas expliquer pourquoi les différents navigateurs affectent le formatage des données, sauf que c'est clairement le cas et que c'était une solution très rapide.

Si Excel refuse obstinément de reconnaître votre colonne comme date, remplacez-la par une autre :

• Ajoutez une nouvelle colonne à droite de l'ancienne colonne
• Faites un clic droit sur la nouvelle colonne et sélectionnez Format
• Réglez le format sur date
• Surlignez toute l'ancienne colonne et copiez-la
• Surlignez la cellule supérieure de la nouvelle colonne et sélectionnez Paste Special, et collez seulement values
• Vous pouvez maintenant supprimer l'ancienne colonne.

Cela se produit tout le temps lors de l'échange de données de date entre les localités dans Excel. Si vous avez le contrôle du programme VBA qui exporte les données, je vous suggère d'exporter le numéro représentant la date au lieu de la date elle-même. Le nombre est en corrélation unique avec une seule date, indépendamment du formatage et de la localisation. Par exemple, au lieu du fichier CSV indiquant le 24/09/2010, il affichera 40445.

Dans la boucle d'exportation, lorsque vous maintenez chaque cellule, s'il s'agit d'une date, convertissez en nombre en utilisant CLng(myDateValue). Voici un exemple de ma boucle parcourant toutes les lignes d'un tableau et exportant en CSV (notez que je remplace également les virgules dans les chaînes de caractères par une balise que je dépouille lors de l'importation, mais vous n'en aurez peut-être pas besoin) :

arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i

J'utilise un mac.

Allez dans les préférences d'excel> Edition> Options de date> Convertir automatiquement le système de date

Aussi,

Allez dans Tables & Filtres> Grouper les dates lors du filtrage.

Le problème a probablement commencé lorsque vous avez reçu un fichier avec un système de date différent, et cela a bousillé votre fonction de date d'excel

En général, je crée juste une colonne supplémentaire pour le tri ou la totalisation, donc utilisez year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

qui fournira un résultat yyyymmdd qui peut être trié. L'utilisation de len(a1) permet simplement d'ajouter un zéro supplémentaire pour les mois 1 à 9.