Как в excel преобразовать текст в дату

Как в Excel преобразовать текст в дату

Частая и назойливая проблема офисных сотрудников – при выгрузке информации из баз данных, даты сохраняются, как текст. Это затрудняет сортировку и фильтрацию дат в таблицах. Как решить эту проблему? Вот парочка функций, которые вам помогут:

  1. ДАТАЗНАЧ(Строка) – преобразует текст в дату, если сможет её распознать. Если функция не распознает в строке дату – вернёт ошибку (#ЗНАЧ!). Такое возможно, если дата записана в специфическом формате, или в строке вообще не дата. Выгруженные из баз данных текстовые даты обычно соответствуют региональным стандартам записи, Эксель их отлично понимает. Но есть и исключения, поэтому проверяйте результаты работы для каждого случая.
  2. ВРЕМЗНАЧ(Строка) – похожа на предыдущую, но превращает строку во время, а не в дату. Других отличий нет.

Кстати, многие функции, работающие с датами, автоматически переводят их из строчных в числовой, для них использование функций ДАТАЗНАЧ, ВРЕМЗНАЧ необязательно. Я применяю их только для организации фильтров и сортировки, а вы смотрите по надобности.

Ура, на страницах блога мы уже рассмотрели все функции работы с датами, доступные в Microsoft Excel 2010. Кроме материала этого поста, мы научились вставлять в ячейку текущую дату, получать составные части дат и времени, собирать даты и время из частей, работать с интервалами дат, считать рабочие дни. Если вы изучили все эти материалы – поздравляю, работа с датами покорена!

Следующие посты я собираюсь посвятить функциям суммирования и счёта. Возвращайтесь!

Преобразование дат из текстового формата в формат даты

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

Даты, отформатированные как текст, выравниваются по левому краю в ячейке (вместо выравнивания по правому краю). Если включена Проверка ошибок , Текстовая дата с двумя цифрами года также может помечаться индикатором ошибки: .

Поскольку функция проверки ошибок в Excel распознает даты в текстовом формате с двузначным номером года, можно воспользоваться средством автозамены и преобразовать их в даты в формате даты. С помощью функции ДАТАЗНАЧ можно преобразовывать в даты большинство типов текстовых дат.

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

Вы можете использовать индикатор ошибки для преобразования дат из текстового формата в формат даты.

Примечания: Сначала убедитесь в том, что в Excel включена проверка ошибок. Для этого:

Щелкните Файл > Параметры > Формулы.

В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры ExcelExcel 2007формулы.

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

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

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

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

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

Нажмите появившуюся рядом с выделенной ячейкой кнопку ошибки.

В меню выберите команду Преобразовать XX в 20XX или Преобразовать XX в 19XX. Если вы хотите отключить индикатор ошибки, не преобразуя число, нажмите кнопку пропустить ошибку.

Текстовые даты с двумя цифрами года преобразуются в стандартные даты с четырьмя цифрами года.

После преобразования ячеек с текстовыми значениями можно изменить внешний вид дат путем применения формата даты.

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

Читать еще:  Как в excel установить высоту строки

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

Нажмите кнопку числовой формат и выберите нужный формат даты.

Краткий формат даты выглядит следующим образом:

В длинный формат даты содержатся дополнительные сведения, как показано на рисунке:

Чтобы преобразовать текстовую дату в ячейку в серийный номер, используйте функцию ДАТАЗНАЧ. Затем скопируйте формулу, выделите ячейки, содержащие текстовые даты, и используйте команду Специальная Вставка , чтобы применить к ним формат даты.

Выполните указанные ниже действия:

Выберите пустую ячейку и убедитесь в том, что ее числовой формат является общим.

В пустой ячейке сделайте следующее.

Щелкните ячейку, содержащую дату в текстовом формате, которую следует преобразовать.

Нажмите клавишу ВВОД, и функция ДАТАЗНАЧ возвращает порядковый номер даты, представленной текстовым форматом даты.

Что такое серийный номер Excel?

В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. является порядковым числом 1, а 1 января 2008 — порядковый номер 39448, так как он составляет 39 448 дня после 1 января, 1900.To скопировать формулу преобразования в диапазон смежных ячеек, выделите ячейку, содержащую введенную формулу. , а затем перетащите маркер заполнения по диапазону пустых ячеек, который соответствует размеру диапазона ячеек, содержащих текстовые даты.

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

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

Сочетание клавиш: Кроме того, можно нажать клавиши CTRL + C.

Выделите ячейку или диапазон ячеек, которые содержат даты в текстовом формате, и на вкладке Главная в группе Буфер обмена нажмите стрелку под кнопкой Вставить и выберите команду Специальная вставка.

В диалоговом окне Специальная вставка в разделе Вставить выберите параметр Значения и нажмите кнопку ОК.

На вкладке Главная нажмите кнопку вызова всплывающего окна рядом с полем число.

В поле Категория выберите пункт Дата, после чего укажите необходимый формат даты в списке Тип.

Чтобы удалить серийные номера после того, как все даты будут успешно преобразованы, выделите ячейки, содержащие их, а затем нажмите клавишу DELETE.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Преобразование в Дату в Excel. Бесплатные примеры и статьи.

Функция ЗНАЧЕН() в MS EXCEL

Функция ДАТАЗНАЧ() в MS EXCEL

Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), — преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ(«11.09.2009») возвращает число 40067, соответствующее 11 сентября 2009 года. Но, функция ДАТАЗНАЧ() понимает только определенные форматы записи дат. Например, 2009-сент-11 она не поймет, а 11-сент-2009 — поймет.

Автоматическое преобразование формата ячейки в MS EXCEL при вводе ТЕКСТовых данных (Часть 2)

Продолжаем бороться в MS EXCEL 2007 с автоматическим преобразованием формата ячейки при вводе данных в ячейку. При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты, то EXCEL производит соответствующее преобразование и форматирование. Часто текстовые строки действительно имеют формат дат (2-3-8, т.е. 2 марта 2008), но на самом деле ими не являются (например, это м.б. артикул). В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.

Преобразование в MS EXCEL ТЕКСТовых значений в ДАТУ

Бывает, что при экспорте значений в EXCEL, даты записываются в незнакомом для EXCEL формате, например 20081223 (т.е. 2008г, 23 декабря). Для дальнейшей работы с такими датами выполним преобразование в привычный для EXCEL формат даты.

Является ли в MS EXCEL значение ДАТОЙ

Попробуем преобразовать заданное значение в дату. Если это удастся, то будем считать значение датой.

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в MS EXCEL)

При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. В ряде случаев можно быстро изменить текстовый формат на числовой сразу в нескольких ячейках. Произведем это преобразование с помощью Специальной вставки из Буфера обмена.

Функция ДАТАЗНАЧ для преобразования текста в дату в Excel

Функция ДАТАЗНАЧ в Excel предназначена для работы с текстовыми данными в формате ДАТА. Она используется для преобразования текстовых данных в формат Дата и возвращает числовое значение, характеризующее указанную дату.

Читать еще:  Как в excel посчитать количество ячеек с одинаковым текстом

Как преобразовать дату в текст в Excel

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.

Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

Вид таблицы данных:

Для получения даты в формате, поддерживаемом Excel, используем следующую функцию:

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

  1. Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2). Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
  2. Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
  3. Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.

Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

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

Обработка значений даты в текстовом формате в Excel

Пример 2. В таблице Excel указаны даты неверного формата (вместо записи вид «13.06.2019» используется 13_06_2019). Такие данные указаны в двух столбцах. В соседнем необходимо вычислить разницу дней между указанными датами.

Вид таблицы данных:

Для расчетов используем следующую формулу:

Для получения текстовой строки, которая может быть преобразована в данные формата Дата с помощью функции ДАТАЗНАЧ, используем функцию ПОДСТАВИТЬ, которая выполняет замену символов «_» на «.». Результат вычитания двух полученных дат – искомое значение.

Растянем формулу вниз по столбцу чтобы рассчитать все значения:

Особенности синтаксиса функции ДАТАЗНАЧ в Excel

Функция ДАТАЗНАЧ имеет следующую синтаксическую запись:

Единственным аргументом (обязателен для заполнения) является дата_как_текст – текстовое представление даты, которое может быть преобразовано к данным формата Дата. В Excel есть несколько допустимых вариантов записи дат: 13-июн-2019, 13.06.2019. Любой из этих вариантов записи может быть использован в качестве аргумента функции ДАТАЗНАЧ.

  1. Если текстовые строки, характеризующие даты, хранятся в ячейках Excel, большинство функций выполняют преобразования данных к требуемому типу автоматически. Однако, во избежание возможных ошибок, рекомендуется использовать функцию ДАТАЗНАЧ.
  2. Рассматриваемая функция ориентируется на показания часов, встроенных в ПК, на котором используется редактор Excel. Если в качестве текстового представления даты указана неполная дата, например «13.06», данные о годе будут взяты из текущего времени. Например, функция =ДАТАЗНАЧ(“13.06”) вернет значение 43629, которое после установления формата Дата для ячейки будет преобразовано в 13.06.2019.
  3. Если в качестве аргумента функции ДАТАЗНАЧ было передано значение, не преобразуемое к формату Дата (например, =ДАТАЗНАЧ(23), =ДАТАЗНАЧ(ИСТИНА), =ДАТАЗНАЧ(“333”)), будет возвращен код ошибки #ЗНАЧ!
  4. Для склеивания значений, содержащихся в отдельных ячейках, чтобы «собрать» их в одну строку, характеризующую значение даты, следует использовать символ “&”. Например, в ячейках A1, B1, C1 хранятся значения 10, 3 и 2019 соответственно. Чтобы получить данные формата Дата и записать их в отдельную ячейку, можно использовать следующую функцию — =ДАТАЗНАЧ(A1&».»&B1&».»&C1).
