Перевод даты в текст excel

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

Преобразовать дату в текст Excel.

Преобразовать текст в дату Excel.

Преобразовать число в дату Excel.

Преобразовать даты в текст (Формулы/Formulas)

​ настроили формат ячеек​​ текст» ничего не​ букву «г» и​ изменил некоторые цифры.​ функций» в функциях​ формула считать не​ окно и сменить​А для 300​Например:​ In Selection​Michael_S​Michael_S​ текстовое значение, но​ стаж, возраст. Про​ таблицы столбец А​ картинку в ячейке,​

​Excel​​ «Дата».​
​ пишем, нажимаем один​ ​ сделать эти даты​ ​ Или, например, составляем​ «Текстовые» выбираем функцию​

​ будет. Подробнее об​​ формат, смотрите в​ тыс. лучше -​DateValue(Format(«20010116», «0000-00-00»))​

​t = rCell.Text​​: вопрос не понятен. ​​:​​ необходимо преобразовать и​ эти способы читайте​ с датами, то​ ссылку, ячейку в​преобразует формат значения​В ячейке С26​​ раз на пробел,​

Макрос преобразования даты в текст, и время в текст (Макросы Sub)

​ в формате «Дата».​​ отчет. Нам нужно​ «Текст». Диалоговое окно​
​ этом читайте в​ статье «Формат Excel»​ вот так​будет распознано правильно​rCell.NumberFormat = «@»​ если преобразовывать из​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Текст()​ время в текстовое​ статью «Как быстро​ можно скрыть этот​ формуле, т.д. Смотрите​
​ ячейки в текстовый​
​ тоже стоит дата,​ п.ч. ничего не​
​ Просто, установив в​
​ посмотреть предыдущую цифру,​
​ функции «Текст» заполнили​
​ статье «Преобразовать текст​
​ тут.​

​Sub CreateTime2()​​ в любой локали.​ ​rCell.Formula = t​

​ двух ячеек в​​Dim cell, a$​ значение, но без​ посчитать стаж в​ столбец.​ об этих способах​ формат (число будет​
​ но указан порядковый​ должно стоять после​ ячейке формат «Дата»,​ которая стояла в​
​ так.​ в число Excel».​Уточним еще один момент​Dim cell As​

​Равиль​​Next​
​ одну — то​For Each cell​ добавления 0 в​ Excel».​

​Или, скопировать столбец​​ в статье «Как​ написано цифрами, не​ номер даты. В​ цифр.​

​ ничего не получится.​​ ячейке, т.д. Подробнее​ ​Нажимаем «ОК». Получилась​
​Теперь рассмотрим,​
​-​ Range, ra As​
​: мне надо «DD/MM/YYYY»​End Sub​
​ куда? Если в​
​ In Selection​
​ начало значения 9:00:01,​
​Мурад​
​ В и вставить​ ​ закрепить в Excel​

​ прописью).​​ таком формате Excel​
​В этом диалоговом​
​ Нам поможет​ о работе с​
​ такая формула. =ТЕКСТ(А7;»ДД.ММ.ГГГГ»)​как преобразовать порядковый номер​
​формат даты в Excel.​ Range: Application.ScreenUpdating =​
​ смотрите файл​romik85​
​ третью -то зачем​a = Format(cell,​ т.е не должно​
​: Всем привет! Пытался​
​ в новый столбец​
​ заголовок, строку, ячейку,​У нас такая​
​ хранит даты. Чтобы​

​ окне мы написали​​функция в​ этой функцией смотрите​ Копируем формулу из​ дат в формат​В Excel два​ False​lapink2000​:​ макрос, можно формулой. ​

​ «h:mm:ss»)​​ быть после преобразования​ связать несколько файлов​

​ значения ячеек. А​​ ссылку, т.д.».​ таблица. В столбце​ преобразовать дату в​ условия, как должно​Excel «ЗАМЕНИТЬ».​ в статье «Журнал​ ячейки В7 вниз​ «Дата»​ ​ формата – формат​
​Set ra =​​: Смотрите мой предыдущий​​SkyPro​​ типа такой​​cell.NumberFormat = «@»​ 09:00:01​

​ по уникальному полю​​ столбцs с датами​Рассмотрим,​ С написаны даты​ нужном нам формате,​ быть написано число​Итак, убираем букву​ изменений в Excel».​

​ по столбцу. Получилось​​.​ячейки​ Range([C1], Range(«C» &​

​ ответ (подсказка: первая​​, то что надо,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ТЕКСТ(RC1+RC2;»ДД.ММ.ГГГГч:мм:сс»)​

​cell.Value = a​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Текст()​ — коду специальности.​ и формулами удалить.​как преобразовать число в​

​ в формате «Дата».​​ достаточно установить обычным​
​ по формату «Дата»,​ «г» в конце​

​Здесь рассмотрим, как​​ так.​ ​У нас такая​
​и формат​ Rows.Count).End(xlUp))​
​ строка)​ спасибо) ++++​
​(между​
​Next cell​
​For Each cell​
​ Обнаружилось, что коды​
​ Подробнее о способах​

​ дату​​ Нам нужно эти​​ способом формат даты​​ в который будем​ дат.​
​преобразовать в​

Преобразовать текст в дату

​Даты в столбце​​ таблица с датами,​значения​ra.NumberFormat = «General»​

​Если же вам​тему прошу закрыть)​ГГГГ​
​MsgBox «готов»​ In Selection​

​ воспринимается программой как​ копирования, смотрите в​Excel​

​ даты перевести в​​ в ячейке. (Формат​

​ преобразовывать это значение.​​В ячейке В1​Excel текст в дату​

​ B написаны как​ написанными порядковыми номерами.​, написанного в ячейке.​ra.Value = Evaluate(«TEXT(«​ надо формулой, я​Равиль​и​End S​cell.Formula = cell.Text​ даты, а не​ статье «Копирование в​

​и​ текст (формат текст).​ ячейки -> Дата).​ Нажимаем «ОК».​ вставляем формулу. На​и​

​ текст (текстовый формат​

​Преобразуем порядковые номера дат​

​Формат ячейки​ & ra.Address(, ,​

​ бы сделал так:​​: Здравствуйте, нужно преобразовать​чч​

​ub​​Next cell​ как текст. Собственно,​ Excel».​

​дату в число.​Выделяем ячейку D26. На​Теперь этот столбец​

​Формула получится такая.​

​ закладке «Формулы» в​​дату в текст Excel​значений​ в привычные для​
​в​ , True) &​=—ТЕКСТ(A1;»00-00-00″)​ текст 160201 в​столько пробелов, сколько​SkyPro​

​MsgBox «готов»​​ вопрос. Как изменить​Если по какой-то​Например, в ячейке​ закладке «Главная» в​

​ можно сортировать по​

​ =ЗАМЕНИТЬ(A26;11;1;»»)Копируем формулу по​
​ разделе «Библиотека функций»​. Если мы скопировали​). Формат​ нас даты.​

​Excel​ «,»»00-00-00″»)»)​Равиль​
​ дату 16.02.2001​
​ нужно)​: Странная задача..​
​End Sub​ формат кодов специальностей​
​ причине не считаются​
​ Excel дата написана​

​ разделе «Библиотека функций»​ дате. Как это​ столбцу. Получилось так.​

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что такое серийный номер 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 — работа с датами: преобразуем текст в дату.

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

Я хотел выделить на тему работы с датами в Excel один пост. Однако, начав писать понял, что материала очень много, даже с учетом формата этого канала — «примеры из офисной жизни». Датам будет посвящено несколько постов и первый из них перед вами — сегодня поговорим о преобразовании текста в формат дат Excel.

Делитесь этим материалом в своих социальных сетях, подписывайтесь на канал и ставьте лайки.

Зачастую, при выгрузке отчетов из разных систем в Excel (SAP, 1С и д.р.) колонки, содержащие даты воспринимаются как текстовые поля. Понять это можно очень просто — когда вы используете инструмент Фильтр, в колонке, содержащей даты, Excel автоматически сортирует данные по годам, месяцам и дням. Если же в колонке «кривые» данные, то фильтр выбрасывает все значения:

Не обязательно использовать фильтр, чтобы понять, что перед нами «лжедаты». В Excel есть функция ТИП(), которая позволяет определить тип данных в ячейке, которые переданы ей в качестве аргумента. Функция возвращает результат в виде числа, в котором закодирован тип данных в ячейке:

• 1 — число
• 2 — текст
• 4 — логическое значение
• 16 — значение ошибки
• 64 — массив

Если в ячейке дата — функция ТИП() вернет 1, если нет — 2.

Обратите внимание, что Excel хранит даты как числа от 1 до 2958466, что соответствует датам от 01.01.1900 до 31.12.9999. Здесь вы можете ознакомиться с принципом хранения дат и времени в Excel.

