Дата в эксель не читается как дата

Обновлено: 05.07.2024

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

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

Рассматриваемая колонка выглядит следующим образом

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

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

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

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

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

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

Проблема: 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 распознавать эти текстовые строки как даты. Очевидно, что если вы делаете это много, это боль в шее, и вы можете составить визуальную основную процедуру, которая сделает это за вас одним нажатием кнопки.

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

= Мир MS Excel/Статьи об Excel

Приёмы работы с книгами, листами, диапазонами, ячейками [6]
Приёмы работы с формулами [13]
Настройки Excel [3]
Инструменты Excel [4]
Интеграция Excel с другими приложениями [4]
Форматирование [1]
Выпадающие списки [2]
Примечания [1]
Сводные таблицы [1]
Гиперссылки [1]
Excel и интернет [1]
Excel для Windows и Excel для Mac OS [2]
Число сохранено как текст или Почему не считается сумма?

Достаточно часто в Excel выгружаются данные из сторонних программ для их дальнейшей обработки, и нередко дальнейшее использование этих данных в формулах даёт непредсказуемый результат: числа не суммируются, между датами невозможно рассчитать количество дней и т.д.

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

Причина первая . Число сохранено как текст

В этом случае видно, что числа или даты прижаты к левому краю ячейки (как текст) и, как правило, в левом верхнем углу ячейки имеется маркер ошибки (зелёный треугольник) и тег, который при наведении мышки поясняет, что число сохранено как текст.


Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

  • С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки ( зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число
  • С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами -- нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) -- в поле Найти вводим , (запятую) -- в поле Заменить на тоже вводим , (запятую) -- Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 -- Enter

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

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

  • С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку -- выполняем команду Копировать -- выделяем диапазон с проблемными числами -- Специальная вставка -- Сложить -- ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат

Вариантом этого приёма может быть умножение диапазона на 1

  • С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные -- Текст по столбцам -- Готово
  • С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных
  • С помощью макросов. Собственно, любой из перечисленных способов можно выполнить макросом. Если Вам приходится часто выполнять подобное преобразование, то имеет смысл написать макрос и запускать его по мере необходимости.

Приведу два примера макросов:

1) умножение на 1

2) текст по столбцам

Причина вторая . В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =--ПОДСТАВИТЬ(B4;" ";"")

Для неразрывных пробелов: =--ПОДСТАВИТЬ(B4;СИМВОЛ(160);"")

Сразу для тех и других пробелов: =--ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);"");" ";"")

Иногда, чтобы добиться желаемого результата, приходится комбинировать перечисленные способы. Например, сначала удалять пробелы, а затем преобразовывать формат ячеек

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

Причины и пути решения

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

  1. Конфликт форматов и блокировка стиля ячейки.
  2. Несоответствие заданных стилей Excel.
  3. Неправильное выполнение работы.
  4. Временные сбои в работе программы.
  5. Неправильные региональные настройки.

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

  • Выберите столбец даты в Excel.
  • Зайдите в раздел «Данные» и выберите «Text to Columns».


  • На первом экране поставьте переключатель «delimited».
  • Кликните на кнопку «Далее».
  • Снимите флажки-разделители и кликните на кнопку «Далее».
  • В секции «Формат данных столбца» выберите «Дату».


  1. Выделите ячейки, где не меняется дата в Экселе.
  2. Жмите на сочетание Ctrl+1.
  3. В разделе «Формат ячеек» откройте вкладку «Число».
  4. В перечне «Категория» выберите требуемый раздел.
  5. Перейдите в «Тип» и укажите подходящий вариант форматирования в Excel. Для предварительного просмотра перейдите в раздел «Образец». Если сделанный вариант не устраивает, можно внести изменения и снова выполнить проверку - меняется отображение или нет.


  1. Используйте формулу: =DATE(MID(A1,FIND(",",A1)+1,5),MATCH(LEFT(A1,3),,0),MID(SUBSTITUTE(A1,","," "),5,5)) для перевода в REAL.
  2. Проверьте, что отображение в Excel меняется с учетом предпочтений.