Читать еще:  Excel сложить две текстовые ячейки в excel

Как преобразовать текст в формат даты в Excel

Мне нужно отсортировать этот столбец в хронологическом порядке, однако, когда я импортировать данные, некоторые клетки распознаются как аа пользовательского формата (А2-А4), а другие находятся в общем формате (A5-11). А2-А4 признается в качестве даты, однако A5-A11 признается в качестве общего текста. Excel не распознает, что они должны быть все даты. Я пытался отформатировать их в качестве даты, но это не работает. Я использовал текст на столбцы, чтобы отделить время от данных, однако клеток, таких как А2-А4 уходит за время полуночи в клетке (например, 2/10/2012 0:00:00), и я до сих пор не могу получить клетки появляются в качестве даты, когда я использую текст на столбцы. Я освобождал форматирование, я попытался формат художник, я попытался = DATEVALUE. Я не уверен, что еще делать. Кроме того, я заметил, что клетки, где Excel распознает значения как даты, время перечислены в 24hr время,

Может кто-то пожалуйста, помогите?

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

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

Для ячеек, которые хранятся в виде текста, я полагаю , мы знаем , формат даты , которая читается (т.е. DD / MM / YYYY) , и что он будет оставаться постоянным. Для строки , как DD/MM/YYYY HH:MM PM , мы можем сделать следующее:

(1) На следующий день должен храниться в символах до первых косых черт, так что мы можем извлечь его, написав:

DAY_FORMULA: =LEFT(A1, FIND(«/», A1, 1)-1)

(2) месяц должен храниться в символы между первой и второй косой чертой. Он занимает один или два персонажей, так что мы можем написать следующее:

MONTH_FORMULA: =MID(A1, FIND(«/», A1, 1) + 1, x)

Здесь, FIND(«/», A1, 1) + 1 говорит Excel , чтобы вернуть расположение первого / : так как месяц начинается после того, что мы добавляем один. Теперь нам нужно найти x , количество символов для чтения из этой точки. Так как первый / находится , FIND(«/», A1, 1) и во — вторых, в FIND(«/», A1, FIND(«/», A1, 1)+1) , то число символов между двумя наклонными чертами определяется по формуле:

FIND(«/», A1, FIND(«/», A1, 1)+1) — FIND(«/», A1, 1) — 1 ,

Полная MONTH_FORMULA можно записать в виде:

MONTH_FORMULA: =MID(A1, FIND(«/», A1, 1)+1, FIND(«/», A1, FIND(«/», A1, 1)+1)-FIND(«/», A1, 1)-1)

(3) год содержится в последних четырех цифр после второй косой черты, так что мы можем написать год, как:

YEAR_FORMULA: =MID(A1, FIND(«/», A1, FIND(«/», A1, 1)+1)+1, 4)

Вы можете использовать стандартную формулу даты, чтобы заставить их в формат даты:

=DATE(YEAR_FORMULA, MONTH_FORMULA, DAY_FORMULA

(4) Если вы хотите сохранить информацию о часов, а затем , если не ведущие пробелы существуют в полях (не то, что это не может быть обойдена с =TRIM ), вы можете использовать:

TIME_FORMULA: =TIMEVALUE(MID(A1, FIND(» «, A1, 1)+1, LEN(A1)))

Затем вы можете добавить дату к значению времени, чтобы получить полную дату и время:

=DATE(YEAR_FORMULA, MONTH_FORMULA, DAY_FORMULA) + TIME_FORMULA

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

=IF(ISTEXT(A1), ADJUST, A1)

Полное решение, заменив ADJUST указанными выше шаги, может выглядеть следующим образом :

=IF(ISTEXT(A1), DATE(MID(A1, FIND(«/», A1, FIND(«/», A1, 1)+1)+1, 4), MID(A1, FIND(«/», A1, 1)+1, FIND(«/», A1, FIND(«/», A1, 1)+1)-FIND(«/», A1, 1)-1), LEFT(A1, FIND(«/», A1, 1)-1))+TIMEVALUE(MID(A1, FIND(» «, A1, 1)+1, LEN(A1))), A1)

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
×
×
Adblock
detector