Не удалось заставить Excel распознать дату в столбце

У меня постоянно возникают проблемы с датами в Excel, я должен что-то делать неправильно, но я не понимаю, что.

У меня есть электронная таблица, экспортированная с нашего сервера обмена, которая содержит столбец с датами. Они вышли в американском формате, хотя я в Великобритании.

  • Как сообщить Windows, чтобы настроить время, даже если оно отключено?
  • Windows 7 - отображение даты с помощью небольших значков
  • Как узнать, когда был записан / сожжен звуковой диск (CD)?
  • Соответствующая колонка выглядит так:

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

    В Excel я выделил столбец и выбранные Format Cells... В этом диалоговом окне я выбрал Date , выбранный English (United States) в качестве языкового стандарта и выбрал соответствующий формат даты из списка. Я нажал ОК и попытаюсь отсортировать данные по этому столбцу.

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

    Это, в свою очередь, сортирует данные даты на первые две цифры.

    Я знаю, что я мог бы переформатировать эти данные в ISO, а затем от A до Z будет работать, но я тоже не должен этого делать, я, очевидно, что-то пропустил. Что это?

    EDIT: я испортил щедрость, но это должно было пойти на ответ @ r0berts , его первое предложение «Текст в столбцы» без разделителя и выбор «MDY» по типу данных. Кроме того, если у вас есть время (то есть 04/21/2015 18:34:22 ), вам нужно сначала избавиться от данных времени. Однако после этого метод, предложенный @ r0berts, отлично работает.

  • Формула Excel 2010 для удаления повторяющегося контента на основе значения
  • Условное соответствие на основе даты и времени
  • Как скопировать таблицы Word в Excel без разделения ячеек на несколько строк?
  • Формула Excel, чтобы изменить цвет фона ячейки, но сохранить ее текущее значение?
  • Как предотвратить печать ячеек в Excel
  • Формула Excel для сравнения одного значения в одной ячейке с несколькими значениями в другой ячейке
  • 20 Solutions collect form web for “Не удалось заставить Excel распознать дату в столбце”

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

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

    1) Выберите столбец даты. В разделе «Данные» выберите «Текст в столбцы». На первом экране откройте переключатель « Разграничено » и нажмите « Далее» . Отсоедините любой из полей разделителя (все поля пустые, никаких галочек) и нажмите « Далее» . В формате данных столбцов выберите « Дата» и выберите « MDY» в соседнем поле со списком и нажмите « Готово» . Теперь вы получили значения даты (т.е. Excel распознал ваши значения как тип данных Date ), но форматирование, скорее всего, все еще является датой локали, а не mm/dd/yyyy .

    2) Чтобы правильно отобразить нужный формат даты в США, вам сначала нужно выбрать столбец (если он не выбран), а затем в разделе Формат ячейкиномер выберите Дата и выберите Язык: английский (США) . Это даст вам формат « m/d/yy ». Затем вы можете выбрать «Пользовательский», и там вы можете ввести « mm/dd/yyyy » или выбрать это из списка пользовательских строк.

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

    Можно сказать, что Excel должен был распознать даты, как только я сказал это через «Формат соты», и я не мог согласиться больше . К сожалению, только через шаг 1 выше я смог заставить Excel распознать эти текстовые строки как даты. Очевидно, если вы делаете это много, это боль в шее, и вы можете составить визуальную основную процедуру, которая сделает это для вас одним нажатием кнопки.

    Кажется, что Excel не распознает ваши даты как даты, он распознает их текст, поэтому вы получаете параметры сортировки как от A до Z. Если вы сделаете это правильно, вы должны получить что-то вроде этого:

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

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

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

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

    Я подозреваю, что проблема заключается в том, что Excel не может понять формат … Хотя вы выбираете формат даты, он остается в виде текста.

    Вы можете легко проверить это: когда вы пытаетесь обновить формат дат, выберите столбец и щелкните его правой кнопкой мыши и выберите ячейки формата (как вы уже это сделали), но выберите вариант 2001-03-14 (около нижней части списка). Затем просмотрите формат ваших ячеек.

    Я буду подозревать, что только некоторые из форматов даты обновляются правильно, что указывает на то, что Excel по-прежнему рассматривает его как строку, а не дату (обратите внимание на разные форматы в приведенном ниже снимке экрана)!

    Введите описание изображения здесь

    Для этого есть обходные пути, но ни один из них не будет автоматизирован просто потому, что вы каждый раз экспортируете. Я бы предложил использовать VBa, где вы можете просто запустить макрос, который преобразует формат данных из США в Великобританию, но это будет означать копирование и вставку VBa в ваш лист каждый раз.

    Кроме того, вы создаете Excel, который читает вновь созданные экспортированные листы Excel (из обмена), а затем выполняет VBa для обновления формата даты для вас. Предположим, файл Exported Exchange Excel всегда будет иметь одинаковое имя файла / каталог и предоставить рабочий пример:

    Итак, создайте новый лист Excel под названием ImportedData.xlsm (excel enabled). Это файл, в который мы будем импортировать экспортированный файл Excel Excel!

    Добавьте этот VBa в файл 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 

    То, что я также сделал, это обновить формат даты до yyyy-mm-dd, потому что таким образом, даже если Excel дает вам фильтр сортировки AZ вместо новейших значений, он все равно работает!

    Я уверен, что у вышеуказанного кода есть ошибки, но я, очевидно, не знаю, какой тип данных у вас есть, но я тестировал его с помощью одного столбца дат (как и у вас), и он отлично работает для меня!

    Как добавить VBA в MS Office?

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

    Я имел дело с аналогичной проблемой с тысячами строк, извлеченных из базы данных SAP, и необъяснимо двумя разными форматами дат в одном столбце даты (большинство из которых являются нашим стандартом «ГГГГ-ММ-ДД», но около 10% составляют «MM- DD-YYY "). Ручное редактирование 650 строк один раз в месяц не было вариантом.

    None (ноль … 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 не признает столбец дат, который был все до 1900 года, настаивая на том, что они были столбцом текста (с 1 января 1900 года имеет числовой эквивалент 1, а отрицательные числа, по-видимому, не допускаются). Поэтому я заменил все мои даты (которые были в 1800-х годах), чтобы поместить их в 1900-е годы, например 180 -> 190, 181 -> 191 и т. Д. Затем процесс сортировки работал нормально. Наконец, я заменил другой путь, например, 190 -> 180.

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

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

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

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

    Я понял!

    Существует пространство в начале и в конце даты.

    1. Если вы используете поиск и замену и нажмите пробел, он НЕ будет работать.
    2. Вы должны нажать прямо перед номером месяца, нажать сдвиг и стрелку влево, чтобы выбрать и скопировать. Иногда вам нужно использовать мышь, чтобы выбрать пробел.
    3. Затем вставьте это как место в поиске и замене, и все ваши даты станут датами.
    4. Если есть пробел и дата, выберите «Данные»> «Перейдите в« Данные »>« Текст в столбцы »>« Разграничено »>« Пространство как разделитель », а затем« Закончить ».
    5. Все пробелы будут удалены.

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

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

    Имел подобную проблему, попытался форматировать данные как дату, но безрезультатно. Затем я понял, что в даче было ведущее место, отредактировал поле и удалил пространство – эй, все хорошо. Не большой для больших объемов данных, но, думая об этом, я мог бы, возможно, проверить его и отредактировать данные в Wordpad или аналогичном редакторе.

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

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

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

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

    Это было загружено через браузер CHROME. Никакое количество манипуляций с «датами» не приведет к их распознаванию как от старого к новому.

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

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

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

     03.01.17 

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

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

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

    Поэтому у меня была такая же проблема. Ни одно из вышеперечисленных решений не работало для меня, или я не нашел их слишком сложными для своего уровня мастерства.

    То, что я закончил, это сделать текст-в-столбцы в разделителе дат. Затем я использовал функцию date () для воссоздания даты, используя ячейки дня, месяца и года. Работал.

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

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

    1. Поле С – смешанный формат, который сводил меня с ума. Вот как данные поступают непосредственно из базы данных и не имеют лишних пробелов или сумасшедших персонажей. При отображении его в виде текста, например, 01/01/2016 отображается как значение типа «42504», смешанное с 04/15/2006, которое было показано как есть.

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

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

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

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

    Изображение 1: базовая таблица, показывающая значения и имена полей

    Изображение 2: таблица, показывающая формулы, соответствующие объяснению выше

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

    СОВМЕЩЕНИЕ МАЛЕНЬКИЙ ДОЛГО, НО МНОГО ЕЩЕ НЕОБХОДИМО ПОДТВЕРЖДЕНИЕ ….. Нет необходимости запускать макросы или geeky вещи … простые формулы ms excel и редактирование.

    Смешанные даты excel моментальный снимок:

    Введите описание изображения здесь

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

    * ЗАПРЕЩАЙТЕСЬ СНОВА ЛИЦЕНЗИОННОГО ЛИСТА ВЫШЕ (= смежные даты excel моментального снимка) *

    Выберите даты и запустите этот код.

    При повторном выполнении ошибки для каждого выбора xcell
    Xcell.Value = CDate (xcell.Value)
    Следующий xcell
    End Sub

    У меня все еще была проблема после нескольких ответов. Чем в формате, выбрал 14-Mar-01 и изменил локаль на английский (США) .. И он работал отлично. Надеюсь это поможет.

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

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

    Давайте будем гением компьютера.