Nie można ustawić Excela na rozpoznanie daty w kolumnie


45

Ciągle mam problemy z datami w Excelu, muszę robić coś złego, ale nie rozumiem co.

Mam arkusz kalkulacyjny wyeksportowany z naszego serwera wymiany, który zawiera kolumnę z datami. Zostały wydane w formacie amerykańskim, mimo że jestem w Wielkiej Brytanii.

Ta kolumna wygląda następująco

04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011

W programie Excel podświetliłem kolumnę i wybrałem Format Cells.... W tym oknie dialogowym, mam zaznaczone Date, wybrany English (United States)jako lokalizacji i wybrany format daty dopasowanie z listy. Nacisnąłem OK i próbuję posortować dane według tej kolumny.

W oknie dialogowym sortowania wybieram tę kolumnę, wybierz sortuj według Wartości, ale kolejność daje mi tylko opcje od A do Z, a nie od najstarszych do najnowszych, jak bym się spodziewał.

To z kolei sortuje dane daty według pierwszych dwóch cyfr.

Wiem, że mógłbym ponownie sformatować te dane do ISO, a wtedy sortowanie od A do Z działałoby, ale ja też nie powinienem, oczywiście czegoś brakuje. Co to jest?

EDYCJA: Pomieszałem nagrodę, ale powinna była przejść do odpowiedzi @ r0berts , jego pierwszej sugestii tekstu do kolumn bez separatora i wybrania „MDY” jako rodzaju danych. Dodatkowo, jeśli masz czas (tj. 04/21/2015 18:34:22), Musisz najpierw pozbyć się danych czasu. Jednak po tym metoda sugerowana przez @ r0berts działa dobrze.


Podejrzewam, że problemem jest to, że Excel nie wie, jak to zrobić - możesz przetestować. Podczas próby zaktualizowania formatu dat wybierz kolumnę i kliknij go prawym przyciskiem myszy, a następnie wybierz formatuj komórki (jak już to robisz), ale wybierz opcję 2001-03-14 (u dołu listy). Podejrzewam, że tylko niektóre formaty daty aktualizują się poprawnie, co wskazuje, że Excel nadal traktuje to jak ciąg, a nie datę!
Dave

Jaki jest format eksportowanych danych? Czy to CSV czy XLSX?
Excellll,

Odpowiedzi:


70

Problem: Excel nie chce rozpoznawać dat jako dat, mimo że poprzez „Formatuj komórki - Liczba - Niestandardowe” wyraźnie próbujesz powiedzieć, że są to daty do „ mm/dd/yyyy”. Jak wiesz; gdy program Excel rozpoznaje coś jako datę, przechowuje to dalej jako liczbę - na przykład „ 41004”, ale wyświetla się jako data zgodnie z określonym formatem. Aby dodać do zamieszania, excel może konwertować tylko część twoich dat, takich jak 08/04/2009, ale pozostawiać inne, np. 28.07.2009, bez konwersji.

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

1) Wybierz kolumnę daty. W obszarze Dane wybierz przycisk Tekst do kolumn. Na pierwszym ekranie pozostaw przycisk opcji „ rozdzielany ” i kliknij Dalej . Usuń zaznaczenie dowolnego pola separatora ( puste pola ; żadnych znaczników wyboru) i kliknij Dalej . W kolumnie danych w formacie wybierz Data i MDY w sąsiednim polu kombi i kliknij Zakończ . Teraz masz wartości daty (tj. Excel rozpoznał twoje wartości jako Datetyp danych), ale formatowanie prawdopodobnie nadal jest datą lokalizacji, a nie taką, mm/dd/yyyyjaką chcesz.

2) Aby poprawnie wyświetlić żądany format daty w USA, najpierw musisz wybrać kolumnę (jeśli nie jest zaznaczona), a następnie w polu Format komórki - Liczba wybierz Data ORAZ wybierz Ustawienia regionalne: angielski (amerykański) . Otrzymasz format taki jak „ m/d/yy”. Następnie możesz wybrać Niestandardowy i tam możesz wpisać „ mm/dd/yyyy” lub wybrać z listy niestandardowych ciągów.

Alternatywnie : użyj LibreOffice Calc. Po wklejeniu danych z posta Patricka wybierz Wklej specjalnie ( Ctrl+Shift+V) i wybierz Niesformatowany tekst. Otworzy się okno dialogowe „Importuj tekst”. Zestaw znaków pozostaje Unicode, ale dla języka wybierz angielski (USA); należy również zaznaczyć pole „Wykryj numery specjalne”. Twoje daty są natychmiast wyświetlane w domyślnym formacie amerykańskim i można je sortować według dat. Jeśli chcesz mieć specjalny amerykański format MM / DD / RRRR, musisz to określić raz za pomocą „Formatuj komórki” - przed wklejeniem lub po nim.

Można powiedzieć, że Excel powinien rozpoznać daty, gdy tylko powiem to przez „Format komórki” i nie mogę się zgodzić . Niestety dopiero w kroku 1 powyżej udało mi się sprawić, że Excel rozpoznaje te ciągi tekstowe jako daty. Oczywiście, jeśli często to robisz, jest to ból szyi i możesz stworzyć wizualną podstawową procedurę, która zrobiłaby to za naciśnięciem jednego przycisku.

Dane | Tekst do kolumn

Zaktualizuj wiodący apostrof po wklejeniu: Na pasku formuły widać, że w komórce, w której data nie jest rozpoznawana, znajduje się wiodący apostrof. Oznacza to, że w komórce sformatowanej jako liczba (lub data) znajduje się ciąg tekstowy. Można powiedzieć - wiodący apostrof uniemożliwia arkuszowi kalkulacyjnemu rozpoznanie liczby. Musisz to sprawdzić w pasku formuły - ponieważ arkusz kalkulacyjny po prostu wyświetla coś, co wygląda jak liczba wyrównana do lewej. Aby rozwiązać ten problem, wybierz kolumnę, którą chcesz poprawić, wybierz z menuData | Text to Columnsi kliknij OK. Czasami będziesz w stanie określić typ danych, ale jeśli wcześniej ustawiłeś format kolumny na swój konkretny typ danych - nie będziesz go potrzebował. Polecenie to naprawdę ma na celu podzielenie kolumny tekstowej na dwie lub więcej za pomocą separatora, ale działa to również jak urok tego problemu. Ja testowałem go w LibreOffice , ale tam jest taka sama pozycja menu w Excelu też.


Odnoszę się do obu twoich punktów w moim OP. Podzielenie wartości daty na kolumny (a następnie ponowna integracja w formacie ISO) jest oczywiście możliwe, ale biorąc pod uwagę, że Excel ma rozbudowane wbudowane funkcje daty, chciałbym móc je wykorzystać przy użyciu prawidłowych danych daty, które posiadam. Twoja druga opcja jest dokładnie taka, jak opisuję to, co nie działa dla mnie.
Patrick

Drogi Patricku, przeczytaj uważnie. Lub możesz użyć Libre Office Calc - z twoimi danymi to działa od razu - po prostu wstępnie sformatuj kolumnę Data - angielski (USA), a po wklejeniu wybierz „Wklej specjalnie” „Niesformatowany tekst” - po prostu powiedz w oknie dialogowym, że język to Angielski (USA) i nie zapomnij zaznaczyć pola, aby rozpoznać numery specjalne. W mojej odpowiedzi starałem się jak najmocniej zasugerować, że Excel jest niespójny z tym (błędem) i musisz skorzystać z tych kroków 1, a następnie 2, aby osiągnąć pożądany rezultat.
r0berts

Powinienem był właściwie przeczytać twoje pytanie. Twoja pierwsza sugestia rzeczywiście działa. Powinieneś był otrzymać nagrodę. Dziękuję za Twoją pomoc.
Patrick

Sugestia 1 działa jak urok. dzięki.
Adam

Naprawdę dobra odpowiedź !!!
Adders

8

Zaznacz wszystkie kolumny i przejść, aby zlokalizować i wymienić i po prostu zastąpić "/"z /.


Czy możesz wyjaśnić, co to robi, aby rozwiązać problem?
fixer1234,

To naprawdę działało i było łatwe. Zastąpienie wystarczy usunąć wiodące zera
PedroGabriel

