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


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

      Рассмотрим, как преобразовать дату в текст Excel. Нам нужно, чтобы дата была написана в текстовом формате.       Сначала вспомним, как записаны и как хранятся даты в Excel.В Excel даты хранятся под порядковыми номерами. Например, дата 01.01.1900 г. хранится под порядковым номером – 1. Дата 02.01.1900 г имеет порядковый номер 2, дата 27.01.2015 г. имеет порядковый номер – 42031, т.д.       Чтобы преобразовать этот порядковый номер в привычный для нас формат даты, нужно установить в ячейке формат даты. Какими способами вызвать диалоговое окно и сменить формат, смотрите в статье «Формат Excel» тут.      Уточним еще один момент - формат даты в Excel.В Excel два формата – формат ячейки и формат значения, написанного в ячейке.      Формат ячейки в Excel  – это внешний вид данных в ячейке - шрифт, размер шрифта; число, записанное с разрядами; в каком месте ячейки будут написаны данные – слева, справа, по центру, выровнены, т.д.Например, при числовом формате ячеек данные пишутся от правой границы ячейки, а при текстовом формате – от левой границы ячейки.      Формат значения ячейки Excel – это формат, с помощью которого Excel понимает ЧТО написано – число, дата, текст, т.д., и проводит различные вычисления в зависимости от формата числа, текста, т.д. Если формат чисел стоит не верный, то формула считать не будет. Подробнее об этом читайте в статье «Преобразовать текст в число Excel».Теперь рассмотрим, как преобразовать порядковый номер дат в формат «Дата».      У нас такая таблица с датами, написанными порядковыми номерами.Преобразуем порядковые номера дат в привычные для нас даты.      Если нужно изменить формат дат в этом же столбце, то выделяем ячейки, устанавливаем нужный формат дат.      Если нужно перенести эти данные в другой столбец, то выделяем ячейки, копируем в другой столбец и устанавливаем формат ячеек «Дата». Здесь в столбце B дата стоит в формате ячейки «Дата».      Excel посчитает даты, записанные и порядковыми номерами, но нам удобнее видеть даты в формате «Дата».      Преобразовать дату в текст в Excel.Даты записаны так. Формат ячеек – «Дата».Преобразовать дату в текст Excel.Нажимаем на ячейку В7. На закладке «Формулы» в разделе «Библиотека функций» в функциях «Текстовые» выбираем функцию «Текст».  Диалоговое окно функции  «Текст» заполнили так.

Нажимаем «ОК». Получилась такая формула.  =ТЕКСТ(А7;"ДД.ММ.ГГГГ")   Копируем формулу из ячейки В7 вниз по столбцу. Получилось так.

Преобразовать дату в текст Excel. Даты в столбце B написаны как текст (текстовый формат значений). Формат в ячейках (внешний вид  данных)стоит – «Общий». Если нужно, то можно изменить формат ячеек «Общий» на формат «Текстовый».      Как скопировать дату в текстовом формате в формат "Дата", смотрите в статье «Преобразовать текст в дату Excel».        Бывает, что в ячейках дата написана одним числом (например, так. 20160112). Нам нужно преобразовать это число в дату, разделить точками год, месяц, день. Как это сделать, смотрите в статье "Преобразовать число в дату Excel".      В Excel можно отследить изменения, произведенные ранее. Например, сотрудник без вашего ведома изменил некоторые цифры. Или, например, составляем отчет. Нам нужно посмотреть предыдущую цифру, которая стояла в ячейке, т.д. Подробнее о работе с этой функцией смотрите в статье "Журнал изменений в Excel".

www.excel-office.ru

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

