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

Было ли у вас случае, когда Microsoft Excel считала простой текст, когда вы вводили что-то вроде «01.05.2023»? Вы не можете использовать эти данные для расчетов или отсортировать их по дате. Это одна из самых частых проблем, с которой сталкиваются пользователи.

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

Давайте поговорим о том, как Microsoft Excel может понимать ваши даты. Это сэкономит много времени и избавит от проблем с таблицами.

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

Способ 1: Изменение формата ячейки на дату

Изменение формата ячейки — самый быстрый способ преобразовать числовое значение в дату. В Microsoft Excel даты хранятся как порядковые числа, где 1 января 1900 года соответствует числу 1, а значение увеличивается на каждый следующий день. Например, число 45000 является датой в 2023 году, и достаточно просто использовать правильное форматирование, чтобы получить стандартное отображение.

1. Выделите ячейку или диапазон ячеек с числовыми значениями, которые необходимо преобразовать в даты. Обратите внимание на то, что числа должны находиться в допустимом диапазоне — от 1 до 2958465, что соответствует периоду с 1 января 1900 года по 31 декабря 9999 года.
2. 
3. Щелкните правой кнопкой мыши по выделенной области и в контекстном меню выберите пункт «Формат ячеек». Альтернативный способ — воспользуйтесь сочетанием клавиш Ctrl + 1 для быстрого вызова этого окна.
4. 
5. В открывшемся диалоговом окне перейдите на вкладку «Число», если она еще не активна. В списке «Числовые форматы» найдите и выберите категорию «Дата».
6. 
7. Справа отобразится список доступных форматов отображения даты. Здесь представлены как короткие варианты вроде «14.03.2012», так и развернутые — «14 марта 2012 г.». Интересно, что Excel автоматически подстраивает некоторые форматы под региональные настройки Windows, поэтому у пользователей из разных стран набор форматов может немного отличаться.
8. 
9. Выберите подходящий формат из списка «Тип» и нажмите «OK». Числовые значения мгновенно преобразуются в читаемые даты, при этом исходное значение в ячейке остается числом — меняется только способ его отображения.
10. 
11. Если вам нужен формат, которого нет в стандартном списке, переключитесь на категорию «Все форматы» или «Пользовательский». Там вы можете создать собственный формат, используя коды: дд для дня, мм для месяца, гггг для года. Например, формат дд.мм.гггг отобразит дату как 15.08.2024.
12. 

Способ 2: Функции ДАТАЗНАЧ для текстовых дат

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

1. Предположим, в столбце A у вас находятся текстовые даты в различных форматах. Выделите ячейку в соседнем столбце, где должен появиться результат преобразования.
2. 
3. Введите формулу =ДАТАЗНАЧ(A1), где A1 — ячейка с текстовой датой. После нажатия Enter вы увидите результат в числовом формате, который затем можно отформатировать как дату по инструкции из первого способа.
4. 
5. Обратите внимание на важную особенность: если текстовая дата записана в формате, который Excel не может распознать автоматически, функция вернет ошибку #ЗНАЧ!. В таких случаях потребуется предварительно обработать текст функциями ЛЕВСИМВ, ПРАВСИМВ, ПСТР или другими текстовыми функциями.
6. 
7. Для массового преобразования скопируйте формулу вниз по столбцу, потянув за маркер автозаполнения в правом нижнем углу ячейки. После получения результатов можете скопировать их и вставить как значения (Ctrl + Alt + V — «Значения») в исходный столбец, удалив затем вспомогательный.
8. 

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

Способ 3: Создание даты из отдельных компонентов функцией ДАТА

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

1. Рассмотрим ситуацию, когда в столбце A находится год, в столбце B — месяц (числом от 1 до 12), а в столбце C — день месяца. Выделите ячейку, где хотите получить объединенную дату.
2. 
3. Введите формулу =ДАТА(A1;B1;C1), где первый аргумент — год, второй — месяц, третий — день. Порядок аргументов строго фиксирован и не зависит от региональных настроек.
4. 
5. После применения формулы Excel автоматически создаст корректное значение даты. Любопытная деталь: функция ДАТА умеет корректировать некорректные значения. Например, если указать 32 день января, Excel автоматически преобразует это в 1 февраля.
6. 
7. Вы можете использовать в качестве аргументов не только ссылки на ячейки, но и математические выражения. Формула =ДАТА(2024;3;1-1) вернет последний день февраля 2024 года, что удобно для расчета последних дней месяцев.
8. 

Способ 4: Преобразование числовых значений времени Unix в дату

С 1 января 1970 года формат времени Unix — количество секунд — часто используется при интеграции с веб-сервисами и API. Похожие цифры могут показаться пугающими: 1725000000 вместо понятной даты. Чтобы перевести их в читаемый формат, можно использовать простую формулу, учитывая различия между системами отсчета Excel и Unix.

