Работа с датами в Excel: от числа к году и месяцу

Чтобы преобразовать дату в число в Excel, измените формат ячейки на «Числовой» или умножьте её на 1. Для получения года используйте функцию =YEAR(ячейка), для месяца — =MONTH(ячейка). Понимание того, что дата внутри Excel хранится как порядковый номер дня, позволяет легко выполнять вычисления, сортировку и группировку данных.

Почему дата в Excel — это число

В основе работы с датами лежит простая концепция: для Excel дата — это целое число, представляющее количество дней, прошедших с 1 января 1900 года.

• 1 соответствует 01.01.1900.
• 45380 соответствует примерно середине 2024 года.

Время (часы, минуты) хранится как дробная часть числа (например, 0.5 — это полдень, 12:00).

Благодаря этому вы можете:

• Вычитать одну дату из другой, получая количество дней.
• Складывать даты с числами (например, прибавить 7 дней к сроку).
• Использовать даты в логических функциях (ЕСЛИ, СУММЕСЛИ).

Способы преобразования даты в число

Иногда требуется получить именно серийный номер даты (например, для уникального идентификатора или совместимости с другими системами).

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

Самый быстрый метод для визуального изменения:

1. Выделите ячейку или диапазон с датами.
2. Нажмите Ctrl + 1 (или правой кнопкой мыши → Формат ячеек).
3. На вкладке Число выберите категорию Числовой.
4. Уберите десятичные знаки, если нужно целое число.

Способ 2: Математические операции

Если нужно получить число в соседней ячейке с помощью формулы:

• =A1*1 — умножение на единицу принудительно переводит формат в числовой.
• =--A1 — двойное отрицание. Первый минус превращает в отрицательное число, второй возвращает положительное. Это стандартный трюк для конвертации текстовых чисел и дат.

Способ 3: Функция ЗНАЧЕН (VALUE)

Используется, если дата записана как текст (выровнена по левому краю):

• =ЗНАЧЕН(A1) или =VALUE(A1)

Извлечение компонентов даты: Год, Месяц, День

Для аналитики часто требуется разбить полную дату на составные части. В Excel для этого есть три базовые функции.

Получение года

Функция ГОД (или YEAR) возвращает четырехзначное число года.

• Формула: =ГОД(A1)
• Пример: Для даты 15.03.2024 результат будет 2024.

Получение месяца

Функция МЕСЯЦ (или MONTH) возвращает номер месяца от 1 (январь) до 12 (декабрь).

• Формула: =МЕСЯЦ(A1)
• Пример: Для даты 15.03.2024 результат будет 3.

Если вам нужно название месяца текстом, используйте функцию ТЕКСТ:

• =ТЕКСТ(A1; "мммм") → «март»
• =ТЕКСТ(A1; "ммм") → «мар»

Получение дня

Функция ДЕНЬ (или DAY) возвращает номер дня в месяце.

• Формула: =ДЕНЬ(A1)
• Пример: Для даты 15.03.2024 результат будет 15.

Практическое применение: Группировка и отчеты

Извлечение года и месяца критически важно для создания сводных таблиц и отчетов.

1. Группировка продаж по месяцам. Добавьте вспомогательный столбец с формулой =МЕСЯЦ(Дата). Затем постройте сводную таблицу, перетащив этот столбец в строки, а сумму продаж — в значения.
2. Фильтрация по годам. Столбец с =ГОД(Дата) позволяет быстро отфильтровать данные за конкретный период, не используя сложные фильтры по диапазону дат.
3. Расчет возраста или стажа. Комбинируя функции, можно вычислять полные годы между датами: =РАЗНДАТ(A1; B1; "y") — более точный способ, чем простое вычитание годов.

Сравнение функций извлечения данных

Частые ошибки и их решение

При работе с датами пользователи часто сталкиваются с типовыми проблемами.

• Даты выровнены по левому краю. Это признак того, что Excel воспринимает их как текст. Формулы ГОД или МЕСЯЦ вернут ошибку. Решение: Используйте «Текст по столбцам» (вкладка Данные), чтобы перезаписать формат, или формулу =--A1.

• Вместо даты отображаются решетки (#####). Ячейка слишком узка для отображения даты. Решение: Расширьте столбец или уменьшите шрифт.

• Неверный месяц при импорте. Часто возникает при переносе данных из американских источников (где формат Месяц/День/Год). Дата 02.03.2024 может быть прочитана как 2 марта или 3 февраля. Решение: При импорте явно укажите формат данных или используйте функцию ДАТА(год; месяц; день) для сборки даты вручную из текстовых частей.

FAQ

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

Можно ли сложить год и месяц в одну дату? Да, с помощью функции ДАТА. Например, =ДАТА(2024; 5; 1) создаст дату 1 мая 2024 года. Эта функция умна: если указать месяц 13, она автоматически перенесет дату на январь следующего года.

Почему при копировании даты в Блокнот получается число? Потому что Блокнот не поддерживает форматы Excel и показывает «сырое» значение — серийный номер дня. Чтобы скопировать именно вид даты, используйте специальную вставку («Текст») или предварительно примените формат Текстовый к ячейкам.