Nie mogę dostać się do Excela, aby rozpoznać datę w kolumnie

**Problem polega na tym, że Excel nie chce rozpoznawać dat jako dat, mimo że przez “Formatowanie komórek - Numer - Niestandardowe” wyraźnie próbujesz powiedzieć, że są to daty przez “mm/dd/yyyy”. Jak wiesz, gdy program Excel rozpoznał coś jako datę, zapisuje to dalej jako liczbę - np. “41004”, ale wyświetla jako datę zgodnie z formatem, który określasz. Aby dodać do dezorientacji, program Excel może przekonwertować tylko część twoich dat, np. 08/04/2009, ale pozostawić inne, np. 07/28/2009, bez konwersji.

Rozwiązanie: kroki 1, a następnie 2

1) Wybierz kolumnę z datą. W zakładce Dane wybrać przycisk Tekst do Kolumny. Na pierwszym ekranie pozostawić przycisk radiowy na “ delimited” i kliknąć Next. Odblokuj dowolne pole ogranicznika (dowolne pola ** puste** ; bez znaczników wyboru) i kliknij _Następny. Pod formatem danych kolumny wybierz Data i wybierz MDY w sąsiednim polu kombi i kliknij Kończ. Teraz masz już wartości daty (tzn. Excel rozpoznał Twoje wartości jako typ danych Date), ale formatowanie jest prawdopodobnie nadal datą locale, a nie mm/dd/yyyy, który chcesz.

2) Aby uzyskać poprawne wyświetlanie pożądanego formatu daty USA należy najpierw wybrać kolumnę (jeśli nie jest zaznaczona) a następnie pod Cell Format - Number wybrać Date i wybrać Locale : English (US). Dzięki temu uzyskasz format typu “m/d/yy”. Następnie możesz wybrać Custom i tam wpisać “mm/dd/yyyy” lub wybrać go z listy niestandardowych ciągów znaków.

Alternative : użyj LibreOffice Calc. Po wklejeniu danych z postu Patricka wybierz Paste Special (Ctrl+Shift+V) i wybierz Unformatted Text. Spowoduje to otwarcie okna dialogowego “Importuj tekst”. Zestaw znaków pozostaje Unicode, ale dla języka wybierz English(USA); należy również zaznaczyć pole “Detect special numbers”. Twoje daty natychmiast pojawiają się w domyślnym formacie amerykańskim i są sortowalne. Jeśli chcesz mieć specjalny amerykański format MM/DD/YYYY, musisz to określić raz poprzez “format Komórki” - albo przed albo po wklejeniu.

Można powiedzieć - Excel powinien mieć rozpoznane daty jak tylko powiem to poprzez “Format Komórkowy” i Nie mogłem się zgodzić więcej. Niestety tylko poprzez krok 1 od góry udało mi się sprawić, że Excel rozpoznał te ciągi tekstowe jako daty. Oczywiście, jeśli robisz to dużo to jest ból w szyi i można umieścić razem wizualną podstawową procedurę, która zrobiłaby to dla Ciebie za naciśnięciem przycisku.

Dane |Tekst do kolumn

Uaktualnij na wiodący apostrof po wklejeniu:Możesz zobaczyć w pasku formuły, że w komórce, gdzie data nie jest rozpoznawany jest wiodący apostrof. Oznacza to, że w komórce sformatowanej jako liczba (lub data) znajduje się łańcuch tekstowy. Można powiedzieć - apostrof wiodący uniemożliwia rozpoznanie numeru w arkuszu kalkulacyjnym. Musisz wiedzieć, żeby to sprawdzić w pasku formuły - ponieważ arkusz kalkulacyjny wyświetla po prostu to, co wygląda jak liczba ustawiona po lewej stronie. Aby rozwiązać ten problem, wybierz kolumnę, którą chcesz poprawić, wybierz w menu Data | Text to Columns i kliknij OK. Czasami będziesz mógł określić typ danych, ale jeśli wcześniej ustawiłeś format kolumny jako swój konkretny typ danych - nie będziesz go potrzebował. Polecenie ma na celu podzielenie kolumny tekstowej na dwie lub więcej części za pomocą delimitera, ale działa jak urok dla tego problemu. Przetestowałem to w Libreoffice, ale jest też ta sama pozycja menu w Excel.

Wybierz całą kolumnę i przejdź do Locate and Replace i po prostu zamień "/" na /.

To może nie być istotne dla oryginalnego pytającego, ale może pomóc komuś, kto nie jest w stanie posortować kolumnę dat.

