Niet in staat om Excel de datum in kolom

Het probleem: Excel wil data niet herkennen als datums, ook al probeert u via “Formatteer cellen - Aantal - Aangepast” expliciet te zeggen dat het om datums gaat door “mm/dd/yyyy”. Zoals u weet; wanneer Excel iets als datum heeft herkend, slaat het dit verder op als een getal - zoals “41004” maar geeft het weer als datum volgens het door u opgegeven formaat. Om verwarring te voorkomen kan excel slechts een deel van uw data zoals 08/04/2009 omzetten, maar andere bijv. 07/28/2009 niet omzetten.

Oplossing: stap 1 en dan 2

1) Selecteer de datumkolom. Kies onder Gegevens de knop Tekst naar Kolommen. Laat op het eerste scherm het keuzerondje op “ delimited” staan en klik op Next. Klik op een van de afbakeningsvakjes (eventuele vakjes blank ; geen vinkje) en klik op Next. Kies onder kolom gegevensformaat Datum en selecteer MDY in het aangrenzende invoerveld en klik op Voltooien. Nu heeft u datumwaarden (d.w.z. Excel heeft uw waarden herkend als Date datatype), maar de opmaak is waarschijnlijk nog steeds de locale datum, niet de mm/dd/yyyy die u wilt.

2) Om het gewenste US-datumformaat goed weer te geven moet u eerst de kolom (indien niet geselecteerd) selecteren en dan onder Cell Format - Number kiezen voor Date EN Locale selecteren: Engels (US). Dit geeft u een formaat als “m/d/yy”. Vervolgens kunt u Aangepast selecteren en daar kunt u “mm/dd/yyyy” typen of dit kiezen uit de lijst met aangepaste strings.

Alternatief : gebruik LibreOffice Calc. Kies bij het plakken van gegevens uit Patrick’s post voor Paste Special (Ctrl+Shift+V) en kies Unformated Text (ongeformatteerde tekst). Dit opent het dialoogvenster “Tekst importeren”. Tekenset blijft Unicode maar voor taal kies English(USA); je moet ook het vakje “Detect Special numbers” aanvinken. Uw data verschijnen onmiddellijk in het standaard VS-formaat en zijn datumsorterbaar. Als je het speciale US formaat MM/DD/YYYYY wenst moet je dit eenmalig specificeren via “format Cells” - ofwel voor of na het plakken.

Men zou kunnen zeggen - Excel zou data moeten hebben herkend zodra ik het via “Cell Format” heb verteld en Ik kon het niet meer. Helaas is het pas door stap 1 van bovenaf dat ik in staat ben geweest om Excel deze tekststrings als datums te laten herkennen. Het is duidelijk dat als je dit veel doet het pijn in de nek is en je zou met een druk op de knop een visuele basisroutine kunnen samenstellen die dit voor je doet.

Data | Text to Columns

Update on leading apostrophe after pasting: Je kunt in de formulebalk zien dat in de cel waar de datum niet herkend wordt er een leidende apostrof is. Dat betekent dat er in de cel geformatteerd als een nummer (of datum) een tekststring staat. Je zou kunnen zeggen - de leidende apostrof voorkomt dat de spreadsheet het nummer herkent. Je moet weten dat je hiervoor in de formulebalk moet kijken - want het spreadsheet geeft gewoon weer wat lijkt op een links uitgelijnd getal. Om dit probleem aan te pakken selecteert u de kolom die u wilt corrigeren, kiest u in menu Data | Text to Columns en klikt u op OK. Soms kunt u het gegevenstype opgeven, maar als u het formaat van de kolom eerder hebt ingesteld op uw specifieke gegevenstype - dan hebt u dat niet nodig. Het commando is echt bedoeld om een tekstkolom in twee of meer te splitsen met behulp van een scheidingsteken, maar het werkt ook als een charme voor dit probleem. Ik heb het getest in Libreoffice, maar er staat ook hetzelfde menu-item in Excel.

Selecteer alle kolommen en ga naar Locate and Replace en vervang "/" door /.

Dit is misschien niet relevant voor de oorspronkelijke vraagsteller, maar het kan iemand anders helpen die niet in staat is om een kolom met data te sorteren.

Ik vond dat Excel een kolom met data die allemaal voor 1900 waren niet zou herkennen, met het argument dat ze een kolom met tekst waren (aangezien 1/1/1900 numerieke equivalent 1 heeft, en negatieve getallen blijkbaar niet zijn toegestaan). Dus deed ik een algemene vervanging van al mijn data (die in de jaren 1800 waren) om ze in de jaren 1900 te zetten, bijvoorbeeld 180 -> 190, 181 -> 191, etc. Het sorteerproces werkte toen prima. Uiteindelijk heb ik een vervanging gedaan op de andere manier, b.v. 190 -> 180.

