Мастер работы с датами и временем в Excel

Чтобы корректно работать с датами в Excel, нужно помнить главное правило: для программы дата — это порядковый номер дня, а время — дробная часть суток. Например, число 1 соответствует 1 января 1900 года, а 0,5 — это 12:00 дня. Понимание этой механики позволит вам легко складывать часы, считать возраст и находить разницу между датами без ошибок.

В этом руководстве мы разберем актуальные формулы для версий Excel 2016–365, которые помогут автоматизировать рутинные расчеты.

Текущая дата и время

Для вставки актуальной даты или момента времени используются две динамические функции. Они обновляются при каждом пересчете листа (например, при открытии файла или внесении изменений).

Если вам нужно зафиксировать дату статично (чтобы она не менялась завтра), используйте горячие клавиши:

• Ctrl + ; (точка с запятой) — вставляет текущую дату.
• Ctrl + Shift + ; — вставляет текущее время.

Точный расчет возраста

Самая частая задача — вычислить полный возраст человека на текущий момент. Простое вычитание годов (=ГОД(СЕГОДНЯ())-ГОД(A1)) часто дает ошибку в 1 год, если день рождения еще не наступил в текущем году.

Способ 1: Универсальная формула (без скрытых функций)

Эта формула учитывает, прошел ли день рождения в этом году:

=ГОД(СЕГОДНЯ()) - ГОД(A1) - ЕСЛИ(ДАТА(ГОД(СЕГОДНЯ()); МЕСЯЦ(A1); ДЕНЬ(A1)) > СЕГОДНЯ(); 1; 0)

Где A1 — ячейка с датой рождения.

Способ 2: Функция ДАТАРАЗН (скрытая, но мощная)

Функция ДАТАРАЗН (в английской версии DATEDIF) специально создана для таких расчетов. Она не отображается в подсказках автозаполнения, но работает во всех версиях Excel.

Синтаксис: =ДАТАРАЗН(начальная_дата; конечная_дата; "единица_измерения")

Примеры использования для даты рождения в ячейке A1:

• Полных лет: =ДАТАРАЗН(A1; СЕГОДНЯ(); "y")
• Полных месяцев (после последнего дня рождения): =ДАТАРАЗН(A1; СЕГОДНЯ(); "ym")
• Дней (после последнего месяца рождения): =ДАТАРАЗН(A1; СЕГОДНЯ(); "md")

Чтобы получить красивую строку «40 лет 5 мес.», объедините их:

=ДАТАРАЗН(A1; СЕГОДНЯ(); "y") & " лет " & ДАТАРАЗН(A1; СЕГОДНЯ(); "ym") & " мес."

Суммирование часов и работа со временем

Excel хранит время как долю от 24 часов. Это создает проблему при суммировании: если сумма превышает 24 часа, стандартный формат времени сбросит счетчик до нуля (например, 25:00 отобразится как 01:00).

Как правильно суммировать часы

1. Введите значения времени в ячейки (например, 8:00, 9:30, 10:15).
2. Используйте обычную сумму: =СУММ(A1:A3).
3. Критически важный шаг: Измените формат ячейки с результатом.
   • Нажмите Ctrl + 1 → вкладка Число → (все форматы).
   • В поле «Тип» введите: [ч]:мм (квадратные скобки обязательны).

Теперь ячейка покажет корректную сумму, например 27:45, а не 3:45.

Перевод времени в десятичные часы

Иногда для табеля учета нужно получить число (например, 1 час 30 мин = 1,5 часа). Формула: =A1 * 24 Не забудьте отформатировать результат как Числовой с нужным количеством знаков после запятой.

Разница между датами

Вычисление количества дней между двумя событиями можно выполнить двумя способами.

Простое вычитание

Формула: =B1 - A1 Результатом будет количество дней в виде числа.

• Если A1 = 01.01.2026, а B1 = 10.01.2026, результат = 9.
• Для расчета рабочих дней (без выходных) используйте: =СЕТЬРАБОЧДНЕЙ(A1; B1).

Использование ДАТАРАЗН для сложных интервалов

Функция ДАТАРАЗН позволяет гибко выбирать единицы измерения:

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

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

1. Формула возвращает #ЗНАЧ!

   • Причина: Одна из ячеек содержит текст, а не дату. Часто бывает при копировании данных из веб-сайтов или 1С.
   • Решение: Выделите столбец → Данные → Текст по столбцам → Далее → Далее → Выберите формат «Дата» (ДМГ) → Готово.

2. Отрицательное время (##### или ошибка)

   • Причина: Вы вычитаете большее время из меньшего (например, конец смены раньше начала).
   • Решение: Если это переход через полночь (смена с 22:00 до 06:00), используйте формулу: =ЕСЛИ(B1<A1; B1+1; B1) - A1. Либо включите отображение отрицательных дат в настройках Excel (Файл → Параметры → Дополнительно → раздел «При вычислении этой книги»).

3. Сдвиг даты на 1 день

   • Причина: Разница в часовых поясах или неверная трактовка системной даты.
   • Решение: Проверьте настройки региона в Панели управления Windows. Убедитесь, что разделитель даты в вашей системе совпадает с тем, что вы используете в формулах (точка или слэш).

FAQ

Как вставить дату, которая не будет меняться завтра? Не используйте формулу =СЕГОДНЯ(). Нажмите сочетание клавиш Ctrl + ; (точка с запятой). Дата вставится как статическое значение.

Можно ли сложить минуты и секунды отдельно? Да. Если у вас есть столбец минут и столбец секунд, приведите их к единому виду времени перед суммированием или используйте формулу: =ВРЕМЯ(0; СУММ(минуты); СУММ(секунды)).

Почему функция ДАТАРАЗН не появляется в подсказках? Это историческая особенность Excel. Функция скрыта из меню автозаполнения ради совместимости со старыми версиями Lotus 1-2-3, но она полностью рабочая. Просто впишите её название вручную.

Как выделить выходные дни цветом? Используйте условное форматирование. Выделите диапазон дат → Главная → Условное форматирование → Создать правило → «Использовать формулу». Введите: =ИЛИ(ДЕНЬНЕД(A1;2)=6; ДЕНЬНЕД(A1;2)=7). Выберите красный цвет заливки.