gototopgototop Поменять, закрепить, развернуть в таблице Excel. Преобразовать текст в дату Excel.       Здесь рассмотрим, как преобразовать в Excel текст в дату и дату в текст Excel. Если мы скопировали или получили в «наследство» таблицу, в которой даты написаны текстом, мы не сможем отсортировать данные по дате. В ячейках есть формат ячейки, а есть формат значений.       Бывает, стоит в ячейке формат «Дата», а сортировка по датам не работает, п.ч. даты написаны текстом. Подробнее про формат ячеек и значений в Excel, смотрите в статье "Преобразовать дату в текст Excel".      У нас такая таблица.В ячейках таблицы стоит формат «Дата», но написаны даты в текстовом формате. В конце даты стоит буква «г» (12.02. 2000г). Как убрать букву «г» и сделать эти даты в формате «Дата».  Просто, установив в ячейке формат «Дата», ничего не получится. Нам поможет функция в Excel «ЗАМЕНИТЬ».       Итак, убираем букву «г» в конце дат.В ячейке В1 вставляем формулу. На закладке «Формулы» в разделе «Библиотека функций» выбираем - «Текстовые». Нажимаем из списка на функцию «ЗАМЕНИТЬ». Заполняем диалоговое окно так. В строке «Старый текст» указываем первую ячейку, в которой нужно убрать букву «г».      В строке «Нач_поз» указываем, какой по счету символ нужно убрать. У нас буква «г» - это 11 по счету символ (считаем и точки, и пробелы).      В строке «Число знаков» написали цифру 1, п.ч. стоит одна буква. Если бы было написано слово «год», то в строке написали бы цифру «3», п.ч. нужно убрать три символа (буквы).      В строке «Новый текст» ничего не пишем, нажимаем один раз на пробел, п.ч. ничего не должно стоять после цифр.      В этом диалоговом окне мы написали условия, как должно быть написано число по формату «Дата», в который будем преобразовывать это значение. Нажимаем «ОК».        Формула получится такая.  =ЗАМЕНИТЬ(A26;11;1;"")Копируем формулу по столбцу. Получилось так.Функция ЗАМЕНИТЬ в Excel.Осталось преобразовать этот текст в дату.      Преобразовать текст в дату Excel.В новом столбце устанавливаем формат ячеек «Дата», выделяем первую ячейку. На закладке «Формулы» в разделе «Библиотека функций» нажимаем на кнопку «Время и дата». Нажимаем в появившемся списке на функцию «ДАТАЗНАЧ». В появившемся диалоговом окне указываем адрес выделенной ячейки. Нажимаем «ОК». Копируем формулу по столбцу. Получилось так, смотрите ячейку С27.Преобразовать текст в дату Excel.      Обратите внимание!В ячейке С27 стоит дата в привычном нам формате даты, п.ч. мы перед вводом формулы настроили формат ячеек «Дата».В ячейке С26 тоже стоит дата, но указан порядковый номер даты. В таком формате Excel хранит даты. Чтобы преобразовать дату в нужном нам формате, достаточно установить обычным способом формат даты в ячейке. (Формат ячейки -> Дата).      Теперь этот столбец можно сортировать по дате. Как это сделать, смотрите в статье «Сортировка по дате в Excel».      Преобразовать число в дату в Excel. Бывает, по разным причинам, дата в ячейке написана одним числом (например, так - 20160112). нам нужно придать дате нормальный вид, отделив точками год, месяц, день (например, так - 2016.01.12). Как это сделать, читайте в статье "Преобразовать число в дату Excel".      Преобразовать дату в текст Excel.Чтобы преобразовать дату в текст Excel (установить текстовый формат, а не записать прописью), воспользуемся функцией «ТЕКСТ» в Excel. Функция «Текст» в Excel преобразует формат значения ячейки в текстовый формат (число будет написано цифрами, не прописью).      У нас такая таблица. В столбце С написаны даты в формате «Дата». Нам нужно эти даты перевести в текст (формат текст).Выделяем ячейку D26. На закладке «Главная» в разделе «Библиотека функций» нажимаем кнопку «Текстовые», выбираем функцию «Текст». Появившееся диалоговое окно заполняем так.В строке «Значение» указали адрес ячейки, дату в которой нужно преобразовать в текст.В строке «Формат» указали формат, в котором нужно записать число.Получилось так.Преобразовать дату в текст Excel.Формат даты пишем заглавными буквами. Например, ММ (большими буквами) -это месяц, а мм (маленькими буквами) – это минуты, формула будет считать по-другому.Если нужен другой формат, то пропишем его в формуле. Например, так.    В Excel можно многое закрепить, чтобы не смещалось при перемещении, после фильтра, при копировании, т.д. - заголовок, строку, картинку в ячейке, ссылку, ячейку в формуле, т.д. Смотрите об этих способах в статье "Как закрепить в Excel заголовок, строку, ячейку, ссылку, т.д.".