Такой вариант может сработать в случае неправильных региональных настроек и невозможности изменения форматирования для дня, строки и времени. Представленную форму Excel можно сделать проще, если представить больше сведений о формате ввода. Но даже в таком случае все должно работать корректно. Кроме того, при желании сохранить часть времени можно воспользоваться формулой +RIGHT(A1,11).

  1. Загрузите лист Excel, где не меняется стиль отображения, на Гугл Док.
  2. Откройте файл с помощью электронной Гугл-таблицы.
  3. Выберите весь столбец с информацией.
  4. Установите тип формата для даты. На данном этапе можно выбрать любой подходящий вариант.
  5. Закройте электронную таблицу Гугл в формате .xlsx.


  1. Копируйте несколько ячеек, где не меняется форматирование даты в Excel, в блокнот.
  2. Жмите на Clear All.
  3. Вставьте содержимое ячейки в блокнот обратно в те же ячейки.
  4. Установите их в качестве текущего дня в нужном варианте и т. д.

Как поменять формат в Excel

Распространенная причина, почему софт не меняет дату в Экселе — неправильные действия пользователя. Для решения проблемы необходимо правильно сделать эту работу. Здесь существует несколько путей.

  1. Зайдите во вкладку «Главная».
  2. Перейдите в раздел «Число».
  3. Жмите на кнопку вызова диалогового окна (находится над строчкой «Число»).
  4. В списке «Категория» выберите нужный пункт.
  5. Перейдите в раздел «Тип» и установите нужный вариант отображения в Excel.


Тип форматирования, который начинается со звездочки, влияют за изменение параметров дня / времени, установленных на панели управления. В ином случае этого влияния нет. После этого необходимо выбрать нужный язык, который меняется с помощью специального переключателя. Выбор осуществляется из доступного перечня (русский язык предусмотрен).

  1. Зайдите во вкладку XLTools.
  2. В группе «Дата и время» откройте выпадающий перечень.
  3. Войдите в раздел «Изменить формат даты и времени».
  4. Задайте область поиска. Здесь указывается выбранный диапазон, рабочий лист, книга, открытые книги.
  5. Выберите подходящий вариант из перечня, принятый в стране и на нужном языке. По желанию задайте пользовательский формат.
  6. Кликните на кнопку «Готово».


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

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

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

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Некорректное отображение значений формул
Здравствуйте! Проблема в следующем: при отображении значений формул, отображаются числа.

Некорректное отображение больших цифр
когда пишу большие числа в Excele появляется последний нули. знаете почему?

Некорректное отображение таблиц в файле CSV
В файле во вложении находятся три столбика таблиц, когда делал его все было как на скриншоте так.

Сводные таблици, некорректное отображение данных в фильтре
Добрый день. В файле эксель есть четыре страницы. На одной странице сводная таблица. На.

Period79, в файле все даты отображаются правильно.
Как вариант, Текст по столбцам, на третьем шаге отметить Формат столбца "Дата" - готово в файле все даты отображаются правильно.
Как вариант, Текст по столбцам, на третьем шаге отметить Формат столбца "Дата" - готово

Не понял. В файле даты не отображаются совсем. Формат даты ничего не меняет, с этими числами оперировать не получается.

Текст по столбцам, на третьем шаге отметить Формат столбца "Дата" - готово. - это совсем не понял, какой текст? по каким столбцам?

Решение

Выделить диапазон с датами.
Поиск и замена. Найти точку, Заменить на точку оно притворяется датой. Вы попробуйте поменять в свойствах на другую форму даты и результата не будет ааа. поняла о чем Вы)))
Или Текст по столбцам, или предложение AlexM

Отображение даты через UserForm
Добрый вечер. Нужна помощь с отображением даты. В частности меняются местами число и месяц .

Некорректное отображение документов формата doc в MS Word 2010
Форумчане! При открытии некоторых документов старого формата *.doc (не *.docx) в MS Office 2010.


Некорректное отображение даты
Снова у новичка в оракле вопрос. Делаю таблицу, в которой лежат операции с другой табличкой с.


Некорректное отображение даты из БД
Привет. такой вопрос, при внесении даты с админки дата в базу данных заносится корректно, но на.

Читайте также: