Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Функция ПРЕОБР / CONVERT
Дальше: Логические выражения и функция ЕСЛИ / IF

Работа с датами

Файл с примерами: Даты.xlsx
ДАТЫ И ВРЕМЯ В EXCEL И GOOGLE ТАБЛИЦАХ
За любой датой в Excel скрывается целое число. Датой его делает форматирование (первая дата в Excel — это 1 января 1900 года, и ей соответствует порядковый номер 1). Благодаря этому возможны вычисления с датами: один день — единица, а значит, можно прибавить к дате 7 дней и получить дату через неделю. Или вычесть из одной даты другую и получить разницу в днях между ними.
Аналогично со временем: если одна единица — это день, то часть единицы (число от 0 до 1) — время, то есть часть дня. Например, 0,5 = 12:00 (половина дня), а 0,75 = 18:00.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
ДД.ММ.ГГГГ (например, 01.06.2022);
ДД/ММ/ГГГГ (например, 01/06/2022);
ГГГГ-ММ-ДД (например, 2022-06-01);
ГГГГ/ММ/ДД (например, 2022/06/01);
ДД название месяца ГГ(ГГ) (например, 1 июнь 22);
ДД короткое обозначение месяца ГГ(ГГ) (например, 1 июн 2022).
Знать про это нужно, ведь, если вы видите числа там, где должны быть даты, дело может быть только в числовом форматировании: нужно поменять формат на «Дату» или любой другой формат даты и/или времени. И благодаря тому что «под капотом» даты — число, с ним можно проводить арифметические операции (об этом — через пару абзацев).
Если к дате и времени применить числовой формат, мы увидим соответствующие им числа.

 

 

Даты в формулах можно использовать:
• как константы, указывая в кавычках в одном из стандартных форматов ("01.01.2021", "01/01/2021", "2021-01-01", "ГГГГ/ММ/ДД");
• ссылаясь на ячейки, где даты хранятся, — уже без кавычек.
С датами можно производить операции вычитания и сложения:
• вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней);

 

 

• прибавить к дате число и получить дату, которая наступит через соответствующее количество дней.

 

 

ФУНКЦИИ ДЛЯ ОТОБРАЖЕНИЯ ТЕКУЩЕЙ ДАТЫ

 

У этих функций нет аргументов, потому что их результат ни от чего не зависит (сегодняшняя дата всегда остается таковой). Поэтому у них скобки для аргументов всегда остаются пустыми (но ввести их все равно необходимо, тем самым показав Excel, что мы подразумеваем здесь функцию, а не что-то другое, например текст или ссылки на ячейки):
=СЕГОДНЯ()
=ТДАТА()
Обе входят в число волатильных функций: это значит, что они пересчитываются при любом изменении в книге Excel или документе Google Таблиц.
Если вам нужно вставить текущую дату как значение, чтобы она не пересчитывалась и не менялась в будущем, воспользуйтесь сочетанием клавиш Ctrl +;(^ +;).
А для вставки текущего времени как значения — сочетанием Ctrl + Shift +:( +;).
ФУНКЦИЯ РАЗНДАТ / DATEDIF
Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст сотрудника), воспользуйтесь функцией РАЗНДАТ / DATEDIF. При ее вводе не будут отображаться аргументы, но не обращайте на это внимания — она работает во всех версиях приложения.
Вот ее синтаксис:
=РАЗНДАТ(дата_начала; дата_окончания; единица измерения)
Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.
Единица измерения задается в кавычках. Есть следующие возможные варианты:
• d — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
• m — число полных месяцев в периоде;
• y — число полных лет в периоде;
• md — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
• ym — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
• yd — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).
Соответственно, чтобы вычислить возраст человека на текущую дату, подойдет следующая формула:
=РАЗНДАТ(ссылка на ячейку с датой рождения; СЕГОДНЯ(); "y")
=DATEDIF(ссылка на ячейку с датой рождения; TODAY(); "y")
В Google Таблицах функция РАЗНДАТ / DATEDIF тоже есть — ее аргументы будут отображаться в подсказке при вводе функции.

 

 

