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

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

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

На самом деле даты в таблицах — это обычные числа, которые таблицы отображают особым образом. Например, дата 09.11.2020 — это число 44 144. Каждой дате соответствует определенное число.

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

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

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

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

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

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

Вычитаем даты: определяем, сколько времени между двумя датами и сколько осталось до конца месяца

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

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

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

Далее нам пригодится функция EOMONTH() — от английского End of the month, конец месяца. Она выводит последний день нужного нам месяца. В нашем случае формула будет выглядеть так: =EOMONTH(B1;0).

Здесь B1 — дата, от которой будем считать последний день. Мы указываем номер ячейки, а не конкретную дату, потому что сегодняшняя дата будет меняться каждый день.

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

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

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

Еще формулу TODAY() можно использовать, чтобы просто автоматически считать, сколько осталось до определенного дня — например до начала отпуска. В этом случае из даты события нужно вычитать TODAY(). Например, =B1-TODAY():

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

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

Предположим, вы хотите накопить 50 000 Р к 1 ноября 2021 года:

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

В ячейку E2 мы запишем формулу, чтобы понять, сколько месяцев осталось: =DATEDIF(A2;C2;"M"). Здесь A2 — дата начала периода, С2 — дата окончания периода, "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 и другие странные единицы измерения? С их помощью можно привести разницу в более человеческий вид. Например, вам нужно посчитать, сколько осталось от сегодняшнего дня до 25 июля 2029 года: вдруг у вас какое-то событие в этот день. С помощью предыдущих вариантов можно было представить эту разницу либо в днях, либо в месяцах, либо в годах. Но с помощью DATEDIF() можно сделать результат более удобным:

В примере можно посмотреть, как все устроено. Вот какие формулы использовали:

  1. В ячейке A6: =DATEDIF(A1;A2;"Y") — разница между двумя датами в годах.
  2. В ячейке A7: =DATEDIF(A1;A2;"YM") — разница между двумя датами в месяцах, не учитывая полные годы.
  3. В ячейке A8: =DATEDIF(A1;A2;"MD") — разница между двумя датами в днях, не учитывая полные месяцы.

Посчитать разницу в днях — вычесть из даты окончания дату начала.

Посчитать разницу в месяцах, годах или еще каким способом — DATEDIF().

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

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

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

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

Теперь можно применить уже знакомую функцию 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 — условие, как в предыдущем примере, октябрь — десятый по счету месяц:

Найти все пятницы

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

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

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

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

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

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

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

Функции, которые мы использовали в уроке

  1. TODAY() — автоматически выводит сегодняшний день. Результат формулы будет сам меняться каждый день. В русскоязычной версии — СЕГОДНЯ().
  2. EOMONTH() — автоматически считает, какой день будет концом месяца. Например, если в ячейке A1 указана дата 05.02.2021, формула =EOMONTH(A1) выведет результат 28.02.2021. В русскоязычной версии — КОНМЕСЯЦА().
  3. DATEDIF() — считает разницу между двумя датами. Разницу можно посчитать в днях, месяцах, годах. В русскоязычной версии — РАЗНДАТ().
  4. DAY(), MONTH() и YEAR() выводят из даты отдельно число с днем, месяцем и годом. Например, если в ячейке A1 указана дата 05.02.2021, формула =DAY(A1) выведет число 5, =MONTH(A1) — 2, а YEAR(A1) — 2021. В русскоязычной версии — ДЕНЬ(), МЕСЯЦ() и ГОД().
  5. WEEKDAY() — определяет, какой день недели приходится на определенную дату. Чтобы первым днем недели считался понедельник, а не воскресенье, нужно добавить аргумент 2. Например, если в ячейке A1 указана дата 05.02.2021, формула =WEEKDAY(A1;2) выведет значение 5 — пятый день недели, пятница. В русскоязычной версии — ДЕНЬНЕД().

Что вы узнали

  1. Дата — это обычное число, которое таблица представляет определенным образом. Например, дата 28.01.2021 — это число 44224.
  2. Если выделить ячейку с датой и протянуть ее по вертикали или по горизонтали, в соседних ячейках проставятся следующие дни месяца. С месяцами и годами работает так же, но выделять нужно сразу две ячейки.
  3. Посчитать, сколько дней пройдет от одной даты до другой, можно просто вычитанием более ранней даты из более поздней. Если разница нужна не в днях, а в месяцах или годах, пригодится функция DATEDIF().