Невозможно получить Excel, чтобы распознать дату в столбце

Question

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

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

Answer 1

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

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

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

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

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

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

Answer 2

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

Answer 3

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

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

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

Answer 4

У меня была точно такая же проблема с датами в Excel. Формат соответствует требованиям для даты в Excel, но без редактирования каждой ячейки он не распознает дату, а когда вы работаете с тысячами строк, редактировать каждую ячейку вручную нецелесообразно. Решение состоит в том, чтобы найти и заменить строку дат, где я заменил дефис дефисом. Excel проходит через столбец, заменяя «like» на «like», но в результате получается, что теперь он пересчитывает даты! ФИКСИРОВАННЫЙ!

Answer 5

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

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

Answer 6

Я имел дело с аналогичной проблемой, связанной с тысячами строк, извлеченных из базы данных SAP, и, необъяснимым образом, двумя разными форматами даты в одном и том же столбце даты (большинство из них - наш стандартный «ГГГГ-ММ-ДД», но около 10% - «ММ- 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")))

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

Answer 7

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

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

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

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

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

Итак, создайте новый лист Excel с именем ImportedData.xlsm (Excel включен). Это файл, в который мы будем импортировать экспортированный файл Exchange 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?

Answer 8

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

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

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

Answer 9

Я понял!

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

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

Answer 10

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

Answer 11

Я обнаружил, что CAST AS smalldatetime решает мою проблему. Решение найдено здесь: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f0f8dd05-4212-428f-8099-748e87c637ae/format-data-from-sql-server-view-as-date- в-первенствует? форум = sqlkjpowerpivotforexcel

Это, конечно, работает, только если у вас есть доступ к базовому запросу.

Answer 12

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

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

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

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

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

Answer 13

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

При ошибке возобновить следующее для каждого xcell In Selection
XCell.Значение = CDate(xcell.Значение)
Следующая xcell
End Sub

Answer 14

Я смог решить проблему после того, как понял, что была выбрана "Показать формулы". в Excel 2016 перейдите в "Формулы", затем в раздел "Аудит формул" и убедитесь, что "Показать формулы" не выбрано. Исправлена моя проблема.

Answer 15

ПОДЕЛИТЬСЯ НЕМНОГО ДОЛГО, НО МНОГО ЛЕГКОГО РЕШЕНИЯ ДЛЯ ПРЕДМЕТА ..... Не нужно запускать макросы или что-то в этом роде .... простые формулы MS Excel и их редактирование.

смешанный снимок Excel Excel:

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

* ОБРАТИТЕСЬ К ОБЗОРУ ЛИСТА EXCEL ВЫШЕ (= смешанный снимок Excel Excel) *

Answer 16

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

Answer 17

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

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

Answer 18

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

Answer 19

Это происходит все время при обмене данными даты между локалями в 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

Answer 20

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

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

Answer 21

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

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

Answer 22

эта проблема заставила меня почти сломать мой ноутбук

Короче говоря, у меня была похожая проблема, когда даты в одних ячейках работают просто отлично, но в других продолжают переворачиваться, независимо от того, копирую ли я вставлять или вводить вручную, я сделал весь текст данных в решения для форматирования столбцов и ячеек и все такое не работает

решение на самом деле не в Excel, его в регионе и языковой обстановке .. я знаю правильно!

чтобы даты отображались как MM/DD/YYYY на вкладке форматов, измените формат на американский

чтобы даты отображались в формате ДД / ММ / ГГГГ на вкладке «Форматы», измените формат на «Великобритания».

Вуаля!

Answer 23

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

Answer 24

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

03.01.17

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

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

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

Answer 25

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

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

1. Поле C - смешанный формат, который сводил меня с ума абсолютно. Так данные поступали напрямую из базы данных и не содержали лишних пробелов или сумасшедших символов. Например, при отображении в виде текста 01.01.2016 отображалось значение типа «42504», смешанное с 15.04.2006, которое показывалось как есть.

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

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

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

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

Рисунок 1: основная электронная таблица со значениями и именами полей

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

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

Answer 26

Я нашел очень простой ответ. Даты были отформатированы 10.11.2018 и не были признаны в качестве дат. сначала отформатировав столбец как даты, я выполнил поиск Ctrl-H и заменил «.» с "/". Все даты были мгновенно признаны.

Answer 27

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

Перейдите в настройки Excel> Изменить> Параметры даты> Автоматически конвертировать систему дат

Также,

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

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

Answer 28

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

Answer 29

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

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