Stwierdziłem, że Excel nie rozpoznałby kolumny dat, które były wszystkie przed 1900 r., podkreślając, że były one kolumną tekstu (ponieważ 1/1/1900 ma numeryczny odpowiednik 1, a liczby ujemne są najwyraźniej niedozwolone). Zrobiłem więc ogólną zamianę wszystkich moich dat (które były w latach 1800-nych), aby umieścić je w latach 1900-nych, np. 180 -> 190, 181 -> 191, itd. Proces sortowania działał wtedy dobrze. W końcu zastąpiłem je w inny sposób, np. 190 -> 180.

Mam nadzieję, że to pomoże jakiemuś innemu historykowi.

Podobną sprawą zajmowałem się z tysiącami wierszy wydobytych z bazy danych SAP i, niewytłumaczalnie, dwoma różnymi formatami daty w tej samej kolumnie daty (większość z nich to nasz standardowy “RRRR-MM-DD”, ale około 10% to “MM-DD-YYYY”). Ręczna edycja 650 wierszy raz na miesiąc nie była opcją.

Żadna (zero… 0… zero) z powyższych opcji nie działała. Tak, wypróbowałem je wszystkie. Kopiowanie do pliku tekstowego lub wyraźne eksportowanie do txt nadal nie miało żadnego wpływu na format (lub jego brak) 10% dat, które po prostu siedziały w swoim rogu odmawiając zachowania.

Jedynym sposobem, w jaki udało mi się to naprawić, było wstawienie pustej kolumny po prawej stronie źle zachowującej się kolumny z datą i użycie następującej, dość uproszczonej formuły:

(zakładając, że źle zachowujące się dane znajdują się w 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")))

Następnie mogłem skopiować wyniki w mojej nowej, obliczonej kolumnie nad błędnie zachowującymi się datami poprzez wklejenie “Wartości”, po czym mogłem usunąć moją obliczoną kolumnę.

Miałem dokładnie ten sam problem z datami w excelu. Format odpowiadał wymaganiom dla daty w programie Excel, ale bez edycji każdej komórki nie rozpoznałby daty, a gdy masz do czynienia z tysiącami wierszy, nie jest praktyczna ręczna edycja każdej komórki. Rozwiązaniem jest uruchomienie wyszukiwania i zamiany w wierszu daty, w którym zamieniłem myślnik na myślnik. Excel działa przez kolumnę zastępując jak z podobnymi, ale wynikającym z tego czynnikiem jest to, że teraz rekompensuje daty! STAŁE!

Wydaje się, że Excel nie rozpoznaje Twoich dat jako daty, rozpoznaje je w tekście, stąd też otrzymujesz opcje sortowania od A do Z. Jeśli zrobisz to poprawnie, powinieneś otrzymać coś takiego:

Stąd ważne jest, aby upewnić się, że Excel rozpoznaje datę. Najprostszym sposobem na to jest użycie skrótu CTRL+SHIFT+3.

Oto co zrobiłem z twoimi danymi. Po prostu skopiowałem je z twojego postu powyżej i wkleiłem je w excelu. Następnie zastosowałem powyższy skrót i otrzymałem wymaganą opcję sortowania. Sprawdź obrazek.

Podejrzewam, że chodzi o to, że Excel nie jest w stanie zrozumieć formatu… Mimo, że wybierzesz format Data, pozostanie on jako Tekst.

Możesz to łatwo przetestować: Kiedy próbujesz zaktualizować format daty, wybierz kolumnę i kliknij na nią prawym przyciskiem myszy i wybierz komórki formatu (jak już to robisz), ale wybierz opcję 2001-03-14 (w pobliżu dolnej części listy). Następnie przejrzyj format swoich komórek.

Podejrzewam, że tylko niektóre formaty daty są aktualizowane poprawnie, wskazując, że Excel nadal traktuje je jak ciąg znaków, a nie datę (zwróć uwagę na różne formaty w poniższym zrzucie ekranu)!

Istnieją ku temu rozwiązania robocze, ale żadne z nich nie zostanie zautomatyzowane tylko dlatego, że za każdym razem eksportujesz. Sugerowałbym korzystanie z VBa, gdzie można po prostu uruchomić makro, które konwertuje z USA do UK formatu daty, ale, oznaczałoby to kopiowanie i wklejanie VBa do swojego arkusza za każdym razem.

Alternatywnie, można utworzyć Excel, który odczytuje nowo utworzone wyeksportowane arkusze Excel (z wymiany), a następnie uruchomić VBa do aktualizacji formatu daty dla Ciebie. Założę, że wyeksportowany plik Excela Exchange zawsze będzie miał tę samą nazwę/katalog i podam przykład roboczy:

Tak więc utwórz nowy arkusz Excela o nazwie ImportedData.xlsm (Excel włączony). Jest to plik, do którego będziemy importować wyeksportowany plik Exchange Excel!

Dodaj ten VBa do pliku 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

Co również zrobiłem to zaktualizowałem format daty do yyyyy-mm-dd ponieważ w ten sposób nawet jeśli Excel daje filtr sortowania A-Z zamiast najnowszych wartości, to nadal działa!

Jestem pewien, że powyższy kod ma błędy, ale oczywiście nie mam pojęcia jakie masz dane, ale przetestowałem go z jedną kolumną dat (tak jak ty) i działa dla mnie dobrze! Jak dodać VBA w MS Office?

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

Proste rozwiązanie tutaj - stwórz nową kolumnę use =datevalue(cell) formula następnie skopiuj formułę do innych wierszy - kilka sekund, aby naprawić

Rozgryzłem to!

Na początku i na końcu daty jest spacja.

1. Jeśli użyjesz znajdź i wymień i naciśnij spację, to NIE będzie działać.
2. Musisz kliknąć prawym przyciskiem myszy przed numerem miesiąca, wcisnąć shift i strzałkę w lewo, aby wybrać i skopiować. Czasami trzeba użyć myszy, aby wybrać spację.
3. Następnie wklej to jako spację w znalezieniu i zastąp, a wszystkie twoje daty staną się datami.
4. Jeśli jest spacja i data Wybierz Dane →Go to Data →Text to columns →Delimited →>Space as separator a następnie zakończ.
5. Wszystkie spacje zostaną usunięte.

Nie miałem szczęścia do wszystkich powyższych propozycji - wymyśliłem bardzo proste rozwiązanie, które działa jak urok. Wklej dane do Google Sheets, podświetl kolumnę z datą, kliknij na format, a następnie numer i jeszcze raz numer, aby zmienić go na format numer. Następnie kopiuję i wklejam dane do arkusza kalkulacyjnego Excel, klikam na daty, a następnie formatuję komórki do daty. Bardzo szybko. Mam nadzieję, że to pomoże.

Po prostu skopiowałem liczbę 1 (jeden) i pomnożyłem ją do kolumny z datą (danymi) za pomocą funkcji PASTE SPECIAL. Następnie zmienił się na formatowanie ogólne i.e 42102 Następnie zastosował datę na formatowaniu i teraz rozpoznaje ją jako datę.

Mam nadzieję, że to pomoże

Moje rozwiązanie w Wielkiej Brytanii - miałam swoje randki z kropkami w nich tak:

03.01.17

Rozwiązałam to w następujący sposób:

1. Podświetl całą kolumnę.
2. Idź do znalezienia i wybierz/wymianę.
3. Zastąpiłem wszystkie kropki środkową kreską np. 03.01.17 03-01-17.
4. Pozostaw kolumnę podświetloną.
5. Formatuj komórki, zakładka numeryczna wybierz datę.
6. Użyj Typu 14-03-12 (niezbędny, aby mój miał środkową kreskę)
7. Locale English (Wielka Brytania)

Podczas sortowania kolumny sortowane są wszystkie daty na rok.

SHARING A LITTLE LONG BUT MUCH EASIER SOLUTION TO SUBJECT… Nie trzeba uruchamiać makr ani maniakalnych rzeczy…proste formuły ms excel i edycja.

mixed dates excel snapshot:

• Data jest w formacie mieszanym.
  
• W związku z tym, aby stworzyć symetryczny format daty, stworzono dodatkowe kolumny konwertujące kolumnę daty w formacie mieszanym na TEXT.
• Na podstawie tego tekstu zidentyfikowaliśmy pozycje “/” i wyodrębniliśmy tekst środkowy określający datę, miesiąc, rok za pomocą formuły MID.
• Te, które były pierwotnie datami, formuła zakończyła się wynikiem ERROR / #VALUE. - Ostatecznie, z utworzonego przez nas ciągu znaków - przekształciliśmy je na daty za pomocą formuły DATA.
• #VALUE zostały wybrane tak, jak to jest w formule IFERROR dodane do formuły DATA i kolumna została sformatowana zgodnie z wymaganiami (tutaj dd/mmm/rr)

* ODNIESIENIE DO SNAPSZTU EXCEL SHEET ABOVE (= mieszane daty excel snapshot) *

Próbowałem różnych sugestii, ale najłatwiejszym rozwiązaniem było dla mnie…

Patrz rysunki 1 i 2 dla przykładu… (uwaga - niektóre pola zostały celowo ukryte, ponieważ nie przyczyniają się do powstania przykładu). Mam nadzieję, że to pomoże…

1. Pole C - format mieszany, który doprowadził mnie do kompletnego szaleństwa. W ten sposób dane pochodziły bezpośrednio z bazy danych i nie miały żadnych dodatkowych spacji ani zwariowanych znaków. Wyświetlając go na przykład jako tekst, 01/01/2016 wyświetlił jako wartość typu “42504”, zmieszaną z 04/15/2006, który pokazał się jak jest.

2. Pole F - gdzie uzyskałem długość pola C (formuła LEN będzie miała długość 5 z 10 w zależności od formatu daty). Pole to jest formatem ogólnym dla uproszczenia.

3. Pole G - otrzymanie składowej miesiąca daty mieszanej - na podstawie wyniku długości w polu F (5 lub 10), I albo otrzymuję miesiąc od wartości daty w polu C, albo otrzymuję znaki miesiąca w łańcuchu.

4. Pole H - otrzymanie składowej dziennej daty mieszanej - na podstawie wyniku długości w polu F (5 lub 10), I albo otrzymuję dzień od wartości daty w polu C, albo otrzymuję znaki dnia w łańcuchu.

Mogę to zrobić również dla roku, a następnie stworzyć datę z trzech składników, które są spójne. W przeciwnym razie, mogę użyć poszczególnych wartości dnia, miesiąca i roku w mojej analizie. Obrazek 1: podstawowy arkusz kalkulacyjny pokazujący wartości i nazwy pól Obrazek 2: arkusz kalkulacyjny pokazujący formuły pasujące do objaśnień powyżej

Mam nadzieję, że to pomoże.

Ten problem doprowadzał mnie do szału, potem potknąłem się o łatwą sprawę. Przynajmniej zadziałało to na moje dane. To jest łatwe do zrobienia i zapamiętania. Ale nie wiem, dlaczego to działa, ale zmiana typów, jak wskazano powyżej, nie działa. 1. Wybierz kolumnę(y) z datami 2. Wyszukaj rok w swoich datach, na przykład 2015. Wybierz opcję Zamień wszystko z tym samym rokiem, 2015. 3. Powtórzyć dla każdego roku. Zmienia to typy na rzeczywiste daty, rok po roku. Jest to nadal uciążliwe, jeśli masz wiele lat w swoich danych. Szybciej jest wyszukać i zastąpić 201 z 201 (zastąpić 2010 do 2019); zastąpić 200 z 200 (zastąpić 2000 do 2009); i tak dalej.

Wybierz daty i uruchom ten kod nad nim…

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

Wszystkie powyższe rozwiązania - w tym r0berts - zakończyły się niepowodzeniem, ale znalazłem to dziwaczne rozwiązanie, które okazało się najszybszą naprawą.

Próbowałem posortować “daty” na pobranym arkuszu transakcji konta.

Zostało to pobrane za pomocą przeglądarki CHROME. Żadna ilość manipulacji przy “datach” nie pozwoliłaby na rozpoznanie ich jako starych do nowych.

Ale potem ściągnąłem je przez przeglądarkę INTERNET EXPLORER - niesamowite - mogłem sortować natychmiast bez dotykania kolumny.

Nie potrafię wyjaśnić, dlaczego różne przeglądarki wpływają na formatowanie danych, poza tym, że wyraźnie tak było i była to bardzo szybka poprawka.

Jeśli program Excel uparcie odmawia uznania Twojej kolumny za datę, zastąp ją inną :

• Dodaj nową kolumnę po prawej stronie starej kolumny
• Kliknij prawym przyciskiem myszy na nową kolumnę i wybierz Format
• Ustaw format na date
• Podświetl całą starą kolumnę i skopiuj ją
• Podświetl górną komórkę nowej kolumny i wybierz Paste Special, a tylko wklej values
• Możesz teraz usunąć starą kolumnę.

Dzieje się tak cały czas podczas wymiany danych o dacie pomiędzy lokalizacjami w programie Excel. Jeżeli masz kontrolę nad programem VBA, który eksportuje dane, proponuję wyeksportować numer przedstawiający datę zamiast samej daty. Liczba ta jest jednoznacznie skorelowana z jedną datą, niezależnie od formatowania i lokalizacji. Na przykład, zamiast pliku CSV pokazującego 24/09/2010 pokaże 40445.

W pętli eksportu, gdy trzymasz każdą komórkę, jeżeli jest to data, skonwertuj na numer przy pomocy CLng(myDateValue). Jest to przykład mojej pętli przebiegającej przez wszystkie wiersze tabeli i eksportującej do CSV (zauważ, że przecinki w łańcuchach również zastępuję tagiem, który stripuję podczas importu, ale może to nie być potrzebne):

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

Używam komputera Mac.

Przejdź do preferencji programu Excel.

Przejdź do tabel i filtrów.

Problem prawdopodobnie zaczął się, gdy otrzymałeś plik z innym systemem daty, a to spieprzyło Twoją funkcję daty w programie Excel.

Zazwyczaj po prostu tworzę dodatkową kolumnę do celów sortowania lub sumowania, więc użyj year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

To da wynik yyyymmdd, który może być sortowany. Użycie len(a1) pozwala tylko na dodanie dodatkowego zera dla miesięcy 1-9.