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


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

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

Соответствующая колонка выглядит так:

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 будет работать, но мне тоже этого не должно быть, я, очевидно, что-то пропустил. Что это?

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


34
2017-09-26 10:46


Источник


Я подозреваю, что проблема заключается в том, что Excel не знает, как это сделать - вы можете протестировать. Когда вы пытаетесь обновить формат дат, выберите столбец и щелкните его правой кнопкой мыши и выберите ячейки формата (как вы уже это сделали), но выберите вариант 2001-03-14 (около нижней части списка). Я буду подозревать, что только некоторые из форматов даты обновляются правильно, что указывает на то, что Excel по-прежнему рассматривает его как строку, а не дату! - Dave
Каков формат экспортируемых данных? Это CSV или XLSX? - Excellll


Ответы:


Проблема: 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) и выберите Unformatted Text. Откроется диалоговое окно «Импорт текста». Набор символов остается Unicode, но для языка выбирайте английский (США); вы также должны установить флажок «Обнаруживать специальные номера». Ваши даты сразу отображаются в стандартном формате США и сортируются по дате. Если вам нужен специальный формат США MM / DD / YYYY, вам нужно указать это один раз через «ячейки формата» - до или после вставки.

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


60
2017-09-26 12:49



Я адресую оба своих пункта в своем OP. Разделение значений даты на столбцы (а затем повторная интеграция в формате ISO), конечно, возможно, но учитывая, что Excel имеет обширные встроенные функции даты, я хотел бы иметь возможность использовать их с действительными данными даты, которые у меня есть. Второй вариант - это то, что я описываю, что не работает для меня. - Patrick
Дорогой Патрик, пожалуйста, внимательно прочитайте. Или вы можете использовать Libre Office Calc - с вашими данными это работает сразу - вы просто предварительно форматируете столбец Date - English (USA), а при вставке делаете «Paste Special» «Unformatted text» - просто скажите диалоговому окну, что язык Русский (США) и не забудьте установить флажок, чтобы распознавать специальные номера. В своем ответе я попытался намекнуть так сильно, как мог, что Excel не согласуется с этим (багги), и вам нужно прибегнуть к этим шагам 1, а затем 2 для достижения желаемого результата. - r0berts
Я действительно должен был правильно прочитать ваш вопрос. Ваше первое предложение действительно работает. У вас должна была быть щедрость. Спасибо за вашу помощь. - Patrick
Предложение 1 работает как шарм. Благодарю. - Adam
Действительно хороший ответ !!! - Adders


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


4
2017-08-28 17:14



Можете ли вы объяснить, что это делает, чтобы исправить эту проблему? - fixer1234
Это действительно сработало и было легко. Замена просто удаляет ведущие нули - PedroGabriel


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


3
2018-02-22 09:06





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

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

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

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

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


2
2017-09-26 11:41



К сожалению, этот ярлык не работает для меня. - Patrick
Что делает ярлык в вашей системе Patrick. - Firee
Ярлык, похоже, ничего не делает. У меня есть несколько систем, которыми я располагаю в Office 2007, 2010 и 2013 годах, и никто из них не реагирует на сокращение CTRL SHIFT 3. CTRL 1 правильно отображает диалог «ячейка формата». - Patrick
Комбинация клавиш Ctrl + Shift + 3, на которую ссылаются несколько небрежно, как «Ctrl + Shift + #», Вот, означает «Применяет формат даты с днем, месяцем и годом» в Excel2007, 2010, 2013 и2016. (В частности, на моем Excel2013 это «d-mmm-yy».) Документ Microsoft не объясняет, что ячейки уже должны содержать ... (Продолжение) - Scott
(Cont'd) ... значения, которые Excel распознает как данные даты и времени, и кажется, что никто здесь не имеет явно объяснил, что, поскольку Патрик находится в Англии, его версия Excel распознает строки, подобные 04/08/2012, 04/09/2009, а также 04/01/2010 как 4 августа 2012 года, 4 сентября 2009 года и 4 января 2010 года, соответственно, и лечит 04/21/2011 как текстовая строка (потому что нет 21-го месяца). Поскольку некоторые значения являются текстом (и некоторые из значений, предположительно, на самом деле были неверно истолкованы), форматирование (например, сочетание клавиш Ctrl + Shift + 3) не принесет никакой пользы. - Scott


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

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

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

enter image description here

Для этого есть обходные пути, но ни один из них не будет автоматизирован просто потому, что вы каждый раз экспортируете. Я бы предложил использовать 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 дает вам фильтр сортировки A-Z вместо самых новых значений, он все равно работает!

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

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


2
2018-03-17 09:14



хорошо ... Я собираюсь оставить это на пару дней, чтобы дать ему немного дополнительной экспозиции, но это похоже на солидный ответ. Это абсолютно безумие, но я не могу этого сделать. Поскольку я исправил данные в источнике с небольшой функцией в powershell, чтобы перевернуть ее в формате ISO, в этот момент мне все равно, если Excel распознает ее как дату: / - Patrick


Я столкнулся с аналогичной проблемой с тысячами строк, извлеченных из базы данных 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")))

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


2
2018-04-10 21:49





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

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

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


2
2018-06-25 09:14





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

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


1
2018-06-23 23:35