разница дат в днях, месяцах и годах
Сегодняшняя запись в блоге посвящена выяснению разницы между двумя датами в Google Таблицах. Вы увидите множество формул DATEDIF для подсчета дней, месяцев и лет и узнаете, как ЧИСТРАБДНИ используются для подсчета только рабочих дней, даже если ваши праздники основаны на пользовательском расписании.
Многие пользователи электронных таблиц находят даты запутанными, если не чрезвычайно сложными для обработки. Но хотите верьте, хотите нет, но для этой цели есть несколько удобных и простых функций. DATEDIF и NETWORKDAYS — пара из них.
Функция DATEDIF в Google Sheets
Как и в случае с функциями, их имена подсказывают действие. То же самое касается DATEDIF. Его нужно читать как разница датнет датировано, еслии это означает разница в датах. Следовательно, DATEDIF в Google Sheets вычисляет разницу между двумя датами.
Давайте разобьем его на части. Функция требует три аргумента:
=DATEDIF(дата_начала, дата_окончания, единица измерения)
- Дата начала – дата, используемая в качестве отправной точки. Это должно быть одно из следующих:
- сама дата в двойных кавычках: “13.08.2020”
- ссылка на ячейку с датой: А2
- формула, которая возвращает дату: ДАТА(2020, 8, 13)
- число, которое обозначает определенную дату и может быть интерпретировано как дата Google Sheets, например 44056 представляет собой 13 августа 2020 г..
- Дата окончания – дата, используемая в качестве конечной точки. Он должен быть того же формата, что и Дата начала.
- Ед. изм – используется, чтобы сообщить функции, какую разницу нужно вернуть. Вот полный список единиц, которые вы можете использовать:
- “Д” – (Короче для дни) возвращает количество дней между двумя датами.
- “М” – (months) количество полных месяцев между двумя датами.
- “Ю” – (years) количество полных лет.
- “МД” – (дни без учета месяцев) количество дней после вычитания целых месяцев.
- “ЮД” – (дни без учета лет) количество дней после вычитания целых лет.
- “ЮМ” – (месяцев без учета лет) количество полных месяцев после вычитания полных лет.
Примечание. Все единицы должны быть помещены в формулы так же, как они указаны выше – в двойных кавычках.
Теперь давайте соберем все эти части вместе и посмотрим, как формулы DATEDIF работают в Google Таблицах.
Рассчитать дни между двумя датами в Google Sheets
Пример 1. Считаем все дни
У меня есть небольшая таблица для отслеживания некоторых заказов. Все они были отгружены в первой половине августа – Дата отгрузки – это будет моя дата начала. Также есть приблизительная дата доставки – Срок оплаты.
Я буду считать дни – “Д” – между датами отгрузки и сроками, чтобы увидеть, сколько времени требуется для доставки товаров. Вот формула, которую я должен использовать:
=РАЗНДАТ(B2, C2, “D”)
Я ввожу формулу DATEDIF в D2, а затем копирую ее вниз по столбцу, чтобы применить к другим строкам.
Кончик. Вы всегда можете рассчитать весь столбец сразу с помощью одной формулы, используя ARRAYFORMULA:
=Формуламассива(РАЗНДАТ(B2:B13, C2:C13, “D”))
Пример 2. Подсчет дней без учета месяцев
Представьте, что между двумя датами есть несколько месяцев:
Как вы считаете только дни, как если бы они принадлежали к одному и тому же месяцу? Правильно: игнорируя полные прошедшие месяцы. DATEDIF вычисляет это автоматически, когда вы используете “МД” Ед. изм:
=РАЗНДАТ(A2, B2, “МД”)
Функция вычитает прошедшие месяцы и подсчитывает оставшиеся дни.
Пример 3. Подсчет дней без учета лет
Другая единица — «YD» — поможет, когда между датами больше года:
=РАЗНДАТ(A2, B2, “ГД”)
Формула сначала вычитает годы, а затем вычисляет оставшиеся дни, как если бы они принадлежали одному и тому же году.
Подсчет рабочих дней в Google Sheets
Есть особый случай, когда нужно считать только рабочие дни в Google Таблицах. Формулы DATEDIF здесь не помогут. И я думаю, вы согласитесь, что вычитание выходных вручную — не самый элегантный вариант.
К счастью, в Google Таблицах есть парочка не очень волшебных заклинаний для этого 🙂
Пример 1. Функция ЧИСТРАБДНИ
Первый называется ЧИСТРАБДНИ. Эта функция вычисляет количество рабочих дней между двумя датами, исключая выходные (субботу и воскресенье) и даже праздничные дни, если это необходимо:
=ЧИСТРАБДНИ(дата_начала, дата_окончания, [holidays])
- Дата начала – дата, используемая в качестве отправной точки. Необходимый.
Примечание. Если эта дата не является выходным днем, она считается рабочим днем.
- Дата окончания – дата, используемая в качестве конечной точки. Необходимый.
Примечание. Если эта дата не является выходным днем, она считается рабочим днем.
- каникулы — это необязательно, когда вам нужно указать определенные праздники. Это должен быть диапазон дат или чисел, представляющих даты.
Чтобы проиллюстрировать, как это работает, я добавлю список праздников, которые проходят между сроками доставки и сроками:
Итак, столбец B — моя дата начала, столбец C — дата окончания. Даты в столбце E — это праздники, которые следует учитывать. Вот как должна выглядеть формула:
=ЧИСТРАБДНИ(B2, C2, $E$2:$E$4)
Кончик. Если вы собираетесь копировать формулу в другие ячейки, используйте абсолютные ссылки на ячейки для праздников, чтобы избежать ошибок или неверных результатов. Или вместо этого рассмотрите возможность создания формулы массива.
Вы заметили, как уменьшилось количество дней по сравнению с формулами DATEDIF? Потому что теперь функция автоматически вычитает все субботы, воскресенья и два праздника, приходящиеся на пятницу и понедельник.
Примечание. В отличие от DATEDIF в Google Sheets, ЧИСТРАБДНИ считаются start_day а также end_day как рабочие дни, если они не являются праздниками. Следовательно, D7 возвращает 1.
Пример 2. NETWORKDAYS.INTL для Google Таблиц
Если у вас есть собственное расписание на выходные, вам пригодится еще одна функция: NETWORKDAYS.INTL. Он позволяет подсчитывать рабочие дни в Google Sheets на основе лично установленных выходных:
=NETWORKDAYS.INTL(дата_начала, дата_окончания, [weekend], [holidays])
- Дата начала – дата, используемая в качестве отправной точки. Необходимый.
- Дата окончания – дата, используемая в качестве конечной точки. Необходимый.
Примечание. NETWORKDAYS.INTL в Google Таблицах также учитывается start_day а также end_day как рабочие дни, если они не являются праздниками.
- выходные дни – это по желанию. Если этот параметр опущен, суббота и воскресенье считаются выходными. Но вы можете изменить это двумя способами:
- Маски.
Кончик. Этот способ идеально подходит для тех случаев, когда ваши выходные разбросаны по всей неделе.
Маска представляет собой семизначный шаблон из 1 и 0. 1 обозначает выходной, 0 рабочий день. Первая цифра в шаблоне всегда понедельник, последняя – воскресенье.
Например, “1100110” означает, что вы работаете в среду, четверг, пятницу и субботу.
Примечание. Маска должна быть заключена в двойные кавычки.
- Числа.
Используйте однозначные числа (1-7), обозначающие пару установленных выходных:
Номер Выходные дни 1 Суббота, воскресенье 2 Воскресенье, понедельник 3 Понедельник, вторник 4 Вторник, среда 5 Среда, четверг 6 Четверг, пятница 7 Пятница, суббота
Или работайте с двузначными числами (11-17), которые обозначают один день отдыха в течение недели:
Номер Выходной день 11 Воскресенье 12 Понедельник 13 Вторник 14 Среда 15 Четверг 16 Пятница 17 Суббота
- Маски.
- каникулы – также является необязательным и используется для указания праздников.
Эта функция может показаться сложной из-за всех этих цифр, но я рекомендую вам попробовать.
Во-первых, просто получите четкое представление о ваших выходных. Давайте сделаем это Воскресенье а также Понедельник. Затем решите, как указать свои выходные.
Если идти с маской, то будет так — 1000001:
=ЧИСТРАБДНИ.МЕЖД(B2, C2, “1000001”)
Но так как у меня два выходных дня подряд, я могу использовать число из таблиц выше, 2 в моем случае:
=ЧИСТРАБДНИ.МЕЖ(B2, C2, 2)
Затем просто добавьте последний аргумент — ссылку на праздники в столбце E, и формула готова:
=NETWORKDAYS.INTL(B2, C2, 2, $E$2:$E$4)
Google Sheets и разница дат в месяцах
Иногда месяцы важнее дней. Если это верно для вас, и вы предпочитаете получать разницу дат в месяцах, а не в днях, позвольте Google Sheets DATEDIF сделать эту работу.
Пример 1. Количество полных месяцев между двумя датами
Упражнение такое же: Дата начала идет первым, затем идет Дата окончания а также “М” – что означает месяцы – в качестве последнего аргумента:
=РАЗНДАТ(A2, B2, “М”)
Кончик. Не забывайте о функции ARRAUFORMULA, которая может помочь вам считать месяцы во всех строках сразу:
=МАССИВФОРМУЛА(РАЗНДАТ(A2:A13, B2:B13, “M”))
Пример 2. Количество месяцев без учета лет
Возможно, вам не потребуется считать месяцы на протяжении всех лет между датами начала и окончания. И DATEDIF позволяет вам это сделать.
Просто используйте “ЮМ” единица, и формула сначала вычитает целые годы, а затем подсчитывает количество месяцев между датами:
=РАЗНДАТ(A2, B2, “ГМ”)
Вычислить годы между двумя датами в Google Sheets
Последняя (но не менее важная) вещь, которую нужно показать вам, — это то, как Google Sheets DATEDIF вычисляет разницу дат в годах.
Я собираюсь подсчитать количество лет, в течение которых пары были женаты, исходя из дат их свадьбы и сегодняшней даты:
Как вы, наверное, уже догадались, я буду использовать “Ю” единица для этого:
=РАЗНДАТ(A2, B2, “Г”)
Все эти формулы DATEDIF являются первыми, которые используются для расчета дней, месяцев и лет между двумя датами в Google Таблицах.
Если ваше дело не может быть решено с их помощью или у вас есть какие-либо вопросы, я рекомендую вам поделиться ими с нами в разделе комментариев ниже.