Hoop dat dit een andere historicus helpt die er is.

Ik had te maken met een soortgelijk probleem met duizenden rijen die uit een SAP-database werden gehaald en, onverklaarbaar, twee verschillende datumformaten in dezelfde datumkolom (de meeste zijn onze standaard “JJJJ-MM-DD” maar ongeveer 10% is “MM-DD-JJJ”). Het handmatig bewerken van 650 rijen per maand was geen optie.

Geen (nul… 0… nihil) van de bovenstaande opties werkte. Ja, ik heb ze allemaal geprobeerd. Het kopiëren naar een tekstbestand of het expliciet exporteren naar txt had nog steeds, op de een of andere manier, geen effect op het formaat (of het ontbreken daarvan) van de 10% data die gewoon in hun hoekje zaten te weigeren zich te gedragen.

De enige manier waarop ik het kon herstellen was door een lege kolom rechts van de kolom met de datum van het wangedrag in te voegen en de volgende, nogal simplistische formule te gebruiken:

(ervan uitgaande dat je wangedraggegevens in Column D staan)

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

Ik kon dan de resultaten in mijn nieuwe, berekende kolom over de wangedrag-data heen kopiëren door alleen “Waarden” te plakken, waarna ik mijn berekende kolom kon verwijderen.

Ik had precies hetzelfde probleem met data in uitmuntendheid. Het formaat kwam overeen met de vereisten voor een datum in excel, maar zonder het bewerken van elke cel zou het de datum niet herkennen, en wanneer je te maken hebt met duizenden rijen is het niet praktisch om elke cel handmatig te bewerken. De oplossing is om een zoekopdracht uit te voeren en te vervangen op de rij data waar ik de hypen heb vervangen door een koppelteken. Excel loopt door de kolom en vervangt deze als het ware door een soort van vervanging, maar de resulterende factor is dat het nu de data herconfigureert! VAST!

Het lijkt erop dat Excel uw data niet herkent als datums, het herkent ze als tekst, vandaar dat u de Sorteer opties krijgt als A tot Z. Als u het correct doet, moet u zoiets als dit krijgen:

. Vandaar dat het belangrijk is dat Excel de datum herkent. De meest eenvoudige manier om dat te doen, is door gebruik te maken van de snelkoppeling CTRL+SHIFT+3.

Dit is wat ik met uw gegevens heb gedaan. Ik heb het gewoon gekopieerd van je bericht hierboven en heb het in Excel geplakt. Vervolgens heb ik de bovenstaande snelkoppeling toegepast, en kreeg ik de vereiste sorteeroptie. Controleer de afbeelding.

Ik vermoed dat het probleem is dat Excel het formaat niet kan begrijpen… Hoewel je het Date formaat kiest, blijft het als Text.

Je kunt dit gemakkelijk testen: Wanneer u probeert het datumformaat bij te werken, selecteer dan de kolom en klik er met de rechtermuisknop op en kies voor het formaat cellen (zoals u al doet) maar kies de optie 2001-03-14 (nabij de onderkant van de lijst). Bekijk dan het formaat van uw cellen.

Ik vermoed dat slechts enkele datumformaten correct worden bijgewerkt, wat aangeeft dat Excel het nog steeds als een string behandelt, niet als een datum (let op de verschillende formaten in de onderstaande schermafbeelding)!

Er zijn workarounds voor dit, maar, geen van hen zal worden geautomatiseerd, alleen maar omdat u elke keer exporteert. Ik stel voor om VBa te gebruiken, waarbij je eenvoudigweg een macro kunt uitvoeren die converteert van US naar UK datumformaat, maar, dit zou betekenen dat je de VBa elke keer moet kopiëren en plakken in je vel.

Als alternatief maak je een Excel aan die de nieuw aangemaakte geëxporteerde Excel-sheets inleest (van exchange) en voer je vervolgens de VBa uit om het datumformaat voor je bij te werken. Ik ga ervan uit dat het geëxporteerde Exchange Excel bestand altijd dezelfde bestandsnaam/directory heeft en een werkend voorbeeld geeft:

Dus maak een nieuw Excel bestand aan, genaamd ImportedData.xlsm (excel ingeschakeld). Dit is het bestand waarin we het Exported Exchange Excel bestand zullen importeren!

Voeg dit VBa toe aan het ImportedData.xlsm bestand

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

Wat ik ook gedaan heb is het datum formaat aangepast naar jjjjj-mm-dd omdat op deze manier, zelfs als Excel je het soort filter A-Z geeft in plaats van de nieuwste waarden, het nog steeds werkt!

Ik weet zeker dat de bovenstaande code fouten bevat, maar ik heb natuurlijk geen idee wat voor soort data je hebt, maar ik heb het getest met een enkele kolom data (zoals je hebt) en het werkt prima voor mij! Hoe voeg ik VBA toe in MS Office?

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680 ](https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680) Eenvoudige oplossing hier - maak een nieuwe kolom gebruik =datevalue(cel)-formule en kopieer vervolgens de formule naar uw andere rijen- een paar seconden om te repareren.

