Что вы узнаете

  1. Как устроены даты.
  2. Как быстро их проставлять.
  3. Как определять, сколько времени от одной даты до другой.
  4. Что можно анализировать с помощью дат.

Время прохождения урока — 10—15 минут.

В этом уроке вы займетесь приятным планированием: отпуска, праздников и поездок. Как быстро посчитать, сколько дней до долгожданного отпуска, не пропустить важный юбилей и распланировать нагрузку для активного отдыха?

Это несложный, но важный урок, потому что работа с датами — один из универсальных навыков, который пригодится вам для многих задач.

Как устроены даты

На самом деле даты в таблицах — это обычные числа, которые таблицы отображают особым образом. Например, число 1 — это 31 декабря 1899 года. И каждое следующее число — это плюс один день. Поэтому, например, дата 09.11.2020 — это число 44 144.

Чтобы ввести дату, нужно через точку указать день, месяц и год. Год можно не указывать, если подразумеваете текущий. Например, если сейчас 2021 год и мы вводим «1.7», это превратится в 1 июля 2021 года:

А чтобы вставить сегодняшнюю дату, есть горячая клавиша, о которой мы говорили в первом уроке: Ctrl + ; (⌘ + ;).

Иногда бывает, что вы вводите дату в таблицу, а она выглядит не как дата — как-нибудь криво. Тогда нужно зайти в форматы и выбрать «Дата»:

Как быстро проставлять даты

Предположим, вы хотите составить список задач на ближайший месяц. Для этого вам нужно в столбик записать все даты этого месяца. Можно проставлять их вручную, но есть вариант проще: вписать первую, а потом «протянуть» ее вниз или вправо:

С месяцами и годами работает похожая схема — если, например, вам нужна таблица, в которой будут первые числа каждого месяца в году. Разница в том, что нужно проставить не одну дату, а две. Тогда таблица поймет, что следующие даты нужно вставлять с интервалом в месяц, год или какой-то произвольный период:

Как определять, сколько времени от одной даты до другой

Как определить, сколько дней осталось до нужной даты. Чтобы посчитать, сколько дней прошло от одной даты до другой, можно просто вычесть из более поздней даты более раннюю — как обычные числа. Результат будет в днях:

Еще в таблицах есть функция, которая всегда выводит сегодняшний день, — TODAY(). Например, если сегодня 21 декабря 2021 года, функция TODAY(), введенная в ячейку B2, выведет эту дату:

Когда наступит следующий день, дата в ячейке автоматически поменяется.

Эта функция помогает, когда нам нужно посчитать, сколько осталось дней до конкретной даты, например до начала долгожданного отпуска. В ячейке A2 записана дата начала отпуска, в ячейке B2 — функция TODAY(). Все, что нам нужно, это вычесть из даты начала отпуска сегодняшнюю дату — то есть из ячейки B1 ячейку B2:

Задание

Посчитайте, сколько дней от сегодняшнего дня до Хеллоуина — следующего 31 октября. Проверить себя можно, если загуглить «сколько дней до Хеллоуина».

Как посчитать, сколько дней осталось до конца месяца. Допустим, вы хотите, чтобы у вас под рукой всегда был счетчик, показывающий, сколько осталось дней до конца текущего месяца. Это может пригодиться, чтобы спланировать свои рабочие дела. Хорошая новость — считать руками не придется, абсолютно все посчитается автоматически:

Разберемся с формулами. В ячейке B1 стоит уже знакомая нам функция TODAY(), которая всегда выводит сегодняшний день. Далее нам пригодится новая функция EOMONTH() — она выводит последний день нужного нам месяца.

В нашем случае нужно определить, какой день будет последним в текущем месяце — то есть в том, что выдала функция TODAY(). Это удобнее, чем прописывать конец месяца вручную, потому что в разных месяцах разное количество дней. Если использовать функцию, дата будет меняться автоматически.

Формула будет выглядеть так: =EOMONTH(B1;0).

Функция TODAY()в ячейке B1 показывает сегодняшний день — это наша точка отсчета. Мы указываем номер ячейки, а не конкретную дату, потому что текущая дата будет меняться каждый день.

0 — это количество месяцев до или после начальной даты. Если мы хотим получить последний день этого же месяца, вводим 0. Конец следующего месяца — вводим 1. Конец предыдущего месяца — -1. И так далее.

Итак, у нас есть дата конца месяца, =EOMONTH(B1;0), и сегодняшняя дата, B1. Чтобы узнать, сколько осталось дней до конца месяца, просто вычитаем из ячейки с концом месяца ячейку с сегодняшней датой:

Эту формулу можно никогда не трогать: какой бы сейчас ни был месяц, она всегда покажет, сколько в нем осталось дней.

Задание

В вашей рабочей тетради есть лист «Сколько осталось дней» с двумя примерами, которые были выше. Посмотрите, как все устроено, и настройте даты под свои задачи.