Если вместо дат, Excel отображает цифры в указанном выше диапазоне — попробуйте поменять тип данных — выделив диапазон ячеек и в меню «Формат ячеек» выбрать числовой формат «Дата» и нужный вариант отображения.

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

Возникает логичный вопрос — как преобразовать эти данные?

Есть несколько вариантов:

Через инструмент «Найти и заменить» :

• находим в ячейке символ, отличающийся от цифр и точек в формате даты, копируем его (лучше именно копировать, т.к. он может отображаться внешне как пробел, но на самом деле быть другим символом ASCII )
• выделяем диапазон ячеек, в котором нужно сделать преобразование
• запускаем инструмент «Найти и заменить» и в поле «Найти» вставляем скопированный ранее символ, а поле «Заменить» оставляем пустым и нажимаем «Заменить все»

Через сочетание функций ДАТА (ГОД(),МЕСЯЦ(),ДЕНЬ()) :

Если вы не нашли «странных» символов внутри ячейки с «датами» то можно попробовать создать рядом чистую колонку и воспользоваться сочетанием функций ДАТА(ГОД(),МЕСЯЦ(),ДЕНЬ()) — в 80% случаев такой прием позволяет получить новую колонку с данными в нормальном формате, после чего ее можно скопировать и вставить «как значения»:

Собрать дату «по крупицам» через функции работы с текстом — вариант, для особо тяжелых случаев, когда вместо дат в ячейках что-то вроде «26 января 2020 __» и т.п. — способ наиболее трудоемкий и потребует сделать «несколько» приседаний.

Нам понадобятся функции работы с текстом: ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ(), ПОИСК(), ДЛСТР() — о том, как с ними работать, можно прочитать на этом канале здесь . Также, рекомендую вспомнить принцип работы функции ВПР() — об этом тоже есть информация на этом канале здесь . Для очистки данных нам понадобиться функция СЖПРОБЕЛЫ( текст ) — она удаляет лишние пробелы в начале и конце текста.

Необходимо создать 4 колонки: в первых трех мы будем вытягивать данные о дне, месяце и годе, а в четвертой (колонка «E») собирать все воедино функцией ДАТА():

В колонке «День» нам необходимо поместить следующее выражение:

=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1) — мы вырезаем символы с лева до первого пробела, который находится в кавычках первого аргумента функции ПОИСК() — » «, а «-1» здесь как раз для того, чтобы пробел не попал в итоговый результат:

В колонке Месяц будет самое сложное выражение — много функций работы с текстом и ВПР(). Для ВПР () надо создать таблицу подстановки и назвать ее «таб_месяц»:

Если кратко, то мы должны вырезать из ячеек колонки Дата название месяца и передать его функции ВПР() в качестве искомого текста, а уже при помощи ВПР() затянуть числовое значение месяца из таблицы подстановки. Готовое выражения выглядит вот так:

=ВПР(СЖПРОБЕЛЫ(ПСТР(A2;ПОИСК(» «;A2)+1;ПОИСК(» «;A2;ДЛСТР(B2)+2)-ДЛСТР(B2)-1));таб_месяц;2;0)

В качестве начальной позиции для вырезания внутри функции ПСТР() мы ищем позицию первого пробела » » в строке и прибавляем к этому значению 1 (чтобы не вырезать пробел), а чтобы определить количество знаков, которые необходимо вырезать — мы используем сложное выражение: ПОИСК(» «;A2;ДЛСТР(B2)+2)-ДЛСТР(B2)-1) — оно ищет позицию второго пробела при помощи функции ПОИСК, причем поиск осуществляется не с начала строки, а с позиции за первым пробелом. Для этого мы определяем длину строки в ячейке День и прибавляем 2 знака — ДЛСТР(B2)+2 , далее из позиции второго пробела мы вычитаем длину второго месяца — 1 пробел: ДЛСТР(B2)-1 . Все это обрамляем в функцию СЖПРОБЕЛЫ() , чтобы вырезать в начале и конце получившейся строки все пробелы, в противном случае функция ВПР может вызвать ошибку. В готовом виде выражение выглядит примерно так:

С колонкой Год все гораздо проще — просто вырезаем с правой стороны 4 символа =ПРАВСИМВ(A2;4) :

В колонке Дата мы просто собираем полученные значения функцией ДАТА(ГОД;МЕСЯЦ;ДЕНЬ) :

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

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

Источник