Excel хранит значения даты и времени в виде серийных номеров «за кулисами». Это значит, что хотя вы можете видеть в ячейке дату вроде «10 января 2022» или «01.10.2020», внутри Excel хранит это как число. Это удобно, поскольку позволяет легко складывать и вычитать даты и время.
В Microsoft Excel для Windows «01 января 1900» хранится как 1, «02 января 1900» — как 2, и так далее. Один и тот же серийный номер может отображаться либо как число, либо как соответствующая ему дата — в зависимости от формата ячейки.
Это также причина того, почему иногда в ячейке вместо ожидаемой даты появляется число — например, при загрузке данных из баз данных или веб-инструментов. Это происходит, когда формат ячейки настроен на отображение числа как числа, а не как даты.
Как же преобразовать эти серийные номера в даты? Об этом — данное руководство. Я покажу два простых способа.
Способ 1: Форматирование чисел
Самый простой способ преобразовать серийный номер в дату — изменить форматирование ячеек с этими числами.
Через встроенные форматы даты на ленте
Выделите ячейки с числами, которые нужно преобразовать в дату
Перейдите на вкладку «Главная»
В группе «Число» откройте раскрывающееся меню форматирования
Выберите «Длинный формат даты» или «Краткий формат даты» — в зависимости от того, что нужно
Готово — числа преобразуются в выбранный формат даты. Это не меняет значение в ячейке, только способ его отображения.
У этого быстрого метода есть ограничения:
- Доступно только два формата — краткая и длинная дата (в фиксированном виде). Если нужен, например, только месяц и год без дня — так сделать не получится
- Нельзя одновременно отобразить и дату, и время через этот выпадающий список
Если нужна большая гибкость — используйте пользовательский формат (ниже).
Пользовательский формат даты через диалоговое окно
Если нужного формата нет в списке (например, дата вида 01.01.2020), можно создать собственный:
Выделите ячейки с числами
Перейдите на вкладку «Главная»
В группе «Число» нажмите значок запуска диалогового окна (маленькая стрелка в правом нижнем углу группы)
На вкладке «Число» в списке «Категория» выберите «Дата»
Выберите нужный формат в поле «Тип»
Нажмите ОК
Здесь вариантов формата гораздо больше, чем в кратком списке на ленте. А если подходящего формата всё равно нет — можно создать полностью свой:
Выделите ячейки с числами
Перейдите на вкладку «Главная»
В группе «Число» откройте диалоговое окно «Формат ячеек»
В категории выберите «(все форматы)» / «Пользовательский»
В поле справа введите, например, мм/дд/гггг
Нажмите ОК
С пользовательским форматом вы получаете полный контроль над отображением — можно даже показать дату и время в одной и той же ячейке, например: мм/дд/гггг чч:мм.
Таблица основных кодов форматирования даты:
| Код | Как форматируется |
|---|---|
| м | Месяц как число от 1 до 12 |
| мм | Месяц двумя цифрами, от 01 до 12 |
| ммм | Месяц как трёхбуквенное сокращение (янв–дек) |
| мммм | Полное название месяца (январь–декабрь) |
| ммммм | Первая буква названия месяца (Я–Д) |
| д | День как число от 1 до 31 |
| дд | День двумя цифрами, от 01 до 31 |
| ддд | День недели сокращённо (пн–вс) |
| дддд | День недели полностью (понедельник–воскресенье) |
| гг | Год двумя цифрами (00–99) |
| гггг | Год четырьмя цифрами (1900–9999) |
Способ 2: Функция ТЕКСТ
Способы выше работают через изменение форматирования ячеек. Но иногда это не сработает — например, если у чисел стоит начальный апостроф, который превращает их в текст.
В таком случае встроенные параметры форматирования ничего не изменят, потому что Excel не считает эти значения числами. Здесь можно либо сначала преобразовать текст обратно в числа, либо использовать функцию ТЕКСТ.
Функция ТЕКСТ принимает два аргумента — число для форматирования и код формата:
=ТЕКСТ(A2;"ММ/ДД/ГГГГ")
Если нужен другой формат — просто укажите соответствующий код вторым аргументом. Эту же формулу можно использовать для отображения даты и времени вместе:
=ТЕКСТ(A2;"мм/дд/гггг чч:мм")
Если нужна только сама дата (а не формула) — преобразуйте результат формулы в статичное значение через специальную вставку («Значения»).
Итак, это два простых способа преобразования серийных номеров в даты в Microsoft Excel.
Разберитесь с датами в Excel раз и навсегда
Серийные номера, форматы и функция ТЕКСТ на понятных примерах