Дата Excel как дата

Обновлено: 21.11.2024

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

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

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

Когда речь идет о форматах текстовых данных, нет ничего особенного. Это зависит от человека, который вводит эти даты.

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

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

Итак, приступим.

1. Преобразование текста в дату

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

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

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

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

И тогда вы можете изменить его формат на сегодняшний день.

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

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

2. Дата с обратной косой чертой

Система дат Excel поддерживает дату с косой чертой. И если у нас есть дата с обратной косой чертой, она будет рассматриваться как текст.

Чтобы решить эту проблему, мы можем использовать приведенную ниже формулу.

<р>3. Дата с названием месяца

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

=DATEVALUE(RIGHT(A3,2)&"-"&TEXT(MID(A3,6,3)",MMM")&"-"&LEFT(A3,4))

<р>4. Дата с точками

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

=ДАТА(ПРАВО(A4,4),СРЕДИНА(A4,4,2),ЛЕВО(A4,2))

<р>5. Дата с названием месяца и запятой

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

=DATEVALUE(LEFT(A5,2)&"-"&TEXT(MID(A5,4,3)",MMM")&"-"&RIGHT(A5,4))

<р>6. Дата с названием дня

Иногда люди сохраняют дату вместе с названием дня. И, если эта дата не в правильном формате, Excel будет рассматривать ее как текст.

=ДАТАЗНАЧ(СРЕДН(A6,НАЙТИ("",,A6)+6,2)&"-"&СРЕДН(A6,НАЙТИ("",,A6)+2,3)&"-"&ПРАВО( А6,4))

<р>7. Дата с названием дня в конце

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

=ДАТАЗНАЧ(СРЕДНЯЯ(A7,10,2)&"-"&СРЕДНЯЯ(A7,6,3)&"-"&СЛЕВА(A7,4))

<р>8. Дата с суффиксом дня

У нас есть суффикс «th» с номером дня, и он делает его текстом вместо даты, поскольку Excel не может его распознать.

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

=ДАТА(ПРАВО(A9,4),СРЕДИНА(A9,4,2),ЛЕВО(A9,2))

<р>10. Дата с коротким названием месяца

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

=ДАТАЗНАЧ(СРЕДНЯЯ(A10,5,2)&"-"&ЛЕВО(A10,3)&"-"&ПРАВО(A10,4))

<р>11. Дата без пробела между днем, месяцем и годом

И для даты, где нет пробела между днем, месяцем и годом.

=ДАТА(ПРАВО(A11,2),СРЕДИНА(A11,3,2),ЛЕВО(A11,2))

22 мысли

Оставить комментарий Отменить ответ

Справка — формат даты с суффиксом учитывает только суффикс "th" — не работает в случае дат с суффиксом, например "st" (как в 1-м, 21-м, 31-м), "rd" (как 2-е, 22-е) и «rd» (например, 3-й, 23-й)

ПОМОГИТЕ! Нет ничего, чтобы преобразовать ТЕКСТОВУЮ дату «2022-Jan» в реальную дату. У меня есть много отчетов с диаграммами, и мне всегда нужно перепечатывать даты для каждого. Это сотни текстовых дат примерно для 20-30 графиков.Кажется, в Интернете не найдено никакого решения. Я хотел бы знать, как исправить это с помощью простой формулы. Но вся справка по дате предполагает наличие месяца, дня и года. У меня есть только текст, показывающий год-месяц. Буду рад предложениям, так как мне нужно сделать это так быстро, что повторный ввод занимает слишком много времени.

Джордж, вы хотите преобразовать 2022 января в 1 января 2022 года?

Привет, спасибо, что поделились.
Но когда я его использую, он показывает: «Параметр 2 функции DATE ожидает числовые значения. Но «4/» — это текст, и его нельзя преобразовать в число». Я не знаю почему.

<р>11. Дата без пробела между днем, месяцем и годом
вообще не работает, если месяц октябрь. например 16102015, это не работает.