Ik heb het uitgezocht!

Er is een ruimte aan het begin en aan het einde van de datum.

1. Als je de spatiebalk zoekt en vervangt en drukt, zal het NIET werken.
2. 2. Je moet rechts klikken voor het maandnummer, druk op shift en de linker pijl om te selecteren en te kopiëren. Soms moet je de muis gebruiken om de spatie te selecteren.
3. 3. Plak dit dan als de spatie in zoek en vervang en al je data worden data.
4. Als er spatie en datum is selecteer je Data>Ga naar Data>Tekst naar kolommen>Delimited>Space als scheidingsteken en dan eindig je.
5. Alle spaties worden verwijderd.

Ik had geen geluk met alle bovenstaande suggesties - kwam met een zeer eenvoudige oplossing die werkt als een charme. Plak de gegevens in Google Sheets, markeer de datumkolom, klik op formaat, dan nummer en nog eens nummer om het te veranderen in nummerformaat. Vervolgens kopieer en plak ik de gegevens in de Excel-spreadsheet, klik op de datums, en formatteer vervolgens de cellen tot op heden. Heel snel. Ik hoop dat dit helpt.

Ik heb eenvoudigweg het getal 1 (één) gekopieerd en vermenigvuldigd met de datumkolom (gegevens) met behulp van de PASTE SPECIAL functie. Het verandert dan in Algemeen formatteren i.e 42102 Ga dan Datum toepassen op de formattering en het herkent het nu als datum.

Hoop dat dit helpt

Mijn oplossing in de UK - Ik had mijn dates met stippen als deze:

03.01.17

Ik loste dit op met het volgende:

1. Markeer de hele kolom.
2. 2. Ga naar zoeken en selecteer/vervanging.
3. 3. Ik heb alle punten vervangen door een middenstreepje bv 03.01.17 03-01-17.
4. 4. Houd de kolom geaccentueerd.
5. Formaat cellen, nummer tab selecteer datum.
6. Gebruik het Type 14-03-12 (essentieel voor de mijne om het middenstreepje te hebben)
7. Lokaal Engels (Verenigd Koninkrijk)

Bij het sorteren van de kolom worden alle datums voor het jaar gesorteerd.

SHARING A LITTLE LONG BUT MUCH EASIER SOLUTION TO SUBJECT….. Geen noodzaak om macro’s of geeky dingen te draaien….eenvoudige ms excel formules en bewerken.

mixed dates excel snapshot:

• De datum is in het gemengde formaat.
  
• Om een symmetrisch datumformaat te maken zijn er extra kolommen gemaakt die de ‘gemengde’ datumkolom omzetten naar TEXT.
• Uit die tekst hebben we de posities van “/” gehaald en de middelste tekst is met behulp van de MID-formule uitgepakt voor het bepalen van datum, maand en jaar.
• Diegene die oorspronkelijk data waren, formule eindigde met ERROR / #VALUE resultaat. - Ten slotte, uit de string die we hebben gemaakt - we hebben die geconverteerd naar data met behulp van DATE formule.
• #VALUE werden gekozen zoals het is door IFERROR toegevoegd aan DATE formule & de kolom werd geformatteerd zoals vereist (hier, dd/mmm/jj)

* REFER THE SNAPSHOT OF EXCEL SHEET ABOVE (= gemengde data blinken uit in een momentopname) *

Ik heb de verschillende suggesties uitgeprobeerd, maar vond de makkelijkste oplossing voor mij was als volgt…

Zie afbeeldingen 1 en 2 voor het voorbeeld… (let op - sommige velden waren opzettelijk verborgen omdat ze niet bijdragen aan het voorbeeld). Ik hoop dat dit helpt…

1. 1. Veld C - een gemengd formaat dat me helemaal gek maakte. Zo kwamen de gegevens rechtstreeks uit de database en hadden ze geen extra spaties of gekke karakters. Bij het weergeven als tekst bijvoorbeeld, werd de 01/01/2016 weergegeven als een ‘42504’ type waarde, vermengd met de 04/15/2006 die als is weergegeven.
2. 1. Veld F - waar ik de lengte van veld C heb verkregen (de LEN-formule zou een lengte van 5 of 10 zijn, afhankelijk van het datumformaat). Het veld is een algemeen formaat voor de eenvoud.

3. Veld F - waar ik de lengte van veld C heb verkregen (de LEN-formule zou een lengte van 5 of 10 zijn, afhankelijk van het datumformaat). 3. Veld G - waarbij ik de maandcomponent van de gemengde datum heb verkregen - op basis van het resultaat van de lengte in veld F (5 of 10), krijg ik de maand uit de datumwaarde in veld C, of krijg ik de maandkarakters in de reeks.