Как посчитать, сколько месяцев или лет осталось до даты. По умолчанию результат вычитания одной даты из другой — дни, и это не очень удобно, если вы хотите понять, например, сколько полных месяцев осталось от сегодняшней даты до 12 июля 2046 года.

Тут поможет функция DATEDIF() — от английского date difference, разница дат. С ее помощью можно посчитать разницу между двумя датами и указать, в чем эту разницу измерять: в днях, месяцах, годах.

Допустим, вы хотите знать, сколько месяцев осталось до вашего любимого праздника:

В ячейке B1 у нас уже известная функция TODAY() — она выводит сегодняшний день. В ячейке B2 — целевая дата.

В ячейку B4 мы запишем формулу, чтобы понять, сколько месяцев осталось: =DATEDIF(B1;B2;"M"). Вот что есть что:

  • B1 — дата начала периода;
  • B2 — дата окончания периода;
  • "M" — единица измерения, то есть месяцы, month.

Однако обратите внимание: DATEDIF() в нашем случае считает полные месяцы между двумя датами. Если между стартом и финишем будет 9 месяцев и 28 дней, формула выведет число 9.

Как настроить счетчик, показывающий, сколько точно осталось до даты. В функции DATEDIF() можно выбрать и другие единицы измерения:

  1. "D" — дни, days. В этом случае результат будет такой же, как и при простом вычитании из даты окончания даты начала.
  2. "Y" — годы, years. Считаются целые годы. Между датами 03.12.2020 и 03.12.2021 формула насчитает год. Но если выбрать даты 03.12.2020 и 02.12.2021, формула с "Y" покажет ноль, потому что целых лет между датами нет.
  3. "MD" — это количество дней между двумя датами без учета полных месяцев. Например, между 1 апреля 2021 года и 2 августа 2021 года формула с "MD" насчитает один день: она не учтет апрель, май, июнь и июль.
  4. "YM" — считает разницу в месяцах, не учитывая целые годы. Между 1 января 2021 года и 1 февраля 2031 года формула насчитает 1 месяц.
  5. "YD" — считает разницу в днях, не учитывая целые годы. Между 1 января 2021 года и 15 февраля 2035 года формула насчитает 45 дней.

Зачем могут понадобиться все эти YM, MD и другие странные единицы измерения? Например, чтобы настроить точный счетчик, который покажет и годы, и месяцы, и дни до конкретной даты. Это может пригодиться, чтобы подготовиться к важному событию, например к собственной свадьбе:

Для этого мы используем три формулы — под каждую единицу измерения.

В ячейке B6: =DATEDIF(B1;B2;"Y") — разница между двумя датами в годах.

В ячейке B7: =DATEDIF(B1;B2;"YM") — разница между двумя датами в месяцах, не учитывая полные годы.

В ячейке B8: =DATEDIF(B1;B2;"MD") — разница между двумя датами в днях, не учитывая полные месяцы.

В итоге мы получаем точную до дня разницу.

Задание

На листе «Точный счетчик времени» в вашей рабочей тетради есть пример счетчика, который мы сделали выше. Поменяйте в нем даты и единицы измерения в формулах и посмотрите, как меняются значения.

Запомним

  1. Дата — это обычное число, которое таблица представляет определенным образом. Например, дата 28.01.2021 — это число 44224. Поэтому, чтобы посчитать, сколько дней прошло от одной даты до другой, можно просто вычесть из более поздней даты более раннюю — как обычные числа. Значение будет в днях.
  2. Если разница нужна не в днях, а в месяцах или годах, пригодится функция DATEDIF(). В ней нужно прописать даты начала и окончания периода и единицу измерения: "D" — дни, days; "M" — месяцы, month; "Y" — годы, years.
  3. Существуют специфические единицы измерения: "MD" — это количество дней между двумя датами без учета полных месяцев, "YM" — месяцы без учета лет, "YD" — дни без лет. Их можно использовать, чтобы настроить счетчик до определенной даты.

Как настроить аналитику с помощью дат

В следующих двух блоках мы объединим навыки, полученные в прошлом уроке, с новыми знаниями о датах.

Чтобы выставить условия для анализа дат, мы изучим, как работают функции условий COUNTIF() и AVERAGEIF().

COUNTIF() и COUNTIFS() считают количество ячеек, соответствующих одному или нескольким условиям соответственно. А AVERAGEIF() и AVERAGEIFS() — среднее арифметическое среди ячеек, соответствующих одному или нескольким условиям. Они работают так же, как функции SUMIF() и SUMIFS(), которые мы рассматривали в предыдущем уроке.

Представьте, что вы решили совместить свой отпуск с настоящим вызовом — и запланировали пробежать марафон в Амстердаме через три месяца. Чтобы справиться с такой задачей, нужны регулярные тренировки. В этом поможет таблица, в которой вы сможете подбить разную статистику по собственной нагрузке:

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