если некоторые данные 16.12.15, как мы можем преобразовать их в 16/12/2015? Пожалуйста, помогите мне справиться с этим.

Привет, Пунит
У меня есть данные в ячейке в формате: EDM 101, POL 102, POL 301, и я хочу, чтобы они были такими:
EDM 101
POL 102
POL 301 Это означает, что я хочу, чтобы они находились в отдельных ячейках, а не в одной ячейке.

Как рассчитать количество дней между двумя датами.
Спасибо за ответ.

Здравствуйте, Пунит,
Этот совет мне очень помог.
Теперь я могу легко делать свою работу,
Спасибо, что поделились и продолжаете публиковать эти вещи.

Хорошая статья, мне нужно было то же самое, но включая часы….

У меня есть это значение в ячейке Excel «31.07.2019 1:57:00». Мне нужно количество часов, прошедших с того времени до сегодняшнего дня().

любая помощь приветствуется

Здравствуйте
Подскажите, пожалуйста, как преобразовать указанные ниже даты Excel в русский формат ГГГГ.ММ.ДД
ПРИМЕЧАНИЕ с точками между ГГГГ ММ ДД

Формат кажется другим,
когда номер месяца больше 12 !!
заранее спасибо
с уважением
Дерек
========================== =============

01.04.2019
02.04.2019
03.04.2019
04.04.2019
05.04.2019
04/ 08/2019
09/04/2019
10/04/2019
11/04/2019
12/04/2019
15/04/2019
/>16.04.2019
17.04.2019
18.04.2019
23.04.2019
24.04.2019
25.04 /2019
26.04.2019
29.04.2019
30.04.2019
01.05.2019
02.05.2019
02.05.2019
>03.05.2019
07.05.2019
08.05.2019
09.05.2019
10.05.2019
13.05/ 2019
14.05.2019
15.05.2019
16.05.2019
17.05.2019
20.05.2019
21.05.2019
22.05.2019
23.05.2019
24.05.2019
28.05.2019
29.05.2019
30.05.2019
31.05.2019
03.06.2019
04.06.2019
05.06.2019
06 /06/2019
07/06/2019
10/06/2019
11/06/2019
12/06/2019
13/06/2019 < br />14.06.2019
17.06.2019
18.06.2019
19.06.2019
20.06.2019
06/ 21.06.2019
24.06.2019
25.06.2019
26.06.2019
27.06.2019
28.06.2019
01.07.2019
02.07.2019
03.07.2019
07 /04/2019
05/07/2019
08/07/2019
09/07/2019
10/07/2019
11/07/2019 < br />12.07.2019
15.07.2019
16.07.2019
17.07.2019
18.07.2019
07/ 19/2019
22/07/2019
23/07/2019
24/07/2019
25/07/2019
26/07/2019
/>29.07.2019
30.07.2019
31.07.2019
01.08.2019
02.08.2019
05.08 /2019
06.08.2019
07.08.2019
08.08.2019
09.08.2019

Подскажите, пожалуйста, как преобразовать указанные ниже даты в русский формат ГГГГ.ММ.ДД
заранее спасибо
с уважением
Дерек

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

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

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

Поскольку проверка ошибок в 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 для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Когда вы вводите какой-либо текст в ячейку, например "2/2", Excel предполагает, что это дата, и форматирует ее в соответствии с настройкой даты по умолчанию на панели управления. Excel может отформатировать его как «2 февраля». Если вы измените настройку даты в панели управления, формат даты по умолчанию в Excel изменится соответствующим образом. Если вам не нравится формат даты по умолчанию, вы можете выбрать другой формат даты в Excel, например «2 февраля 2012 г.» или «2/2/12». Вы также можете создать свой собственный формат в настольном Excel.

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

Выберите ячейки, которые хотите отформатировать.

В поле "Формат ячеек" перейдите на вкладку "Число".

В списке категорий нажмите Дата.