4. Veld G - waarbij ik de maandcomponent van de gemengde datum heb verkregen - op basis van het resultaat van de lengte in veld F (5 of 10), krijg ik de maand uit de datumwaarde in veld C, of krijg ik de maandkarakters in de reeks.

5. Veld F - waarbij de LEN-formule een lengte van 5 of 10, afhankelijk van het datumformaat, heeft. Veld H - verkrijgen van de dagcomponent van de gemengde datum - op basis van het lengte-resultaat in veld F (5 of 10), verkrijgt I ofwel de dag uit de datumwaarde in veld C, of verkrijgt de dagtekens in de reeks.

I kan dit ook voor het jaar doen, en dan een datum creëren uit de drie componenten die consistent is. Anders kan ik de individuele dag-, maand- en jaarwaarden in mijn analyse gebruiken. Afbeelding 1: basisspreadsheet met waarden en veldnamen Afbeelding 2: spreadsheet met formules die overeenkomen met de uitleg boven

Ik hoop dat dit helpt.

Dit probleem maakte me gek, toen struikelde ik over een makkelijke oplossing. Het werkte tenminste voor mijn gegevens. Het is makkelijk te doen en te onthouden. Maar ik weet niet waarom dit werkt, maar het veranderen van types zoals hierboven aangegeven werkt niet. 1. 2. Selecteer de kolom(s) met de data. 3. Zoek een jaar in uw data, bijvoorbeeld 2015. 3. Selecteer Vervang alles door hetzelfde jaar, 2015. 3. 3. Herhaal dit voor elk jaar. Dit verandert de typen in daadwerkelijke data, jaar voor jaar. Dit is nog steeds omslachtig als u vele jaren in uw gegevens heeft. Sneller is het zoeken naar en vervangen van 201 door 201 (om 2010 tot en met 2019 te vervangen); vervang 200 door 200 (om 2000 tot en met 2009 te vervangen); enzovoort.

Selecteer data en voer deze code uit…

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

Alle bovenstaande oplossingen - inclusief r0berts - waren niet succesvol, maar vonden deze bizarre oplossing die de snelste oplossing bleek te zijn.

Ik probeerde “data” te sorteren op een gedownloade spreadsheet van accounttransacties.

Dit was gedownload via CHROME-browser. Geen enkele hoeveelheid manipulatie van de “data” zou ze herkennen als oud-naar-nieuw sorteerbaar.

Maar, toen ik downloadde via INTERNET EXPLORER browser - geweldig - kon ik direct sorteren zonder een kolom aan te raken.

Ik kan niet verklaren waarom verschillende browsers de opmaak van data beïnvloeden, behalve dat het duidelijk een zeer snelle oplossing was en was.

Als Excel hardnekkig weigert je kolom als datum te herkennen, vervang deze dan door een andere :

• Voeg een nieuwe kolom toe aan de rechterkant van de oude kolom
• Klik met de rechtermuisknop op de nieuwe kolom en selecteer Format
• Stel het formaat in op date
• Markeer de hele oude kolom en kopieer deze
• Markeer de bovenste cel van de nieuwe kolom en selecteer Paste Special, en plak alleen values
• Je kunt nu de oude kolom verwijderen.

Dit gebeurt de hele tijd bij het uitwisselen van datumgegevens tussen locaties in Excel. Als u controle heeft over het VBA-programma dat de gegevens exporteert, stel ik voor om het nummer dat de datum vertegenwoordigt te exporteren in plaats van de datum zelf. Het nummer komt uniek overeen met een enkele datum, ongeacht de opmaak en de locatie. Bijvoorbeeld, in plaats van het CSV-bestand met 24/09/2010 toont het 40445.

In de exportlus als u elke cel vasthoudt, als het een datum is, converteer dan naar nummer met behulp van CLng(myDateValue). Dit is een voorbeeld van mijn lus die door alle rijen van een tabel loopt en exporteert naar CSV (let op: ik vervang ook komma’s in strings door een tag die ik strip bij het importeren, maar dit heb je misschien niet nodig):

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

Ik gebruik een mac.

Ga naar excel voorkeuren> Bewerken> Datum opties> Automatisch datumsysteem omzetten

Ook,

Ga naar Tabellen & Filters> Groepsdata bij het filteren.

Het probleem is waarschijnlijk begonnen toen je een bestand met een ander datumsysteem kreeg, en dat je excel datumfunctie verknoeide.

Ik maak meestal gewoon een extra kolom aan voor het sorteren of totalen, dus gebruik year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

Dat levert een yyyymmdd resultaat op dat gesorteerd kan worden. Met het gebruik van len(a1) kan gewoon een extra nul worden toegevoegd voor de maanden 1-9.