Как посчитать сумму за каждый месяц. Для начала можно создать технический столбец, в котором будет определяться месяц интересующей нас даты — от 1 до 12. За определение месяца отвечает функция MONTH(). Например, формула =MONTH(A2) выведет номер месяца из даты в ячейке A2. Проставим формулы на тех же строках, где стоят даты. Все как обычно — пишем формулу для первой даты, а затем копируем вниз на нужный диапазон:

Такие же функции есть для выделения из даты только дня или только года — DAY() и YEAR() соответственно.

Теперь можно применить уже знакомую функцию SUMIF(), чтобы посчитать наш пробег за октябрь. Функция суммирует ячейки, которые отвечают определенному условию. Условие такое: если месяц из столбца D совпадает с октябрем (10), то суммируем все соответствующие строки из столбца B.

Формула будет такой: =SUMIF(D:D;10;B:B).

Диапазон, в котором ищем условие, — наш столбец с месяцами, D:D.

Какое условие ищем — совпадение с нужным нам месяцем, октябрем, 10.

Какой диапазон суммируем — тот, где указываем пробег, то есть B:B.

Для ноября мы просто сменим в формуле 10 на 11. Для декабря — на 12:

Как посчитать не сумму, а количество за каждый месяц. Можно продолжить собирать статистику: например, сколько пробежек вы совершили в каждом месяце. Для этого нам понадобится функция, аналогичная SUMIF(), — COUNTIF(). Напоминаем, что она считает количество ячеек, соответствующих определенному условию, — не сумму в них, а само количество заполненных ячеек. Формула для октября будет такой: =COUNTIF(D:D;10).

Здесь D:D — диапазон, в котором мы будем считать, 10 — условие, как в предыдущем примере, октябрь — десятый по счету месяц:

Как посчитать среднее арифметическое по условию. Допустим, вы хотите оценить среднюю дистанцию пробежек по месяцам, чтобы потом сравнить, когда средняя нагрузка была больше. Можно использовать функцию AVERAGEIF(). Она считает среднее арифметическое среди ячеек, соответствующих заданному условию:

Формула для октября будет такой: =AVERAGEIF(D:D;10;B:B).

Здесь D:D — наш диапазон, где будем искать нужное значение. 10 — значение месяца, которое будем искать в этом диапазоне: октябрь — десятый месяц. B:B — диапазон, по которому мы будем считать среднее арифметическое при выполнении условия.

Как анализировать информацию по разным дням недели. Предположим, вы хотите проанализировать свою активность не по месяцам, а по разным дням недели, чтобы оптимально распределить свою нагрузку. Поможет функция WEEKDAY() — день недели: если применить ее к дате, она выведет номер дня недели, который этой дате соответствует — от 1 до 7. Создадим отдельный столбец с днями недели:

Формула в ячейке C2 на изображении выше будет выглядеть так: =WEEKDAY(A2;2).

Здесь A2 — дата, для которой мы определяем день недели. В нашем случае дата находится в ячейке A2, поэтому ссылаемся на нее.

2 — тип исчисления. Если не указать этот параметр, счет дней недели будет как в США — с воскресенья. Если выбрать 2, первым днем недели будет понедельник.

А потом просто копируем эту формулу на все остальные дни.

Дальше с помощью все той же функции COUNTIF() можно посчитать количество пробежек, например, по пятницам:

Формула будет выглядеть так: =COUNTIF(C:C;5). Здесь C:C — диапазон, где ищем день недели, 5 — номер дня недели, нас интересует пятый.

Запомним

  1. Функция MONTH() поможет нам автоматически вывести из дат месяцы, чтобы мы могли настроить аналитику только по ним.
  2. После этого мы можем посчитать сумму значений за конкретный месяц функцией SUMIF() — для этого нужно поставить условием поиск нужного месяца.
  3. Чтобы посчитать количество значений или среднее арифметическое, мы используем функции COUNTIF() и AVERAGEIF() соответственно. Они работают по той же логике.
  4. Функция WEEKDAY() выводит номер дня недели. Будьте внимательны при составлении формулы: следует указать тип исчисления «2», иначе первым днем будет считаться воскресенье — по американской системе.

Функции, которые вы освоили

  1. TODAY() — автоматически выводит сегодняшний день. Результат будет сам ежедневно меняться.
  2. EOMONTH() — автоматически считает, какой день будет концом месяца.
  3. DATEDIF() — считает разницу между двумя датами. Разницу можно посчитать в днях, месяцах, годах.
  4. DAY(), MONTH() и YEAR() — выводят из даты отдельно число, соответствующее дню, месяцу и году.
  5. WEEKDAY() — определяет, на какой день недели приходится дата.
  6. COUNTIF() и COUNTIFS() — считают количество ячеек, соответствующих одному или нескольким условиям соответственно.
  7. AVERAGEIF() и AVERAGEIFS() — считают среднее арифметическое среди ячеек, соответствующих одному или нескольким условиям соответственно.