В разделе "Тип" выберите формат даты. Ваш формат будет предварительно просмотрен в поле «Образец» с первой датой в ваших данных.

Примечание. Форматы даты, начинающиеся со звездочки (*), изменятся, если вы измените региональные настройки даты и времени в панели управления. Форматы без звездочки не изменятся.

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

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

Выберите ячейки, которые хотите отформатировать.

В поле "Формат ячеек" перейдите на вкладку "Число".

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

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

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

Используйте этот код

Месяцы с января по декабрь

Месяцы как первая буква месяца

Дни с воскресенья по субботу

Годы как 1900–9999

Если вы изменяете формат, включающий значения времени, и используете "m" сразу после кода "h" или "hh" или непосредственно перед кодом "ss", Excel отображает минуты вместо месяца.< /p>

Чтобы быстро ввести текущую дату на листе, выберите любую пустую ячейку, нажмите CTRL+; (точка с запятой), а затем при необходимости нажмите клавишу ВВОД.

Чтобы ввести дату, которая будет обновляться до текущей даты каждый раз, когда вы повторно открываете лист или пересчитываете формулу, введите =СЕГОДНЯ() в пустой ячейке и нажмите клавишу ВВОД.

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

Выберите ячейки, которые хотите отформатировать.

Нажмите Control+1 или Command+1.

В поле "Формат ячеек" перейдите на вкладку "Число".

В списке категорий нажмите Дата.

Выберите ячейки, которые хотите отформатировать.

Нажмите Control+1 или Command+1.

В поле "Формат ячеек" перейдите на вкладку "Число".

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

Чтобы преобразовать текст в нераспознанном формате даты в правильную дату Excel, вы можете проанализировать текст и составить правильную дату с помощью формулы, основанной на нескольких функциях: ДАТА, ЛЕВАЯ, СРЕДНЯЯ и ПРАВАЯ. В показанном примере формула в C6 выглядит так:

Эта формула извлекает значения года, месяца и дня по отдельности и использует функцию ДАТА, чтобы объединить их в дату 24 октября 2000 года.

Фон

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

Текст	Дата представления
20001024	24 октября 2000 г.
20050701	1 июля 20115 г.
19980424	24 апреля 1998 г.
28 февраля 2014 г.	28 февраля 2014 г.

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

Функция ДАТА создает допустимую дату, используя три аргумента: год, месяц и день:

В ячейке C6 мы используем функции ЛЕВЫЙ, СРЕДНИЙ и ПРАВЫЙ, чтобы извлечь каждый из этих компонентов из текстовой строки и передать результаты в функцию ДАТА:

Функция LEFT извлекает 4 крайних левых символа для года, функция MID извлекает символы в позициях 5–6 для месяца, а функция RIGHT извлекает 2 крайних правых символа для дня. Каждый результат возвращается непосредственно в функцию DATE. Конечным результатом является правильная дата Excel, которую можно форматировать как угодно.

Этот подход можно настроить по мере необходимости. Например, нераспознанный формат даты в строке 8 — дд.мм.гггг, а формула в C8 — следующая:

Подробный текст

Иногда у вас могут быть даты в более длинной форме, например "11 апреля 2020 г., 08:43:13", которые Excel не распознает должным образом. В этом случае вы можете настроить строку таким образом, чтобы Excel правильно распознавал дату с помощью функции ПОДСТАВИТЬ. В приведенной ниже формуле второй экземпляр пробела (" ") заменяется запятой и пробелом (","):

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

Без формул

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

Добавьте ноль, чтобы исправить даты

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

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

Введите ноль (0) в неиспользуемую ячейку и скопируйте в буфер обмена.
Выберите проблемные даты.
Специальная вставка > Значения > Добавить
Примените формат даты (при необходимости)

Вы также можете добавить ноль в следующую формулу:

где A1 содержит нераспознанную дату.

Отправьте текст в столбцы, чтобы исправить даты

Выберите столбец дат, затем попробуйте Данные > Текст в столбцах > Фиксированное > Готово

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