2
Po pierwsze, TO DZIAŁA. NAJLEPSZE rozwiązanie. DALEKO lepiej niż sprawdzone rozwiązanie :). Aby fixer1234 i @PedroGabriel: To nie jest tak, że usuwa zera. Podczas zastępowania zawartości komórki program Excel ocenia, czy wynikowa komórka zawiera formułę, tekst lub liczbę. Po wykonaniu zamiany „/” interpretuje komórkę, aby zawierała teraz liczbę (która jest technicznie datą w Excelu), więc jest teraz poprawnie sortowalna. (Sprytni użytkownicy mogą rozpoznać technikę znajdowania / zastępowania znakiem „=” w podobnym scenariuszu z programem Excel reinterpretującym zawartość.) W każdym razie Jair ma idealne rozwiązanie :)
MicrosoftShouldBeKickedInNuts

@MicrosoftShouldBeKickedInNuts dzisiaj Nie mam pojęcia, dlaczego powiedziałem, że o zerach, to naprawdę nie ma nic wspólnego z zerami ... To najlepsza odpowiedź, wciąż z tą jedną. Pozdrawiam
PedroGabriel

3

Miałem dokładnie ten sam problem z datami w programie Excel. Format spełnił wymagania dotyczące 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 praktyczne ręczne edytowanie każdej komórki. Rozwiązaniem jest uruchomienie funkcji znajdź i zamień w wierszu dat, w którym zastąpiłem łącznik łącznikiem. Program Excel przechodzi przez kolumnę, zastępując znak podobny do podobnego, ale wynikowy czynnik polega na tym, że teraz ponownie oblicza daty! NAPRAWIONY!


3

Miałem do czynienia z podobnym problemem z tysiącami wierszy wyodrębnionych z bazy danych SAP i, niewytłumaczalnie, dwoma różnymi formatami dat w tej samej kolumnie daty (większość to nasze standardowe „RRRR-MM-DD”, ale około 10% to „MM- DD-RRRR ”). Ręczna edycja 650 wierszy raz w miesiącu nie była opcją.

Żadna (zero ... 0 ... zero) powyższych opcji nie zadziałała. Tak, wypróbowałem je wszystkie. Kopiowanie do pliku tekstowego lub jawne eksportowanie do txt wciąż nie miało żadnego wpływu na format (lub jego brak) 10% dat, które po prostu siedziały w kącie, odmawiając zachowania.

Jedynym sposobem, w jaki udało mi się to naprawić, było wstawienie pustej kolumny po prawej stronie niewłaściwej kolumny daty i użycie następującej, dość uproszczonej formuły:

(zakładając, że Twoje błędnie zachowujące się dane są 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")))

Mógłbym następnie skopiować wyniki w mojej nowej, obliczonej kolumnie nad niepoprawnymi datami, wklejając „Wartości” tylko po czym mogłem usunąć moją kolumnę obliczoną.


3

Może to nie dotyczyć pierwotnego pytającego, ale może pomóc komuś innemu, kto nie jest w stanie posortować kolumny dat.

Odkryłem, że Excel nie rozpoznaje kolumny dat, które były wszystkie przed 1900 rokiem, nalegając, aby były one kolumną tekstu (ponieważ 1/1/1900 ma numeryczny odpowiednik 1, a liczby ujemne najwyraźniej nie są dozwolone). Zrobiłem więc ogólną zamianę wszystkich moich dat (które były w 1800 roku), aby umieścić je w latach 1900, np. 180 -> 190, 181 -> 191 itd. Następnie proces sortowania działał dobrze. Wreszcie zrobiłem wymianę w inny sposób, np. 190 -> 180.

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


2

Wygląda na to, że Excel nie rozpoznaje twoich dat jako dat, rozpoznaje ich tekst, dlatego otrzymujesz opcje Sortuj od A do Z. Jeśli zrobisz to poprawnie, powinieneś otrzymać coś takiego:

http://i.stack.imgur.com/Qb4Pj.png

Dlatego ważne jest, aby Excel rozpoznał datę. Najprostszym sposobem na to jest użycie skrótu CTRL+SHIFT+3.

Oto, co zrobiłem z Twoimi danymi. Po prostu skopiowałem go z powyższego wpisu i wkleiłem w programie Excel. Następnie zastosowałem powyższy skrót i dostałem wymaganą opcję sortowania. Sprawdź obraz.

http://i.stack.imgur.com/yAa6a.png


2
Niestety ten skrót nie działa dla mnie.
Patrick

Co robi skrót w twoim systemie Patrick.
Firee

3
Skrót wydaje się w ogóle nic nie robić. Mam do dyspozycji kilka systemów z Office 2007, 2010 i 2013 i żaden z nich nie reaguje na skrót CTRL SHIFT 3. CTRL 1 poprawnie wyświetla okno dialogowe „Formatuj komórkę”.
Patrick

Ctrl + skrót Shift + 3 klawiatura, odwołuje nieco niechlujnie jak „Ctrl + Shift + #” tutaj , oznacza „Stosuje format Data dzień, miesiąc i rok.” W programie Excel 2007, 2010, 2013 i 2016 (Specyficznie , w moim Excelu 2013 jest to „d-mmm-yy”.) Dokument Microsoft nie wyjaśnia, że ​​komórki muszą już zawierać… (ciąg dalszy)
Scott

(Ciąg dalszy) ... Wartości, które Excel rozpoznaje dane data / czas, i wydaje się, że nikt nie ma tu wyraźnie wyjaśnił, że od Patrick jest w Anglii, jego wersja Excel rozpoznaje ciągi jak 04/08/2012, 04/09/2009i 04/01/2010jako 4-sie-2012 , Odpowiednio, 4 września 2009 r. I 4 stycznia 2010 r. I traktuje się 04/21/2011jak ciąg tekstowy (ponieważ nie ma 21 miesiąca). Ponieważ niektóre wartości są tekstem (a niektóre wartości prawdopodobnie zostały błędnie zinterpretowane), formatowanie (takie jak skrót klawiszowy Ctrl + Shift + 3) nie przyniesie nic dobrego.
Scott

2

Podejrzewam, że problemem jest to, że Excel nie jest w stanie zrozumieć formatu ... Chociaż wybierzesz format daty, pozostaje on tekstem.

Możesz to łatwo przetestować: kiedy próbujesz zaktualizować format dat, wybierz kolumnę i kliknij go prawym przyciskiem myszy i wybierz formatuj komórki (jak już to robisz), ale wybierz opcję 2001-03-14 (u dołu listy). Następnie sprawdź format swoich komórek.

Podejrzewam, że tylko niektóre formaty daty aktualizują się poprawnie, co wskazuje, że Excel nadal traktuje to jak ciąg, a nie datę (zwróć uwagę na różne formaty na poniższym zrzucie ekranu)!

wprowadź opis zdjęcia tutaj

Istnieją obejścia tego problemu, ale żadne z nich nie zostanie zautomatyzowane tylko dlatego, że eksportujesz za każdym razem. Sugerowałbym użycie VBa, gdzie można po prostu uruchomić makro, które konwertuje format daty z USA na Zjednoczone Królestwo, ale oznaczałoby to kopiowanie i wklejanie VBa za każdym razem.

Alternatywnie możesz utworzyć program Excel, który odczytuje nowo utworzone wyeksportowane arkusze Excela (z wymiany), a następnie uruchomić VBa, aby zaktualizować format daty. Zakładam, że wyeksportowany plik programu Excel Excel zawsze będzie miał tę samą nazwę / katalog i podam działający przykład:

Utwórz nowy arkusz Excela o nazwie ImportedData.xlsm (excel włączony). To jest plik, do którego zaimportujemy wyeksportowany plik programu Excel Excel!

Dodaj tę 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

Zrobiłem też aktualizację formatu daty do rrrr-mm-dd, ponieważ w ten sposób, nawet jeśli Excel podaje filtr sortowania AZ zamiast najnowszych wartości, nadal działa!

Jestem pewien, że powyższy kod zawiera błędy, ale oczywiście nie mam pojęcia, jakiego rodzaju dane posiadasz, ale przetestowałem je z jedną kolumną dat (jak masz) i działa dobrze dla mnie!

Jak dodać VBA w MS Office?


no cóż ... Zostawię to na kilka dni, aby dać trochę więcej ekspozycji, ale wygląda to na solidną odpowiedź. To jest absolutnie szalone, nie mogę uwierzyć, że to jest potrzebne. Ponieważ tak jest, naprawiłem dane u źródła za pomocą małej funkcji w programie PowerShell, aby odwrócić go do formatu ISO, w którym to momencie nie obchodzi mnie, czy Excel rozpozna je jako datę: /
Patrick


1

Rozgryzłem to!

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

  1. Jeśli użyjesz funkcji Znajdź i zamień i naciśniesz spację, NIE zadziała.
  2. Musisz kliknąć tuż przed numerem miesiąca, nacisnąć Shift i strzałkę w lewo, aby wybrać i skopiować. Czasami musisz użyć myszy, aby wybrać spację.
  3. Następnie wklej to jako miejsce w polu znajdź i zamień, a wszystkie daty staną się datami.
  4. Jeśli jest miejsce i data Wybierz Dane> Idź do Dane> Tekst do kolumn> Rozdzielane> Spacja jako separator, a następnie zakończ.
  5. Wszystkie spacje zostaną usunięte.

Powodem, dla którego znajdź i zamień nie działa ze spacją, jest to, że przerwa jest najprawdopodobniej tabulatorem.
Patrick,

0

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

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

Próbowałem tego, zachowuje się tak samo z nową kolumną. Zobacz odpowiedź Dave'a, czapkę ekranu u góry dostaję bez względu na to, ile razy przenoszę ją między kolumnami.
Patrick

Spróbuj wymusić problem, używając funkcji DATEVALUE (stara kolumna) we wzorze na nową kolumnę, a następnie użyj listy rozwijanej Formatowanie liczb na pasku wstążki Początek, aby wybrać opcję Krótka / długa data.
harrymc

0

Właśnie skopiowałem liczbę 1 (jeden) i pomnożyłem ją do kolumny daty (danych) za pomocą funkcji WKLEJ SPECJALNE. Następnie zmienia się na Ogólne formatowanie, tj. 42102. Następnie należy zastosować datę na formatowaniu i rozpoznaje ją jako datę.

Mam nadzieję że to pomoże


0

Wszystkie powyższe rozwiązania - w tym r0berts - zakończyły się niepowodzeniem, ale uznały to dziwne rozwiązanie, które okazało się najszybszym rozwiązaniem.

Próbowałem posortować „daty” na pobranym arkuszu kalkulacyjnym transakcji na koncie.

Zostało to pobrane za pośrednictwem przeglądarki CHROME. Żadna manipulacja „datami” nie sprawiłaby, że zostałyby one uznane za sortowane od starych do nowych.

Ale potem pobrałem 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 to zrobiło i było bardzo szybką poprawką.


0

Moje rozwiązanie w Wielkiej Brytanii - miałem daty z kropkami w następujący sposób:

03.01.17

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

  1. Podświetl całą kolumnę.
  2. Idź, aby znaleźć i wybrać / wymienić.
  3. Wszystkie kropki zastąpiłem środkową kreską, np. 03.01.17 03-01-17.
  4. Podświetl kolumnę.
  5. Sformatuj komórki, wybierz datę z karty numerycznej.
  6. Użyj typu 14-03-12(niezbędny, aby mój miał środkową kreskę)
  7. Locale English (Wielka Brytania)

Podczas sortowania kolumny wszystkie daty roku są sortowane.


0

Próbowałem różnych sugestii, ale znalazłem najłatwiejsze rozwiązanie dla mnie w następujący sposób ...

Przykład: patrz obrazy 1 i 2 ... (uwaga - niektóre pola zostały celowo ukryte, ponieważ nie przyczyniają się do przykładu). Mam nadzieję, że to pomoże...

  1. Pole C - mieszany format, który doprowadził mnie do szaleństwa. W ten sposób dane pochodziły bezpośrednio z bazy danych i nie miały dodatkowych spacji ani zwariowanych znaków. Podczas wyświetlania go na przykład jako tekstu, 01.01.2016 jest wyświetlany jako wartość typu „42504”, zmieszany z 15.04.2006, który pokazał, że jest.

  2. Pole F - gdzie uzyskałem długość pola C (wzór LEN miałby długość 5 z 10 w zależności od formatu daty). Pole ma format ogólny dla uproszczenia.

  3. Pole G - uzyskanie komponentu miesiąca mieszanej daty - na podstawie wyniku długości w polu F (5 lub 10), albo uzyskuję miesiąc z wartości daty w polu C, albo uzyskuję znaki miesiąca w ciągu.

  4. Pole H - uzyskanie składnika dnia mieszanej daty - w oparciu o wynik długości w polu F (5 lub 10), albo otrzymuję dzień z wartości daty w polu C, albo uzyskam znaki dnia w ciągu.

Mogę to zrobić również dla roku, a następnie utworzyć spójną datę z trzech składników. W przeciwnym razie mogę użyć poszczególnych wartości dnia, miesiąca i roku w mojej analizie.

Zdjęcie 1: podstawowy arkusz kalkulacyjny pokazujący wartości i nazwy pól

Zdjęcie 2: arkusz kalkulacyjny zawierający formuły pasujące do powyższego wyjaśnienia

Mam nadzieję, że to pomoże.


(1) Chociaż miło jest opublikować zrzut ekranu, aby pokazać, że twoja odpowiedź faktycznie działa, wolimy, aby formuły w twoim rozwiązaniu były zamieszczane jako tekst w treści twojej odpowiedzi, aby ludzie mogli je skopiować i wkleić. (2) Wydaje się, że rozumiesz problem - takie daty liczbowe 04/08są niejednoznaczne. 04/08będą interpretowane jako 8 kwietnia w niektórych lokalizacjach (takich jak Stany Zjednoczone) i 4 sierpnia w innych (takich jak Anglia). Ale są one dwuznaczne właśnie dlatego, że numer miesiąca i dzień miesiąca są różne. … (Ciąg dalszy)
Scott

(Ciąg dalszy) ... Więc dlaczego używasz un  niejednoznacznych dat jak 01/01w odpowiedzi? (3) W rzeczywistości nie jestem pewien, czy rozumiesz problem, ani czy twoja odpowiedź faktycznie działa, ponieważ arkusz ma wiersze, w których miesiąc nr to 15; wydaje się to oczywistym błędem. A biorąc pod uwagę, że 04/15jest to jednoznaczne - musi to oznaczać 15 kwietnia - nie ma sensu, aby twoja metoda była analizowana 01/11jako pierwszy dzień 11 miesiąca. … (Ciąg dalszy)
Scott

(Ciąg dalszy)… (4) Należy zauważyć, że w najlepszym wypadku odpowiedź będzie obsługiwać tylko daty wyglądające jak nn / nn / nnnn . To nie w terminach takich jak 5/8, 5/08, 5/28, 05/8, i 11/8. Również - zabawny fakt - Twoja odpowiedź nie powiedzie się w dniach ≤ 17 maja 1927 r. I ≥ 17 października 2173 r. Nieważne, że problem z Y10K; problemy możemy zacząć już za 154 lata! (5) Mówisz: „Mogę to zrobić również dla roku, a następnie utworzyć datę z trzech spójnych elementów”. Dlaczego więc nie, i pokazać to? … (Ciąg dalszy)
Scott

(Ciąg dalszy)… Szczególnie trudno jest sprawdzić, czy twoja metoda działa, gdy zaprezentujesz jej niekompletną wersję; taki, który nie pokazuje, że miesiąc, dzień i rok są ponownie składane na datę. (6) Jak powiedziałem Patsaeed , byłoby miło, gdybyś użył danych PO z pytania, zamiast tworzyć własne. Nieużywając danych OP, mogłoby być miło, gdybyś użył Patsaeeda, więc przynajmniej moglibyśmy porównać jabłka do jabłek. … (Ciąg dalszy)
Scott

(Ciąg dalszy)… I niezależnie od tego, czy używasz danych PO, czy danych kogoś innego, byłoby miło, gdybyś pokazał oryginalny wkład do swojego scenariusza. (Na przykład, wiersz 7, to jest 01/11/2016albo  11/01/2016?) A jeśli masz zamiar użyć własnych danych, byłoby miło, gdyby trzeba było zawarte pochodzi z tego samego miesiąca, wykazać (na przykład), jak 1/11i 1/21są obsługiwane różnie. A dlaczego zduplikowane dane? Zmarnowałeś 19 wierszy, używając tych samych czterech wartości wiele razy, rozłożonych na 23 wiersze.
Scott

0

UDOSTĘPNIANIE MAŁEGO DŁUGIEGO ALE DUŻO ŁATWIEJSZE ROZWIĄZANIE DLA PODMIOTU ... Nie trzeba uruchamiać makr ani maniaków ... proste MS Excel formuły i edycja.

mieszane daty excel migawka:

wprowadź opis zdjęcia tutaj

  • Data jest w formacie mieszanym.
  • Dlatego, aby stworzyć symetryczny format daty, utworzono dodatkowe kolumny, konwertując tę ​​kolumnę daty w „formacie mieszanym” na TEKST.
  • Na podstawie tego tekstu zidentyfikowaliśmy pozycje „/”, a środkowy tekst został wyodrębniony określając datę, miesiąc, rok za pomocą formuły MID.
  • Te, które były pierwotnie datami, formuła zakończyła się na BŁĄD / #VALUEwynik. - Wreszcie z utworzonego przez nas ciągu - przekonwertowaliśmy je na daty według formuły DATE.
  • #VALUE zostały wybrane bez zmian przez dodanie IFERROR do formuły DATE i kolumna została sformatowana zgodnie z wymaganiami (tutaj, dd / mmm / rr)

* PATRZ SNAPSHOT KARTY EXCEL POWYŻEJ (= migawka z mieszanymi datami excel) *


(1) Chociaż miło jest opublikować zrzut ekranu, aby pokazać, że twoja odpowiedź faktycznie działa, wolimy, aby formuły w twoim rozwiązaniu były zamieszczane jako tekst w treści twojej odpowiedzi, aby ludzie mogli je skopiować i wkleić. (2) Twoje wzory jak pokazane na zdjęciu są błędne, ponieważ odnoszą się one do kolumny  V, Wi  Xkiedy należy je odwołując się do kolumny  B, Ci  D. … (Ciąg dalszy)
Scott

(Ciąg dalszy)… (3) Byłoby miło, gdybyś użył danych PO z pytania, zamiast tworzyć własne. I niezależnie od tego, czy korzystasz z danych PO, czy własnych, byłoby miło, gdybyś pokazał oryginalne dane wejściowe do swojego scenariusza (np. Dla Wiersza 9, to jest 5/8/2014lub  5/08/2014). A jeśli zamierzasz korzystać z własnych danych, byłoby miło, gdybyś podał daty z tego samego miesiąca, aby zademonstrować (na przykład), w jaki sposób 5/8i jak postępować 5/28inaczej. A dlaczego zduplikowane dane? Zmarnowałeś sześć wierszy, używając tych samych wartości więcej niż raz.
Scott

0

Dzieje się tak przez cały czas podczas wymiany danych daty między lokalizacjami w Excelu. Jeśli masz kontrolę nad programem VBA, który eksportuje dane, sugeruję wyeksportowanie liczby reprezentującej datę zamiast samej daty. Liczba jednoznacznie koreluje z jedną datą, niezależnie od formatowania i ustawień regionalnych. Na przykład zamiast pliku CSV z 24/09/2010 wyświetli 40445.

W pętli eksportu, gdy trzymasz każdą komórkę, jeśli jest to data, przekonwertuj na liczbę za pomocą CLng (myDateValue). To jest przykład mojej pętli przebiegającej przez wszystkie wiersze tabeli i eksportującej do pliku CSV (uwaga: zastępuję również przecinki w ciągach znacznikiem, który usuwam podczas importowania, ale możesz nie potrzebować tego):

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

0

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


0

Ten problem doprowadzał mnie do szału, a potem natknąłem się na łatwą naprawę. Przynajmniej działało dla moich danych. Łatwo to zrobić i zapamiętać. Ale nie wiem, dlaczego to działa, ale zmiana typów jak wskazano powyżej nie. 1. Wybierz kolumny z datami 2. Wyszukaj rok w datach, na przykład 2015. Wybierz Zamień wszystko na ten sam rok, 2015. 3. Powtórz dla każdego roku. Z roku na rok zmienia to typy na rzeczywiste daty.
Jest to nadal kłopotliwe, jeśli masz wiele lat danych. Szybciej jest szukać i zamieniać 201 na 201 (zastąpić 2010 do 2019); zamień 200 na 200 (w celu zastąpienia 2000 do 2009); i tak dalej.


0

Wybierz daty i uruchom nad nim ten kod ...

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

-1

Używam Maca.

Przejdź do preferencji programu Excel> Edytuj> Opcje daty> Automatycznie przekonwertuj system dat

Również,

Przejdź do Tabele i filtry> Grupuj daty podczas filtrowania.

Problem prawdopodobnie zaczął się, gdy otrzymałeś plik z innym systemem dat, co zepsuło twoją funkcję Excela


-2

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

To zapewni yyyymmddwynik, który można posortować. Użycie len(a1)just pozwala na dodanie dodatkowego zera dla miesięcy 1-9.


2
To nie pomoże, ponieważ daty nie są rozpoznawane jako daty. Ponadto użycie dodatkowych kolumn do celów sortowania jest rzadko konieczne
CallumDA
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.