Это подробное руководство посвящено работе с форматом даты в Excel. Здесь вы узнаете, как задать стандартное отображение даты и времени, изменить их форматирование и создать собственное оформление. Рассмотрим, как может выглядеть дата в текстовом или числовом формате.
Помимо числовых значений, одними из самых часто используемых типов данных в Excel являются дата и время. Но работа с ними может быть не всегда простой, так как:
- Одна и та же дата может выводиться в разных форматах,
- Excel всегда сохраняет дату в числовом виде, независимо от выбранного способа ее отображения.
Глубокое понимание особенностей формата даты в Excel поможет значительно сэкономить время. Именно этому и посвящено наше руководство, в котором мы рассмотрим следующие моменты:
- Формат даты в Excel
- Формат даты по умолчанию в Excel и его изменение
- Быстрое применение форматирования даты и времени по умолчанию
- Изменение формата даты в Excel
- Изменение языка даты
- Создание пользовательского формата даты
- Создание собственного формата для другого языка
- Дата в текстовом и числовом формате в Excel
- Решение проблем с форматом даты в Excel
- Часто задаваемые вопросы
Формат даты в Excel
Прежде всего важно понять, как Microsoft Excel хранит дату и время, что часто становится причиной путаницы. Вопреки ожиданиям, Excel не запоминает день, месяц и год отдельно – он сохраняет даты как последовательные числа, а формат ячейки определяет, как число будет отображаться: как дата, как время или их сочетание.
Все даты представляются целыми числами, отражающими количество дней, прошедших с 1 января 1900 года (это число равно 1) до 31 декабря 9999 года (число 2958465).
Форматирование ячеек преобразует эти числа в читаемый вид.
Пример: Число 44197 отображается как 1 января 2021 года при форматировании как дата.
Время сохраняется в виде десятичных дробей от 0,0 до 0,99999. Эти дроби представляют долю дня, где:
- 0,0 означает 00:00:00
- 0,99999 – 23:59:59
- 0.25 соответствует 06:00,
Когда дата и время хранятся вместе, они сохраняются как число с целой частью (дата) и дробной частью (время). Например, значение 44197.5 соответствует 1 января 2021 года в 12:00.
Формат даты по умолчанию в Excel и его изменение
Краткий и длинный форматы даты, которые установлены в Excel по умолчанию, задаются региональными настройками Windows.
Стандартные форматы даты в Excel зависят от региональных настроек Windows. Например:
- Американский формат: MM/DD/YYYY (например, 04/30/2025).
- Европейский формат: DD/MM/YYYY (например, 30/04/2025).
Изменения в настройках даты и времени в панели управления автоматически обновляют эти форматы. Если вы хотите изменить формат отображения даты с американского на тот, который принят в вашей стране, перейдите в Панель управления → «Региональные стандарты» → «Изменение форматов даты, времени и чисел».
На вкладке «Форматы» выберите нужный регион, а затем измените формат, щелкнув по стрелке рядом с необходимым пунктом и выбрав нужный вариант из списка. Если стандартные варианты вас не устраивают, нажмите «Дополнительные параметры». В появившемся окне перейдите на вкладку «Дата», введите желаемый краткий или длинный формат в соответствующее поле и нажмите ОК для сохранения.
Используйте коды, такие как «м» для номера месяца или «дддд» для полного названия дня недели.
Пример: Формат «д мммм гггг» покажет дату как «30 апрель 2025».
Быстрое применение форматирования даты и времени по умолчанию
В Excel существует два базовых формата отображения даты и времени – короткий и длинный. Чтобы быстро их изменить, выполните следующие шаги:
- Выделите даты, которые нужно отформатировать.
- На вкладке «Главная» в группе «Число» нажмите маленькую стрелку рядом с полем «Формат числа» и выберите нужный тип – краткую дату, длинную дату или время.
- Для дополнительных настроек выберите «Другие числовые форматы» или нажмите кнопку диалогового окна «Формат ячеек», где вы сможете задать любые параметры форматирования.
Изменение формата даты в Excel
Чтобы поменять вид даты для отдельной ячейки или диапазона:
- Выделите нужные данные или пустые ячейки для ввода дат.
- Нажмите Ctrl + 1, чтобы открыть «Формат ячеек», или щелкните правой кнопкой мыши и выберите соответствующую опцию в контекстном меню.
- Перейдите на вкладку «Число» и выберите категорию «Дата».
- Из списка «Тип» выберите наиболее подходящий вариант, после чего в поле «Образец» отобразится предварительный просмотр.
Если отображение даты не меняется, возможно, данные в ячейке записаны в виде текста. В таком случае их сначала необходимо преобразовать в формат даты. Подробнее об этом читайте здесь: Как преобразовать текст в дату в Excel
Изменение языка даты
Если в вашем файле использованы иностранные форматы дат, например, американский (месяц/день/год) вместо привычного европейского (день/месяц/год), выполните следующие шаги:
- Выберите столбец с датами.
- Нажмите Ctrl + 1 чтобы открыть настройки форматирования.
- В выпадающем списке «Язык (местоположение)» выберите нужный вариант и нажмите ОК.
Создание пользовательского формата даты
Если ни один из стандартных вариантов не удовлетворяет ваши требования, можно создать собственный формат:
- Выделите нужные ячейки.
- Нажмите Ctrl + 1.
- На вкладке «Число» выберите категорию «Все форматы» и в поле «Тип» введите желаемый формат.
- Нажмите ОК для сохранения изменений.
Совет: Начните с выбора стандартного формата даты, близкого к тому, что вам нужно, затем измените его, используя поле «Тип» на вкладке «Все форматы».
При настройке формата даты можно использовать следующие коды:
| Код | Описание | Пример |
| м | Номер месяца без ведущего нуля | 1 |
| мм | Номер месяца с ведущим нулем | 01 |
| ммм | Краткое название месяца | Янв |
| мммм | Полное название месяца | Январь |
| ммммм | Первая буква месяца | М (март, май) |
| д | День месяца без ведущего нуля | 1 |
| дд | День месяца с ведущим нулем | 01 |
| ддд | Краткое название дня недели | Пн |
| дддд | Полное название дня недели | понедельник |
| гг | Год (последние 2 цифры) | 05 |
| гггг | Год (4 цифры) | 2025 |
Также можно использовать дополнительные коды, заключая их в квадратные скобки:
| Код | Пояснение |
| x-sysdate | Системный длинный формат с месяцем в родительном падеже |
| x-systime | Системное время |
| x-genlower | Родительный падеж в нижнем регистре (только для русского) |
| x-genupper | Родительный падеж в верхнем регистре (только для русского) |
| x-nomlower | Именительный падеж в нижнем регистре (только для русского) |
При создании пользовательского формата можно использовать запятые, тире, наклонную черту, двоеточие и другие символы.
Создание собственного формата для другого языка
Если требуется отображать даты на другом языке, не изменяя региональные настройки Windows, нужно создать собственный формат с использованием специального префикса. Код языка заключается в квадратные скобки и начинается с знака доллара и тире. Примеры:
- [$-419] – Россия,
- [$-409] – английский (США),
- [$-422] – Украина,
- [$-423] – Беларусь,
- [$-407] – Германия.
Дата в текстовом и числовом формате в Excel
Чтобы узнать, какое число соответствует дате или времени, можно использовать два способа:
Способ 1: Диалоговое окно «Формат ячеек»
- Выделите ячейку, нажмите Ctrl + 1 и перейдите на вкладку «Общие». Если вы хотите просто узнать число, посмотрите значение в поле «Образец» и нажмите «Отмена». Если нужно заменить дату числом, нажмите «ОК».
- Также можно выбрать формат «Общий» на ленте, чтобы преобразовать дату в число.
Способ 2: Функции ДАТАЗНАЧ и ВРЕМЗНАЧ
- Дата в текстовом формате в Excel может быть преобразована в число. Для этого используйте формулу
=ДАТАЗНАЧ(«20/1/2021») - Для времени:
=ВРЕМЗНАЧ(«16:30») - Для комбинации даты и времени:
=ДАТАЗНАЧ(«20/1/2021») & ВРЕМЗНАЧ(«16:30»)
Вернется дата и время в текстовом формате. А если применить операцию сложения:
=ДАТАЗНАЧ(«20/1/2021») + ВРЕМЗНАЧ(«16:30»)
то получится число, которое можно отформатировать как дату-время и использовать для математических вычислений (например, для нахождения разницы дат). Подробнее об этом читайте здесь: Разность дат и количество дней между датами в Excel.
Примечание:
Так как отсчет дат в Excel начинается с 1 января 1900 года, отрицательные числа не поддерживаются. Даты до 1900 года, например 31.12.1812, будут восприниматься как текст. Это не позволяет проводить с ними арифметические операции, так как для этого в Excel нужна дата в числовом формате. Попробуйте ввести формулу =ДАТАЗНАЧ(«31/12/1812») – вы получите ошибку #ЗНАЧ!.
Решение проблем с форматом даты в Excel
Несмотря на то, что Excel обычно корректно работает с датами, иногда могут возникать проблемы:
1. Ячейка слишком узкая
Если вместо даты вы видите символы «#####», скорее всего, ячейка недостаточно широка.
Решение: Дважды щелкните правую границу столбца или вручную измените её размер.
2. Отрицательные значения форматируются как даты
При отрицательных результатах (например, если вычесть большее значение из меньшего) Excel может отображать «#####».
Вариант решения 1: Переключитесь на систему дат 1904. Для этого перейдите в: Файл → Параметры → Дополнительно, прокрутите вниз до раздела «Вычисления». Там установите флажок «Использовать систему дат 1904» и нажмите ОК.
Вариант решения 2: Используйте функцию ТЕКСТ для форматирования отрицательной даты, например:
=ТЕКСТ(ABS(B1-C1);»-ДД ММ ГГГГ»)
Учтите, что функция ТЕКСТ возвращает текстовое значение, и такие данные не подходят для дальнейших расчетов.
3. Даты импортированы как текст
При импорте данных из CSV или других источников даты могут оказаться в виде текстовых значений.
Решение: дата в текстовом формате может быть преобразована в формат даты в Excel с помощью функции ДАТАЗНАЧ или инструмента «Текст по столбцам».
Часто задаваемые вопросы
Таким образом, после изучения данного руководства вы научитесь работать с форматом даты в Excel: от понимания внутреннего представления дат до создания пользовательских форматов и устранения распространенных проблем.
Надеемся, что представленные примеры и советы помогут вам эффективно настраивать формат даты в Excel и ускорят вашу работу с временными данными!