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

Обновлено: 20.11.2024

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

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

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

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

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

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

Нажмите "Файл" > "Параметры" > "Формулы".

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

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

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

Следуйте этой процедуре, чтобы преобразовать дату в текстовом формате в обычную дату:

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

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

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

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

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

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

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

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

Нажмите «Числовой формат» и выберите нужный формат даты.

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

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

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

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

В пустой ячейке:

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

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

Введите )

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

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

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

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

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

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

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

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

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

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

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В предыдущей статье мы обсуждали различные способы преобразования текста в дату в Excel. Если вы ищете решение противоположной задачи — замены даты в Excel на текст — вам снова доступно несколько вариантов.

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

Использование функции ТЕКСТ в Excel для преобразования даты в текст

Функция Excel ТЕКСТ специально разработана для преобразования числового значения в текстовую строку и отображения ее в указанном вами формате.

Синтаксис функции ТЕКСТ Excel следующий:

  • значение – это числовое значение, которое необходимо преобразовать в текст. Это может быть число, формула, возвращающая числовое значение, или ссылка на ячейку, содержащую число.
  • format_text — это способ форматирования результирующего текстового значения, представленного в виде текстовой строки, заключенной в кавычки.

Например, вы можете использовать следующую формулу для преобразования даты в ячейке A1 в текстовую строку в традиционном для США формате даты (месяц/день/год):

=ТЕКСТ(A1;"мм/дд/гггг")

Как видно на снимке экрана выше, значение, возвращаемое формулой ТЕКСТ, выравнивается по левому краю, что является первым признаком, указывающим на дату в текстовом формате. Помимо выравнивания в ячейке есть еще несколько индикаторов, которые помогут вам различать даты и текстовые строки в Excel.

Пример 1. Как преобразовать дату в текстовые строки в разных форматах

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

Microsoft Excel понимает следующие коды даты.

Месяцы:

  • m – номер месяца без начального нуля.
  • мм — номер месяца с нулем в начале
  • ммм — краткая форма названия месяца, например Мар
    • .
  • мммм — полная форма названия месяца, например март
    • .
  • ммммм - месяц как первая буква, например М (обозначает март и май)

Дни:

  • d — число дней без начального нуля.
  • дд — номер дня с нулем в начале
  • ddd – сокращенный день недели, например Sun
  • dddd — полное название дня недели, например Воскресенье

Годы:

  • yy — год из двух цифр
  • yyyy — год из четырех цифр

Чтобы преобразованная текстовая дата отображалась именно так, как вы хотите, вы можете разделить коды даты различными разделителями, такими как тире (-), косая черта (/), запятая (,), двоеточие (:) и т. д. Вот несколько примеров:

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

Еще несколько примеров формул и их результаты показаны ниже:

Пример 2. Как преобразовать время в текстовые строки

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

Часы работы:

  • ч – часы без начального нуля, от 0 до 23.
  • чч – часы с нулем в начале, от 00 до 23.

Минуты:

  • m – минуты без начального нуля, от 0 до 59.
  • мм – минуты с нулем в начале, от 00 до 59.

Секунды:

  • s — секунды без начального нуля
  • ss — секунды с нулем в начале

Время суток:

  • AM/PM — отображается как AM или PM
  • Если не указано, используется 24-часовой формат времени

Как вы, наверное, заметили, m-коды используются как для месяцев, так и для минут, и вам может быть любопытно, как Microsoft Excel различает их. Если вы поместите "m" сразу после кодов h (часов) или непосредственно перед кодами s (секунд), Excel поймет, что вы хотите отображать минуты, а не месяц. Да, это так просто : )

Функция ТЕКСТ в Excel позволяет включать в аргумент format_text коды даты и времени, например:

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

Результаты ваших формул TEXT могут выглядеть примерно так:

Пример 3. Как преобразовать текущую дату в текст в Excel

Если вы хотите преобразовать текущую дату в текстовый формат, вы можете использовать функцию ТЕКСТ Excel в сочетании с функцией СЕГОДНЯ, которая возвращает текущую дату, например:

Пример 4. Формула Excel TEXT для преобразования текста в дату

Хотя основным назначением функции ТЕКСТ в Excel является преобразование чисел в текст, она также может выполнять обратное преобразование, т. е. изменять текст на дату. Для этого вы просто добавляете двойное отрицание (--) в формулу ТЕКСТ.

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

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

Преобразование даты в текст с помощью мастера Excel «Текст в столбцы»

Как вы только что видели, функция ТЕКСТ в Excel отлично справляется с преобразованием дат в текст. Но если вы не большой поклонник формул Excel, это решение может вам понравиться больше.

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

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

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

Если вы не возражаете против формата по умолчанию, выполните следующие действия:

Совет. Если вы не хотите, чтобы итоговые текстовые строки перезаписывали исходные даты, укажите Destination для верхней ячейки нового столбца.

Это было очень просто, правда? На приведенном ниже снимке экрана показан результат: даты преобразованы в текстовые строки в формате короткой даты по умолчанию, установленном в региональных настройках Windows, в моем случае это «мм/дд/гггг»:

Преобразование даты Excel в текст с помощью Блокнота

Еще один быстрый способ без формул превратить даты Excel в текстовые строки — использовать Блокнот или любой другой текстовый редактор. В отличие от мастера преобразования текста в столбцы, он позволяет преобразовать дату Excel в текст в любом формате по вашему выбору.

  1. На листе Excel отформатируйте даты именно так, как вы хотите, чтобы текстовые строки выглядели.
  2. Выберите все даты, которые вы хотите преобразовать, и нажмите Ctrl+C, чтобы скопировать их.
  3. Откройте Блокнот или любой другой текстовый редактор и вставьте туда скопированные даты.
  4. Блокнот автоматически преобразует даты в текстовый формат. Нажмите Ctrl+A, чтобы выделить все текстовые строки, а затем Ctrl+C, чтобы скопировать их.
  5. Вернитесь в Microsoft Excel, выберите столбец, в который вы хотите вставить текстовые строки, и примените к нему текстовый формат. Для этого нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек, и выберите Текст на вкладке Число.
  6. Наконец, выберите первую ячейку, в которую вы хотите вставить текстовые строки, и нажмите Ctrl+V, чтобы вставить их.

    7. На следующем снимке экрана показан результат с исходными датами Excel в столбце B и текстовыми записями в столбце D. Обратите внимание, что преобразованные текстовые строки отражают исходный формат даты с абсолютной точностью, за исключением того, что они выделены по левому краю, так как все текстовые значения должны быть в Excel.

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

    Если вам нужно преобразовать даты в текст (т. е. преобразовать дату в строку), вы можете использовать функцию ТЕКСТ. Функция ТЕКСТ может использовать такие шаблоны, как «дд/мм/гггг», «гггг-мм-дд» и т. д., для преобразования действительной даты в текстовое значение. См. таблицу ниже со списком доступных токенов.

    Даты и время в Excel хранятся в виде серийных чисел и на лету преобразуются в удобочитаемые значения с использованием числовых форматов. Когда вы вводите дату в Excel, вы можете применить числовой формат для отображения этой даты по своему усмотрению. Аналогичным образом функция ТЕКСТ позволяет преобразовать дату или время в текст в предпочтительном формате. Например, если в ячейку A1 введена дата 9 января 2000 г., вы можете использовать ТЕКСТ для преобразования этой даты в следующие текстовые строки следующим образом:

    Коды формата даты

    При условии, что дата 9 января 2012 г., вот более полный набор кодов форматирования для даты, а также пример вывода.

    Код формата Вывод
    d 9
    dd 09
    ddd Пн
    dddd Понедельник
    Пн 1
    мм 01
    ммм Янв
    мммм Январь
    ммммм Дж
    гг 12
    гггг 2012
    мм/дд/гггг 09/01/2012
    м/д/г 09.01.12
    ддд, ммм д Пн, 9 января
    мм/ дд/гггг ч:мм AM/PM 09/01/2012 17:15
    дд/мм/гггг чч:мм:сс< /td> 01.09.2012 17:15:00

    Вы можете использовать функцию ТЕКСТ для преобразования дат или любых числовых значений в фиксированный текстовый формат. Вы можете изучить доступные форматы, перейдя к Формат ячеек (Win: Ctrl + 1, Mac: Cmd + 1) и выбрав различные категории форматов в списке слева. См. также Пользовательские числовые форматы Excel.

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

    Функция Excel TEXT специально разработана для преобразования числового значения в текстовую строку и отображения ее в указанном вами формате.

    Ниже приведены шаги для преобразования даты в текст:

    В этом примере используются следующие данные.

    Шаг 1. Кроме того, в ячейку даты введите следующую формулу
    =ТЕКСТ(B7,"ДД-ММ-ГГГГ")

    Шаг 2. После ввода формулы нажмите "ОК". Теперь вы увидите, что дата выровнена по левому краю, что означает, что дата представлена ​​в текстовом формате.

    Дополнительный шаг: в Excel есть опция, позволяющая выровнять формат текста по правой стороне, чтобы ввести «–» перед формулой. Ваш текст будет выровнен по правому краю.

    Шаг 3. Перетащите формулу вниз. Ваши даты будут преобразованы в текст. Как показано на изображении.

    Как преобразовать текстовые строки в другой формат

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

    Месяцы:

    • m – номер месяца без начального нуля.
    • мм — номер месяца с нулем в начале.
    • ммм — краткая форма названия месяца, например, январь, февраль, март.
    • мммм – длинная форма названия месяца.
    • ммммм — месяц в качестве первой буквы, например М (обозначает март и май)

    Дни:

    • д – количество дней без начального нуля.
    • дд — номер дня с нулем в начале.
    • ддд – сокращенный день недели.
    • дддд – полное название дня недели

    Годы:

    • гг – год из двух цифр.
    • Yyyy — год из четырех цифр.

    См. изображение ниже

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

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