ФУНКЦИИ ДЛЯ ПОЛУЧЕНИЯ ОТДЕЛЬНЫХ ПАРАМЕТРОВ ДАТЫ
Есть функции, извлекающие только один элемент даты. Это бывает удобно, чтобы создать отдельный столбец с номерами недель или месяцев, например, и по нему фильтровать/анализировать данные — в соответствующем разрезе.

 

 

Обратите внимание, что на выходе эти функции возвращают числа (кроме КОНМЕСЯЦА / EOMONTH), а не даты. Если вы хотите отображать в ячейке, например, только день недели или номер месяца __, но в значении этой ячейки хотите сохранить дату, то лучше воспользоваться числовыми форматами, чтобы оставить в ячейке значение, но отображать только отдельный параметр.

 

К сожалению, для вычисления номера квартала функции нет ни в Excel любых версий, ни в Google Таблицах. Обычно номер квартала вычисляют составной формулой, опираясь на номер месяца. Например, такой (формула есть в файле с примерами):
=ЦЕЛОЕ((МЕСЯЦ(ячейка с датой) + 2)/3)
Для января (месяц = 1) формула будет возвращать единицу:
(1 + 2)/3 = 1
А для августа (8):
(8 + 2)/3 = 3,(3)
Дробную часть мы убираем с помощью функции ЦЕЛОЕ / INT и за счет этого получаем целое число 3 для августа.
ЦЕЛОЕ((8 + 2)/3) = ЦЕЛОЕ(3,(3)) = 3
ВЫЧИСЛЕНИЯ С РАБОЧИМИ ДНЯМИ
В Excel и Google Таблицах есть две функции для работы с датами — ЧИСТРАБДНИ / NETWORKDAYS / (количество дней между двумя датами) и РАБДЕНЬ / WORKDAY (дата, которая наступит по прошествии заданного числа рабочих дней).

 

 

Например, нам нужно знать, какая дата наступит через 30 рабочих, а не календарных дней после некоторой даты, для этого нужна функция РАБДЕНЬ:
=РАБДЕНЬ(дата; число рабочих дней; [Праздники])

 

 

У обеих функций есть необязательный аргумент «Праздники», который позволяет ссылаться на отдельный диапазон с датами, которые не нужно считать рабочими (по умолчанию все дни с понедельника по пятницу будут рабочими при расчетах, а государственные, корпоративные праздники нужно исключать через последний аргумент этих функций).
Так что если нам нужно исключить какие-то дни как праздничные, введем их в отдельных ячейках и будем ссылаться на эти ячейки:
=РАБДЕНЬ(B1;30;G1:G2)

 

 

Функция ЧИСТРАБДНИ возвращает не дату, как РАБДЕНЬ, а число — число рабочих дней в периоде от одной даты до другой:
=ЧИСТРАБДНИ(начальная дата; конечная дата; [Праздники])
Даты, как и в других функциях, могут быть статичными (их можно указать внутри функции в кавычках или ссылаться на ячейки с ними), а могут быть и расчетными.
Допустим, нам нужно число рабочих в текущем году на текущую дату:
=ЧИСТРАБДНИ(первый день года; текущая дата; [Праздники])
Текущую дату вычислить легко — это функция СЕГОДНЯ / TODAY.
А вот первый день года можно вычислить с помощью функции ДАТА / DATE, она позволяет сформировать дату из трех составляющих — года, месяца и дня:
=ДАТА(год; месяц; день)
Например, следующая функция будет возвращать 01.01.2023:
=ДАТА(2023;1;1)
Если нам нужна первая дата текущего года, то второй и третий аргумент останутся единицами, а год можно вычислять как год от сегодняшней даты:
=ДАТА(ГОД(СЕГОДНЯ();1;1)
Итак, если все собрать, то функция для расчета рабочих дней в текущем году будет выглядеть так:
=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());1;1); СЕГОДНЯ())
У обеих функций есть «международная» версия (.МЕЖД или. INTL на конце), в которой задается специальный тип рабочей недели. Вы можете выбрать один из вариантов в списке или ввести вручную соответствующее ему число. Например, 7 — выходные в пятницу и субботу.

 

Или задать ваш собственный (в том же аргументе) в формате 0011001, где 1 = выходной, а 0 = рабочий день.
Назад: Функция ПРЕОБР / CONVERT
Дальше: Логические выражения и функция ЕСЛИ / IF