Невозможно заставить Excel распознать дату в колонке

Проблема: Excel не хочет распознавать даты как даты, даже если через “Формат ячеек - Номер - Настройка” вы явно пытаетесь сказать ему, что это даты “mm/dd/yyyy”. Как вы знаете, когда Excel распознал что-то как дату, он далее сохраняет это как число - например, “41004”, но отображает как дату в соответствии с указанным вами форматом. Чтобы добавить путаницу, Excel может конвертировать только часть ваших дат, таких как 08/04/2009, но оставить другие, например, 07/28/2009 неконвертированным.

Решение: шаги 1 и затем 2

1) Выберите столбец даты. В разделе “Данные” выберите кнопку “Текст в столбцы”. На первом экране оставьте радиокнопку “delimited” и нажмите кнопку Next. Разблокируйте все поля разделителя (любые поля пустые; никаких галочек) и нажмите Next. Под форматом данных колонки выберите Date и выберите MDY в соседнем комбо-боксе и нажмите Finish. Теперь у вас есть значения даты (т.е. Excel распознал ваши значения как Date типа данных), но форматирование, скорее всего, все равно будет локальной датой, а не mm/dd/yyyy, которую вы хотите.

2) Чтобы нужный формат даты США отображался правильно, сначала нужно выбрать столбец (если он не выбран), а затем в разделе Cell Format - Number выберите Date И выберите Locale : Английский (США). Это даст вам формат “m/d/yy”. Затем вы можете выбрать Custom и там вы можете либо набрать “mm/dd/yyyy”, либо выбрать это из списка пользовательских строк.

Alternative : используйте LibreOffice Calc. После вставки данных из сообщения Патрика выберите “Вставить специальный” (Ctrl+Shift+V) и “Неформатированный текст”. Откроется диалоговое окно “Импорт текста”. Набор символов останется Unicode, но для языка выберите English(USA); вы также должны поставить галочку в поле “Обнаружение специальных номеров”. Ваши даты сразу же отображаются в формате США по умолчанию и могут быть перенесены. Если вы хотите, чтобы специальный американский формат MM/DD/YYYY был указан один раз через “format Cells” - до или после вставки.

Можно сказать - Excel должен был распознавать даты, как только я сказал это через “Cell Format” и I couldn’t agree more. К сожалению, только через шаг 1 сверху я смог заставить Excel распознать эти текстовые строки как даты. Очевидно, что если вы сделаете это много, то это заноза в шее, и вы можете собрать вместе визуальную основную рутину, которая сделает это за вас одним нажатием кнопки.

Данные | Текст в столбцы

Обновить _ ведущий апостроф_ после вставки: Вы можете увидеть в строке формулы, что в ячейке, где дата не распознана, есть ведущий апостроф. Это означает, что в ячейке, отформатированной как число (или дата), есть текстовая строка. Можно сказать - ведущий апостроф не позволяет электронной таблице распознать число. Для этого нужно знать, что нужно искать в строке формулы - ведь в электронной таблице просто отображается то, что выглядит как выровненное влево число. Чтобы решить эту проблему, выберите столбец, который Вы хотите исправить, выберите в меню Data | Text to Columns и нажмите OK. Иногда вы сможете указать тип данных, но если вы предварительно задали формат столбца - он вам не понадобится. Команда действительно предназначена для разделения текстовой колонки на две и более с использованием разделителя, но она работает как шарм и для этой проблемы. Я тестировал ее в Libreoffice, но в Excel тоже есть такой же пункт меню.

Выберите все колонки и перейдите к Найти и Заменить и просто замените "/" на /.

Это может не иметь отношения к исходному автору, но может помочь кому-то другому, кто не может отсортировать столбец дат.

Я обнаружил, что Excel не распознает столбец дат, которые все были до 1900 года, настаивая на том, что они являются столбцом текста (поскольку 1/1/1900 имеет числовой эквивалент 1, а отрицательные числа, по-видимому, не допускаются). Поэтому я сделал общую замену всех моих дат (которые были в 1800-х годах), чтобы поместить их в 1900-х годах, например, 180 -> 190, 181 -> 191, и т.д. Процесс сортировки затем работал хорошо. В конце концов, я сделал замену другим способом, например, 190 -> 180.

Надеюсь, это поможет какому-нибудь другому историку.

Я имел дело с похожей проблемой с тысячами строк, извлеченных из базы данных SAP, и, необъяснимо, с двумя разными форматами даты в одной колонке (большинство из них - это наш стандарт “YYYY-MM-DD”, но около 10% - это “MM-DD-YYY”). Ручное редактирование 650 строк один раз в месяц не было опцией.