www.excel-office.ru

Как сцепить дату и текст в Excel

  • 28.12.2015
  • Просмотров: 30833
  • Excel

В этом уроке расскажу как сцепить дату и текст в Excel. Допустим, у вас есть несколько ячеек, одна из которой дата. Необходимо получиться ячейку, в которой будет храниться запись "Договор №150 от 28.12.2015" при условии, что вид документа, его номер и дата будут храниться в разных ячейках.

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

Как сцепить дату и текст в Excel

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

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

  • Первый - ячейка со значением, которое необходимо преобразовать;
  • Второй - формат, в котором преобразованные данные необходимо вывести.

Если с первым все понятно, то второй можно использовать по разному. В нашем примере формат будет "ДД.ММ.ГГГГ"- это говорит о том, что при выводе информации день и месяц надо вывести в виде двух чисел, а год в виде четырехзначного числа. Пример, 01.02.2015. Конечно в вашем случае это может быть другой формат.

Как сцепить дату и текст в Excel

Вся двойная функция будет выглядеть следующим образом:=СЦЕПИТЬ(A2;" №";B2;" от ";ТЕКСТ(C2;"ДД.ММ.ГГГГ"))

Как сцепить дату и текст в Excel

Только в этом случае получиться нужным нам результат.

Более подробно о форматах вывода даты вы можете прочитать в справке. Заходим во вкладку "Формулы", далее нажимаем иконку "Текстовые" и из выпадающего списка выбираем "Текст". В открывшемся окне нажимаем ссылку "Справка по этой функции".

Как сцепить дату и текст в Excel

Как сцепить дату и текст в Excel

Здесь, в блоке "Рекомендации по форматам даты и времени", вы найдете все возможные выводы даты и времени.

Как сцепить дату и текст в Excel

Не забудьте поделиться ссылкой на статью ⇒

Функции Excel в строке состояния

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

  • 04.01.2016
  • Просмотров: 6079
  • Excel
Читать полностью Совпадение в столбцах Excel

Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.

  • 24.11.2015
  • Просмотров: 48334
  • Excel
  • Видеоурок
Читать полностью Дубликаты в Excel

В этой статье расскажу как удалить дубликаты в Excel. Рассматривать будем самый простой штатный способ, который появился начиная с Excel 2007.

  • 30.10.2015
  • Просмотров: 2585
  • Excel
  • Видеоурок
Читать полностью Стаж работы в Excel

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

  • 12.01.2016
  • Просмотров: 7779
  • Excel
Читать полностью Как удалить страницу из PDF

В этом уроке расскажу как удалить страницу из PDF файла. Этим способом можно удалить любую страницу из файла и поможет нам в этом браузер Google Chrome.

Читать полностью

4upc.ru

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

Текст в дату в ExcelДата: 11 февраля 2016 Категория: Excel Поделиться, добавить в закладки или распечатать статью

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

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

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

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

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

Поделиться, добавить в закладки или распечатать статью

officelegko.com

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

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

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

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

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

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

Ячейки с зеленым индикатором ошибки в левом верхнем углу

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

Примечания: Во-первых убедитесь, что ошибок включен в Microsoft Excel. Это можно сделать:

  1. Выберите пункты Файл > Параметры > Формулы.

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

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

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

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

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

    Совет: Для отмены выделения ячеек щелкните любую ячейку в таблице.

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

    Кнопка ошибки

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

    Команды преобразования дат

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

    Преобразованные даты

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

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