1. Предположим, в ячейке A1 находится временная метка Unix, например 1725000000. Выделите ячейку для результата.
2. 
3. Введите формулу =A1/86400+ДАТА(1970;1;1). Число 86400 — это количество секунд в сутках (60 секунд × 60 минут × 24 часа), а ДАТА(1970;1;1) — точка отсчета Unix-времени.
4. 
5. Полученное значение отобразится как обычное число. Примените к ячейке формат даты и времени через «Формат ячеек», выбрав категорию «Дата» или создав пользовательский формат типа дд.мм.гггг чч:мм:сс.
6. 
7. Если временная метка указана в миллисекундах (что характерно для JavaScript), сначала разделите значение на 1000: =A1/1000/86400+ДАТА(1970;1;1). Определить формат просто — временные метки в миллисекундах содержат 13 цифр, в секундах — 10.
8. Важный нюанс: формула не учитывает часовые пояса. Если данные пришли в UTC, а вам нужно локальное время, добавьте смещение часового пояса делением на 24. Для Москвы (UTC+3) это будет выглядеть так: =A1/86400+ДАТА(1970;1;1)+3/24.
9. 

Способ 5: Работа с датами в нестандартном формате через текстовые функции

Иногда даты записаны в формате, который Excel просто не может распознать автоматически. Например, «2024-08-15» или «20240815» без разделителей — это примеры формата данных, который Excel не может распознать автоматически. В таких ситуациях необходимо использовать текстовые функции для ручного извлечения компонентов даты, а затем собирать их в правильное значение. Хотя эта процедура кажется сложной, она дает полный контроль над преобразованием.

1. Допустим, в ячейке A1 записана дата в формате «20240815» (год-месяц-день без разделителей). Выделите другую свободную ячейку для формулы преобразования, куда будет выводиться результат, и переходите далее.
2. 
3. Используйте комбинацию функций для извлечения частей: =ДАТА(ЛЕВСИМВ(A1;4);ПСТР(A1;5;2);ПРАВСИМВ(A1;2)). Функция ЛЕВСИМВ берет первые 4 символа (год), ПСТР извлекает 2 символа начиная с 5-й позиции (месяц), ПРАВСИМВ забирает последние 2 символа (день).
4. 
5. Для формата с разделителями, например «15-08-2024», примените функцию разбиения текста. Можете воспользоваться инструментом «Текст по столбцам» на вкладке «Данные», указав дефис как разделитель, а затем собрать части функцией ДАТА.
6. 
7. Альтернативный вариант для «15-08-2024» — комбинация ЗНАЧЕН и функций поиска: =ДАТА(ЗНАЧЕН(ПРАВСИМВ(A1;4));ЗНАЧЕН(ПСТР(A1;4;2));ЗНАЧЕН(ЛЕВСИМВ(A1;2))). ЗНАЧЕН преобразует текстовые числа в числовой формат.
8. 
9. Если в таблице встречаются разные форматы дат вперемешку, создайте проверочную формулу с использованием функции ЕСЛИ, которая определит формат и применит соответствующий метод преобразования. Это трудозатратно, но позволяет автоматизировать обработку смешанных данных.

Способ 6: Инструмент «Текст по столбцам»

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

1. Выделите диапазон ячеек с текстовыми датами, которые необходимо преобразовать. Перейдите на вкладку «Данные» и найдите кнопку «Текст по столбцам» в группе «Работа с данными».
2. 
3. В открывшемся мастере на первом шаге выберите «С разделителями», если даты содержат точки, дефисы или слэши в качестве разделителей. Для дат без разделителей подойдет вариант «Фиксированной ширины». Нажмите «Далее».
4. 
5. На втором шаге укажите символ-разделитель. Для большинства дат это будет точка или дефис. Снимите остальные галочки, оставив только нужную. В окне предварительного просмотра вы увидите, как Excel разобьет данные.
6. 
7. Третий шаг — самый важный. В разделе «Формат данных столбца» выберите «Дата» и укажите соответствующий формат из выпадающего списка: ДМГ для «день-месяц-год», МДГ для «месяц-день-год» или ГМД для «год-месяц-день». Excel покажет, как будет выглядеть результат.
8. 
9. Обратите внимание на поле «Поместить в» — здесь указывается ячейка, куда будет помещен результат. По умолчанию данные заменят исходные, но вы можете указать другой диапазон для сохранения оригинала. После нажатия «Готово» все текстовые даты преобразуются в полноценные значения.
10. 
11. Инструмент запоминает последние настройки, поэтому при повторном использовании можно просто нажимать «Далее» — «Далее» — «Готово» без изменения параметров. После разделения вы можете использовать формат преобразования, как это было показано ранее.
12. 

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

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

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