Ни одна (ноль… 0… ноль) из вышеперечисленных опций не работала. Да, я перепробовал их все. Копирование в текстовый файл или явный экспорт в txt до сих пор, как-то, не повлияло на формат (или отсутствие информации) 10% дат, которые просто сидели в своем углу и отказывались вести себя.

Единственный способ, которым я смог это исправить, это вставить пустой столбец справа от столбца неправильного ведения себя и использовать следующую, довольно простую формулу:

(предполагая, что ваши неправильные данные находятся в 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")))

Я мог затем скопировать результаты в мой новый, рассчитанный столбец поверх дат неправильного ведения себя, вставив только “Значения”, после чего я мог удалить свой рассчитанный столбец.

У меня был точно такой же выпуск с датами в Excel. Формат соответствовал требованиям к дате в Excel, но без редактирования каждой ячейки она бы не узнала дату, а когда вы имеете дело с тысячами строк, вручную редактировать каждую ячейку нецелесообразно. Решение заключается в том, чтобы запустить поиск и замену в строке дат, где я заменил дефис на дефис. Excel пробегает через столбец, заменяя, как с “like”, но в результате получается, что теперь он пересчитывает даты! ИСПРАВЛЕННО!

Похоже, что Excel не распознает ваши даты как даты, он распознает их текст, поэтому вы получаете опции сортировки как от А до Я. Если вы делаете это правильно, вы должны получить что-то вроде этого:

Следовательно, важно убедиться, что Excel распознает дату. Самый простой способ сделать это - использовать ярлык CTRL+SHIFT+3.

Вот что я сделал с вашими данными. Я просто скопировал их из вашего сообщения выше и вставил в Excel. Затем я применил ярлык, указанный выше, и получил нужную опцию сортировки. Проверьте изображение.

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

Простое решение здесь - создайте новый столбец use =datevalue(cell) formula, затем скопируйте формулу в другие строки - несколько секунд, чтобы исправить это.

Есть пробел в начале и в конце даты.

1. Если вы используете найти и заменить и нажмите пробел, это не будет работать.

2. Вы должны щелкнуть правой кнопкой мыши перед номером месяца, нажать shift и левую стрелку, чтобы выбрать и скопировать. Иногда нужно использовать мышь для выбора пробела.

3. Затем вставьте это как пробел в поиске и заменить, и все ваши даты станут датами.

4. Если есть пробел и дата Выберите Data>Перейти на Data>Ссылка на columns>Delimited>Пробел как разделитель, а затем закончить.

5. Все пробелы будут удалены.

Мне не повезло со всеми вышеперечисленными предложениями - я придумал очень простое решение, которое работает как очарование. Вставьте данные в Google Sheets, выделите колонку даты, нажмите на формат, затем номер и еще раз номер, чтобы изменить его на формат номера. Затем я копирую и вставляю данные в электронную таблицу Excel, нажимаю на даты, затем форматирую ячейки на дату. Очень быстро. Надеюсь, это поможет.

Я просто скопировал число 1 (одно) и умножил его на столбец даты (данные), используя функцию PASTE SPECIAL. Затем он меняет общее форматирование i.e 42102 Затем идите применить Дата на форматирование, и теперь он распознает его как дату.

Надеюсь, что это поможет

Мое решение в Великобритании - у меня были даты с точками в них так:

03.01.17

Я решил это следующим образом:

1. Выделите всю колонку.
2. Найти и выбрать/заменить.
3. Я заменил все стопы на средние, например 03.01.17 03-01-17.
4. 5. Формат ячеек, номер закладки, выберите дату.
5. Используйте тип 14-03-12 (необходимо, чтобы у меня был средний тире)
6. Местный английский (Великобритания)

При сортировке столбца сортируются все даты года.

ИЗОБРАЖЕНИЕ МНОГО ДЛЯ НО МНОГО ЛЕГКИХ РЕШЕНИЙ К ОБЪЕКТУ…………………………..Не нужно запускать макросы или всякую хрень…простые формулы и редактирование ms excel.

Смешанные даты excel снимки:

• Дата в смешанном формате.
  
• Таким образом, чтобы сделать симметричный формат даты, были созданы дополнительные колонки, конвертируя столбец даты “смешанного формата” в TEXT.
• Из этого текста мы определили позиции “/” и средний текст был извлечен определяющий дату, месяц, год с помощью MID формулы.
• Те, которые были первоначально даты, формула заканчивается ERROR / #VALUE результат. - Наконец, из созданной нами строки - мы преобразовали их в даты по формуле DATE.
• #VALUE были выбраны так же, как и IFERROR, добавленный в формулу DATE, и столбец был отформатирован по мере необходимости (здесь, dd/mmm/yy)

* ОТКАЗ ОШИБОК ДАННЫХ (= смешанные даты со снимком excel) *.

Я пробовал различные предложения, но нашел для себя самое простое решение…

См. пример на рисунках 1 и 2… (примечание - некоторые поля были спрятаны намеренно, так как они не вносят свой вклад в пример). Надеюсь, это поможет…

1. Поле C - смешанный формат, который сводил меня с ума. Так данные поступали непосредственно из базы данных и не содержали лишних пробелов или сумасшедших символов. Например, при отображении в виде текста, 01/01/2016 отображалось значение типа ‘42504’, перемешанное с 04/15/2006, которое отображалось как есть.

2. Поле F - где я получил длину поля C (формула LEN будет длиной 5 из 10 в зависимости от формата даты). Для простоты это поле имеет общий формат.

3. Поле G - получение месячной составляющей смешанной даты - исходя из длины результата в поле F (5 или 10), я либо получаю месяц из значения даты в поле С, либо получаю символы месяца в строке.

4. Поле H - получение дневной составляющей смешанной даты - на основе результата длины в поле F (5 или 10) я получаю либо день от значения даты в поле C, либо получаю символы дня в строке.

я могу сделать это и для года, затем создать дату из трех составляющих, которая является последовательной. В противном случае я могу использовать в своем анализе отдельные значения дня, месяца и года. Изображение 1: основная электронная таблица со значениями и именами полей Изображение 2: электронная таблица со значениями формул, совпадающими с объяснением выше

Надеюсь, это поможет.

Эта проблема сводила меня с ума, а потом я наткнулся на легкое решение. По крайней мере, это сработало с моими данными. Это легко сделать и запомнить. Но я не знаю, почему это работает, но изменение типов, как указано выше, нет. 1. Выберите колонку(ы) с датами 2. Ищите год в ваших датах, например 2015. Выберите Заменить все на один и тот же год, 2015. 3. Повторите для каждого года. Это изменит типы на фактические даты, год за годом. Это все равно будет громоздко, если у вас много лет в данных. Ускорьте поиск и замену 201 на 201 (для замены 2010 на 2019); замену 200 на 200 (для замены 2000 на 2009); и так далее.

Выберите даты и прогоните этот код…

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

Все вышеперечисленные решения, включая r0berts, были неудачными, но нашли это причудливое решение, которое оказалось самым быстрым исправлением.

Я пытался отсортировать “даты” на загруженной электронной таблице транзакций по счетам.

Это было загружено через CHROME браузер. Никакие манипуляции с “датами” не позволили бы распознать их как старые-новые сортируемые.

Но, затем я скачал через браузер INTERNET EXPLORER - удивительно - я мог сортировать мгновенно, не касаясь колонки.

Я не могу объяснить, почему разные браузеры влияют на форматирование данных, за исключением того, что это было сделано явно и было очень быстрым исправлением.

Если Excel упорно отказывается распознавать ваш столбец как дату, замените его на другой:

• Добавить новый столбец справа от старого
• Щелкните правой кнопкой мыши новый столбец и выберите Format
• Установить формат на date
• Выделите весь старый столбец и скопируйте его
• Выделите верхнюю ячейку нового столбца и выберите Paste Special, и только вставьте values
• Теперь вы можете удалить старый столбец.

Это происходит постоянно при обмене данными о датах между локалями в Excel. Если у вас есть контроль над программой VBA, которая экспортирует данные, я предлагаю экспортировать номер, представляющий собой дату, а не саму дату. Номер уникально коррелирует с одной датой, независимо от форматирования и локали. Например, вместо файла CSV, показывающего 24/09/2010, будет показано 40445.

В цикле экспорта, когда вы держите каждую ячейку, если это дата, конвертируйте в число, используя CLng(myDateValue). Это пример моего цикла, проходящего через все строки таблицы и экспортирующего в CSV (обратите внимание, что я также заменяю запятые в строках на тег, который я снимаю при импорте, но вам это может не понадобиться):

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

Я использую mac.

Перейти в excel preferences> Edit> Дата options> Автоматически конвертировать систему дат

Также,

Перейти в таблицы & Filters> Группировать даты при фильтрации.

Вероятно, проблема началась, когда вы получили файл с другой системой дат, и это испортило вашу функцию даты excel

Обычно я просто создаю дополнительную колонку для сортировки или суммирования, поэтому используйте year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

, которая обеспечит результат yyyymmdd, который можно отсортировать. Использование len(a1) позволяет добавлять дополнительный ноль в течение месяцев с 1 по 9.