Выполните следующие действия.

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

    Проверка числового формата: на вкладке Главная в группе число щелкните стрелку рядом с полем Числовой формат и выберите пункт Общие.

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

    1. Введите = ДАТАЗНАЧ (

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

    3. Введите )

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

      Что такое числовом Excel?

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

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

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

    Сочетание клавиш:  Можно также нажать клавиши CTRL + C.

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

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

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

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

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

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

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

support.office.com

Вводим в ячейку число как текст

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

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

Как преобразовать число в текст?

Вводить числа как текст в Excel приходится достаточно часто. Например:

  1. Номер фактуры или договора состоит из более 12-ти чисел. Допустим номер: «1234567891012» Excel округляет большие числа в такой формат «1,23457E+12».
  2. Некоторые номера телефонов автоматически конвертируются в дату. Номер телефона: 2012-10-17 отображается как «17.10.2012».
  3. Иногда нужно записать формулу обычным текстом.

Поэтому важно научиться управлять форматами ячеек.

Попробуйте заполнить табличку, так как показано на рисунке:

Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:

Необходимо заставить Excel распознавать числа как текст.

Решение 1. В ячейке D2 введите перед текстовым значением символ апострофа «'» (апостроф следует ввести с английской раскладки клавиатуры). Теперь число преобразовано в текстовое значение.

Решение 2. Задайте в ячейке D2 текстовый формат. Для этого откройте окно «Формат ячеек» CTRL+1. На вкладке «Число» выберите из списка «Числовые формат» опцию «Текстовый» и нажмите ОК. Теперь можно вводить любые числа как текст без апострофа.

Главным отличием выше описанных двух примеров является закрепление за ячейкой способа отображения ее значений с помощью присвоения формата. Если в первом решении в ячейку будут вводиться новые типы данных (без апострофа), то они будут отображаться по умолчанию.

Примечание. Второе решение закрепляет за ячейкой ее формат, и любые другие типы данных будут преобразованы, пока пользователь не изменит для нее другие форматы (например, «Общий» - по умолчанию).



Математические операции с текстом в Excel

Если функция ссылается на ячейку, в которой число записано текстом, то при вычислении значение этой ячейки равно «0». Но здесь нужно быть очень внимательным, так как бывают исключения. Это может приводить к ошибочным расчетам. Приводим конкретный пример.

  1. Заполните диапазон ячеек A1:D6 так как показано на рисунке:
  2. Колонку E не нужно заполнять, так как в ней отображается содержимое ячеек в колонке D. Обратите внимание в ячейках B3 и B6 числовые данные записаны как текстовые через апостроф «'».
  3. В колонку D введите формулы, которые отображаются в колонке E как на рисунке.

Теперь обратите внимание на результаты вычислений функций и формул в колонке D.

В ячейке D2 функция =СУММ(A2:C2) отображает правильный результат 1+100+10=111. А в ячейке D3 функция суммирования меняет значение ячейки B3 на число 0, так как там данные отображаются в текстовом формате. В результате: 1+0+10=11.

Внимание! При вычислении аналогичных значений ячеек, но уже с помощью обычной формулы =A6+B6+C6 все значения ячеек воспринимаются как числовые значения, несмотря на то, что ячейка B6 имеет текстовый формат. Формулы могут суммировать, умножать, разделять и вычитать текст, если он состоит только из символов чисел (0-9), а функции не могут.

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

Читайте так же: как перевести число и сумму прописью в Excel.

exceltable.com

Объединение текста с датой или временем

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

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

Пример

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

Функция текст требуется два аргумента (то есть элементы между круглых скобок). Первый аргумент — значению, например даты или числа. Это значение преобразуется в текстовое значение. Второй аргумент формат, — это специальная строка, Excel распознает и используется для отображения значения преобразованный текст в формате, который можно указать. В приведенных примерах второе и третье дат в B3 и B4, преобразуются в текст и отображается в двух различных форматов: «д МММ ГГГГ» (что приводит к 4 июне 2010 г.) и «д мммм гггг». (в результате чего июн 07 2010).

См. также

ТЕКСТ

support.office.com