Работа с датой и временем в Excel

Вычленение даты и времени из ячейки

Часто пользователи Excel сталкиваются с необходимостью достать из ячейки формата «Дата» или «Время» по отдельности: Год, Месяц, Номер недели, День месяца, Час, Минуту. Такая задача решается с помощью функций.

Допустим в ячейке В2 у нас хранится дата 4 июня 2019 г. Получим из нее Год в другой ячейке D2:

1. Поставить курсор в ячейку D2.
2. Кликнуть на значке формулы .
3. В появившемся окне «Вставка функции» в поле «Категория» выбрать Дата и время, а в поле «Выберите функцию» выделить ГОД. Нажать [OK].
4. В появившемся окне «Аргументы функции» поместить курсор в поле «Дата_в_числовом_формате» и кликнуть мышкой по ячейке В2, в результате чего адрес ячейки будет вбит в это поле. Нажать [OK].

Теперь в ячейке D2 будет храниться значение 2019. При изменении года в дате в ячейке В2 автоматически изменится год в ячейке D2.
Аналогичным образом поступать в случаях, когда из ячейки B2 необходимо вычленить месяц, номер недели с начала года или день месяца. Соответственно в п.3 надо будет выбирать функцию не ГОД, а МЕСЯЦ, НОМНЕДЕЛИ или ДЕНЬ.

Если в ячейке хранится также время или только время, то для выноса часов или минут в отдельную ячейку в п.3 надо выбирать ЧАС или МИНУТЫ.

Вычисление разницы между датами

Для такой задачи существует математическая операция «–» (минус), а также функция РАЗНДАТ (в английской версии - DATEDIF).

С операцией вычитания никаких подвохов нет (разве что, знак минус или плюс надо обрамлять пробелами). Разница между двумя датами вычисляется в днях. Если в дате указано время, то в дробных днях. Нет ошибки, если из меньшей даты вычесть бОльшую – тогда результат будет со знаком минус:

А вот в применении функции РАЗНДАТ есть нюансы.
Первая загвоздка в том, что эту функцию вы не найдете в Мастере функций после нажатия на кнопку . Но ее возможно вписать в поле значения ячейки вручную. Синтаксис функции:

=РАЗНДАТ(Начальная_дата; Конечная_дата; Единица_измерения)

Начальная_дата – это меньшая дата;
Конечная_дата – это бОльшая дата;
Единица_измерения – этот аргумент определяет в каких временных единицах выводить разницу дат:

"y"   разница в полных годах;
"m"   в полных месяцах;
"d"   в полных днях;
"yd"  разница в днях с начала года без учета лет;
"md"  разница в днях без учета месяцев и лет;
"ym"  разница в полных месяцах без учета лет.

Вторая загвоздка в том, что надо строго придерживаться последовательности ввода дат – сначала ввести меньшую дату (ссылку на ячейку с датой), потом – бОльшую. Иначе Excel выдаст в ячейке ошибку. Примеры использования функции:

Вычисление разницы во времени

Для такой задачи существует математическая операция «–» (минус), а также функции ЧАС, МИНУТЫ, СЕКУНДЫ, применяемые к разнице значений ячеек.

При простом вычитании следует придерживаться той аксиомы, что время нельзя повернуть назад, поэтому надо вычитать из большего меньшее, иначе – ошибка.

При вычислении разницы в часах, минутах или секундах следует иметь в виду, что результат в часах не может превышать 24 часа, а результат в минутах или секундах не может превышать 60. Потому что эти вычисления происходят без учета временных периодов более высокого порядка.

Практические примеры

1. Проверка превышения запланированного времени доставки груза

На практике иногда требуется узнать была ли разница в часах больше некоторой запланированной величины. Допустим есть таблица, в которой выводятся времена Загрузки и Разгрузки по завершенным заказам, а также нормативное время, запланированное между Загрузкой и Разгрузкой. Требуется в отдельном столбце вывести индикацию того, уложился ли водитель в нормативное время. Для этого:

Создадим столбец E, в который будем выводить разницу между Разгрузкой и Загрузкой в формате времени в ячейке E2:

=C2-B2

В ячейке F2 напишем формулу, в которой будет задействована функция ЕСЛИ:

=ЕСЛИ(E2>D2;"Опоздание";"Норма")

В формуле мы видим такую логику:

• Берется фактическая разница между Разгрузкой и Загрузкой;
• Эта Разница сравнивается с Нормой;
• Если Разница больше Нормы, то пишется «Опоздание»;
• Если Разница меньше или равна Норме, то пишется «Норма».

Если написать вышеприведенную функцию ЕСЛИ сначала только в F2, а потом протянуть ячейку вниз по столбцу за плюсик в нижнем правом углу:

то формула в каждой последующей ячейке автоматически будет интеллектуально преобразована в формулу с релевантными строке адресами ячеек. Аналогичным образов поступаем с ячейкой E2 – размножает ее по столбцу E.

2. Красивое представление даты и времени в одной ячейке

Допустим, у нас есть список дат тех же Загрузок и Разгрузок при дальних перевозках, когда машина в рейсе находится не один день. Требуется представить время между Загрузкой и Разгрузкой в формате дней, часов и минут в одной ячейке.

В ячейке E9 пишем красивую формулу:

=СЦЕПИТЬ(ТЕКСТ(C9-B9;"дд"); " дн. "; ТЕКСТ(C9-B9;"чч"); " час. "; ТЕКСТ(C9-B9;"мм"); " мин.")

Здесь функция ТЕКСТ переводит дату в число дней, или часов, или минут. Во что преобразовывать, зависит от второго параметра функции – «дд», «чч» и «мм» соответственно. А функция СЦЕПИТЬ склеивает наши числа и тексты в одну строку.
После размножения формулы по столбцу E получаем красивый результат:

Маленькие хитрости

Чтобы быстро внести в ячейку текущую дату надо воспользоваться сочетанием горячих клавиш Ctrl+Ж.

Чтобы внеси в ячейку номер дня недели надо воспользоваться функцией ДЕНЬНЕД, для которой есть дополнительный параметр «Тип» – он влияет на начало отсчета номера дня недели. У нас принято начинать отсчет с понедельника, этому типу соответствует значение 2. Таким образом, функция по отображению в какой-то ячейке дня недели из даты 4 июня 2019 г., хранящейся в ячейке B2 будет выглядеть так: =ДЕНЬНЕД(B2;2)
Результатом срабатывания формулы будет 2, т.е., вторник.