Как преобразовать текстовые даты в правильный формат даты в Excel? –

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

Зачем использовать правильный формат даты?

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

Хранение дат в Excel

Отображение даты по умолчанию в Excel зависит от региональных настроек вашего компьютера. Однако, когда вы вводите дату в другом формате (например, 24 января 2024 г.), ячейка автоматически использует «Пользовательский» формат. Это также возможно; формат ячейки даты неправильно изменен на «Текст» или «Число», что вызывает следующие проблемы.

• Даты выглядят как числа.
• Ячейки с датами отмечены зеленым треугольником в верхнем левом углу ячейки.
• Показывает в другом формате даты, например, 23/05/24, что может сбить вас с толку, поскольку вы не знаете, какой это год или дата.
• Ошибки формулы из-за неправильного формата.

Хорошая часть заключается в том, что независимо от того, что вы видите, Excel сохраняет дату в виде последовательного порядкового номера от 01 января 1900 г. (или 01 января 1904 г.) до 31 декабря 9999 г. Например, 01 января 1900 г. хранится в Excel как 1 и 01 января 2024 г. хранится как 45292. Вы можете использовать один из следующих методов для преобразования серийного номера в правильный формат даты по своему усмотрению.

1. Даты отображаются в виде чисел в Excel

Когда в ячейках используются другие форматы, такие как «Общий», «Число» или «Текст», Excel будет отображать даты в виде чисел, как показано ниже. Вы можете изменить форматирование ячейки в столбце B, чтобы преобразовать числа в даты. Существует несколько способов изменить формат ячейки в Excel. Мы объясняем здесь версию Microsoft 365 Excel для Mac, и шаги такие же, как и в версии Excel для Windows.

1.1. Использование форматирования копирования и вставки

Вы меняете форматирование первой ячейки (B2) и копируете и вставляете в другие ячейки (от B3 до B6). Хотя наш пример прост, такое копирование и вставка форматирования полезно, когда ячейки разбросаны по разным столбцам на листе.

Даты, отображаемые в виде чисел в Excel

• Щелкните правой кнопкой мыши первую ячейку даты, которую вы хотите отобразить правильно.
• Выберите опцию «Формат ячеек» в контекстном меню, чтобы открыть всплывающее окно «Формат ячеек». Кроме того, вы можете нажать Команда + 1 в Mac или Контроль + 1 в Windows, чтобы открыть всплывающее окно «Формат ячеек».

Параметр «Формат ячеек» в контекстном меню Excel

• Перейдите на вкладку «Число» и измените «Категорию» на «Дата». Выберите формат даты, который вы хотите отображать, в разделе «Тип» и нажмите «ОК», чтобы закрыть всплывающее окно.

Форматирование ячеек даты в Excel

• Теперь выбранная ячейка будет отображать дату вместо числа и копировать эту ячейку (B2), нажав «Control + V» или «Command + V».

Формат, применяемый к первой ячейке

• Выделите все остальные ячейки с номерами (от B3 до B6) и вставьте, нажав «Control + V» или «Command + V».

Параметры вставки Excel

• Нажмите на маленький значок рядом с последней ячейкой, показывающей «Параметры вставки».
• Это покажет множество вариантов и выберите «Форматирование», чтобы применить формат даты и к другим ячейкам. В версии Excel для Windows вы увидите значки вместо текстового меню и выберите значок «Форматирование» в разделе «Другие параметры вставки».
• Это применит форматирование даты ко всем выбранным ячейкам и преобразует число в правильный формат даты.

Применить параметры вставки форматирования

Помните, что вы можете изменить отображение даты на любой формат, например 22-янв-24, 22/01/24 или 01/22/24, во всплывающем окне «Формат ячеек». Выбранный формат будет применен ко всем ячейкам при вставке.

1.2. Выберите ячейки и примените форматирование даты

В этом методе выберите все ячейки (от B1 до B6) и сразу примените форматирование.

• После выбора ячеек перейдите в меню «Главная» и щелкните раскрывающийся список форматов ячеек. Выберите формат «Длинная дата» или «Короткая дата», чтобы преобразовать число в дату.

Применить длинный или короткий формат даты в Excel

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

2. Ячейки даты отображаются зеленым треугольником

Иногда вы увидите зеленый треугольник в ячейках. Это указывает на ошибку в дате из-за неправильного форматирования ячейки или использования 2 цифр года вместо 4 цифр. Просто нажмите на ячейку, чтобы увидеть предупреждающее сообщение: «Эта ячейка содержит строку даты, представленную только двумя цифрами года».

Ошибка формата даты в Excel

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

Преобразование в 4-значный формат даты

3. #ЦЕННОСТЬ! Ошибки формулы

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

VALUE Ошибка из-за неправильного формата даты

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

Отслеживание ошибки в Excel

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

4. Проблема DATEVALUE

Функция DATEVALUE полезна для получения порядкового номера дат из значения ячейки. Однако для этого ячейка ДОЛЖНА быть в текстовом формате плана. В этом случае НЕ СЛЕДУЕТ использовать формат даты, даже если ячейка содержит дату. Например, ссылка на 20 января 2024 года в Функция ДАТАЗНАЧ покажет серийный номер как 45311, только если указанная ячейка имеет формат «Текст». в противном случае вы увидите #ЗНАЧ! ошибка.

DATEVALUE Результат с текстовой ячейкой