3 простых способа вычитания дат в Excel

В какой-то момент при работе в Excel вам нужно будет найти количество дней между двумя датами.

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

Есть три простых способа вычитания дат в Excel:

1. Простое вычитание
2. Использование функции ДНИ()
3. Использование функции ЧИСТРАБДНИ()

Существует один старый способ, функция DATEDIF(), которую Microsoft больше не поддерживает. Однако перечисленные три метода надежны и точны. Вы быстро найдете различия между датами для таких задач, как сроки проекта или бизнес-анализ.

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

Давайте углубимся в это.

Оглавление

• Понимание дат в Excel
• 1. Использование простого вычитания для получения количества дней
  • Добавление пользовательской метки
• 2. Использование функции DAYS для расчета количества дней
• 3. Использование функции ЧИСТРАБДНИ
  • Указание праздников
  • Указание разных дней для выходных
• Старая функция DATEDIF (не рекомендуется)
• Использование текущей даты в расчетах
• Как вычесть месяцы или годы, чтобы найти день
• Что делать, если дата начала позже даты окончания?
• Советы по точному вычитанию даты
  • Совет 1. Используйте правильную формулу
  • Совет 2. Правильно оформляйте даты
  • Совет 3. Учитывайте високосные годы
  • Совет 4. Четко отображайте даты и результаты
• Распространенные ошибки и устранение неполадок
  • Ошибка 1: #ЗНАЧ!
  • Ошибка 2: неверные результаты
• Заключение

Понимание дат в Excel

Даты в Excel иногда могут сбивать с толку, поскольку они действуют и как числа, и как текст. Это имеет больше смысла, когда вы понимаете, что Excel хранит даты как порядковые номера.

Система дат Excel начинает отсчет с 1 января 1900 года, которому присваивается порядковый номер 1. Каждому последующему дню присваивается следующий номер.

Например, 2 января 1900 года имеет порядковый номер 2.

Вы, наверное, видите, что это упрощает вычитание двух дат и нахождение дней между ними. Давайте пробежимся по рекомендуемым способам сделать это.

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

На моем листе для выборки дат используются ячейки B2 и C2. Вы можете использовать примеры в качестве шаблонов для своей работы.

1. Использование простого вычитания для получения количества дней

Первый способ найти количество дней между двумя датами в Excel — использовать простое вычитание (знак минус). Это формат:

=Конец-Начало

Следуй этим шагам:

1. Введите две даты в отдельные ячейки.
2. В новой ячейке напишите следующую формулу:=C2-B2
3. Нажмите Ввод.

Полученное значение будет количеством полных дней между двумя датами.

В нашем примере я ввел «3 марта 2023 года» в ячейку B2 и «20 марта 2023 года» в ячейку C2. В результате подсчета получилось 17.

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

Добавление пользовательской метки

Если вы хотите отобразить результат с пользовательской меткой, используйте следующую формулу:

=C2-B2 и "дни"

Эта формула объединяет рассчитанную разницу дат ссылок на ячейки с текстом «дни». Это дает более наглядный результат.

Например, если между двумя датами 17 дней, результат будет отображаться как «17 дней».

2. Использование функции DAYS для расчета количества дней

Функция ДНЕЙ Excel — это встроенная функция даты, которая вычисляет количество дней между датами. Это формат:

=ДНЕЙ(Конец, Начало)

Следуй этим шагам:

1. Введите две даты в отдельные ячейки.
2. В новой ячейке напишите следующую формулу:=DAYS(C2, B2)
3. Нажмите Ввод.

Полученное значение будет количеством полных дней между двумя датами.

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

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

3. Использование функции ЧИСТРАБДНИ

Функция даты ЧИСТРАБДНИ вычисляет количество рабочих дней между двумя датами. Поскольку это рабочие дни, в расчет не включены выходные и праздничные дни.

Это формат:

= ЧИСТРАБДНИ(Начало, Конец, [Праздники])

Третий параметр (Праздники) является необязательным. Если вы не используете его, формула будет исключать выходные.

Выполните следующие действия, чтобы использовать простейшую формулу без указания праздников:

1. Введите две даты в отдельные ячейки.
2. В новой ячейке введите следующую формулу:=NETWORKDAYS(B2, C2)
3. Нажмите Ввод.

Если взять даты, которые я использовал в предыдущем примере (3 января и 20 января 2023 г.), результатом этой функции Excel будет 14.

Это на три дня меньше, чем при использовании простого вычитания. Это потому, что в этот период есть два выходных дня (я перепроверил).

Указание праздников

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

Вы делаете это, вводя каждый праздник в диапазон ячеек и помещая диапазон в параметр праздника.

Это легче понять на рабочем примере.

1. Две даты находятся в ячейках B2 и C2.
2. Первая дата праздника находится в ячейке D2, а вторая — в ячейке E2.
3. В ячейке F2 используйте формулу:=NETWORKDAYS(B2, C2, D2:E2)
4. Нажмите клавишу Enter, чтобы увидеть результат.

В нашем примере результат равен 12. Это на два дня меньше, чем в предыдущем расчете, потому что формула нашла список дат в предоставленном нами диапазоне ячеек.

( Я не показываю ячейки A1 и A2 на рисунке. Ячейка A1 пуста, а A2 содержит описание. ).

Кстати, если у вас длинный список дат праздников и вы не уверены, есть ли в нем дубликаты, в видео ниже показано, как проверить данные.

Он также показывает вам, как удалить дубликаты!

Указание разных дней для выходных

Обратите внимание, что ЧИСТРАБДНИ используют субботу и воскресенье как выходные дни по умолчанию для своего расчета.

Если у вас разные выходные, вы можете использовать функцию ЧИСТРАБДНИ.МЕЖД, чтобы настроить день выходного дня.

Если вы хотите узнать больше о формате, вот документация Microsoft .

Старая функция DATEDIF (не рекомендуется)

Вы не найдете функцию РАЗНДАТ в списке встроенных функций в текущей версии Microsoft Excel.

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

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

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

= РАЗНДАТ(Начало, Конец, «д»)

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

В качестве альтернативного использования «m» выполняет расчет как количество месяцев, а «y» дает количество лет.

Вот шаги:

1. Введите две даты в соседние ячейки.
2. В новой ячейке введите следующую формулу: =DATEDIF(Start_Date, End_Date, "d").
3. Нажмите Ввод.

Это вернет дни между двумя датами.

Используя ячейки в наших предыдущих примерах, функция выглядит следующим образом: РАЗНДАТ(B2, C2, «d»).

В отличие от других функций, эта показывает #ЧИСЛО! ошибка, если первая дата после второй.

Использование текущей даты в расчетах

Если вы хотите, чтобы одна из дат в расчете была текущей датой, Excel предлагает удобную функцию «Сегодня».

Вы просто заменяете одну из ссылок на ячейки на СЕГОДНЯ(). На моем листе мне нужен только столбец B для хранения дат. Вот пример:

=ДНЕЙ(СЕГОДНЯ(), B2)

Несколько сбивает с толку тот факт, что эквивалентная функция в VBA имеет другое имя. Используйте функцию Date(), если вы пишете макрос.

Как вычесть месяцы или годы, чтобы найти день

Если вы ищете дату ровно месяц назад, у вас может возникнуть соблазн вычесть 30 дней из вывода функции СЕГОДНЯ().

Однако это будет точно только для подмножества месяцев, поэтому это не очень хороший метод.

Функция ДАТАМЕС предназначена именно для этой цели. Синтаксис:

= ДАТАМЕС(начало, месяцы)

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

=ДАТА(Сегодня(), -1)

Это дает мне дату на целый месяц в прошлом. Это лучший способ вычесть месяцы.

Если вам нужно вычесть годы, функция ГОД вычисляет годы от заданной даты.

Что делать, если дата начала позже даты окончания?

Если первая дата больше второй, рекомендуемые методы не будут считать это ошибкой. Они сделают расчет и предоставят отрицательное число.

Например, если я по ошибке перепутал даты в примерах, результат будет -17.

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

Вот пример того, как объединить функцию ЕСЛИ с функцией ДНИ:

=ЕСЛИ(B2 > C2, "Неверные даты", ДНИ(B2, C2, "d"))

Эта формула Excel проверяет, больше ли первая дата, чем вторая. В этом случае отображается текст «Недействительные даты».

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

В приведенном ниже примере я поменял местами две даты. Функция отображает текст, который я указал для функции IF().

Советы по точному вычитанию даты

При работе с датами в Excel важно обеспечить точность вычислений.

Вот мои лучшие советы, которые помогут вам использовать вычисления даты.

Совет 1. Используйте правильную формулу

Я рекомендую вам не использовать функцию DATEDIF(). Это не всегда надежно.

Вместо этого выберите один из трех других методов, описанных в этой статье.

Совет 2. Правильно оформляйте даты

Убедитесь, что ячейки даты начала и окончания отформатированы как даты.

Для этого выполните следующие действия:

1. щелкните правой кнопкой мыши по ячейке.
2. выберите «Формат ячеек» (или нажмите ctrl + 1, чтобы открыть диалоговое окно).
3. выберите «Дата» на вкладке «Число».

Это поможет избежать возможных ошибок в ваших расчетах.

Совет 3. Учитывайте високосные годы

Имейте в виду, что високосные годы могут влиять на количество дней между двумя датами.

Excel автоматически учитывает високосные годы при расчете даты, поэтому, если вы будете следовать правильной формуле и форматированию, вы должны получить точные результаты.

Вы можете найти список високосных лет здесь .

Совет 4. Четко отображайте даты и результаты

Чтобы улучшить читаемость ваших данных и результатов, рассмотрите возможность использования некоторых функций форматирования Excel:

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

Распространенные ошибки и устранение неполадок

При работе с датами в Excel вы можете столкнуться с несколькими распространенными ошибками. Вот несколько советов по устранению неполадок, которые помогут вам решить эти проблемы:

Ошибка 1: #ЗНАЧ!

Эта ошибка возникает, когда значения ячеек не распознаются как правильные даты.

Чтобы решить эту проблему:

• Убедитесь, что обе ячейки даты отформатированы как даты.
• Проверьте настройки локали, чтобы правильно интерпретировать даты.

Разные локали имеют разный формат даты (например, дд/мм/гг или мм/дд/гг).

Ошибка 2: неверные результаты

Если вы получаете неправильные результаты, рассмотрите следующее

• Дважды проверьте, как вы ввели свои даты. Смешение месяца и дня может привести к ошибкам в расчетах.
• Используйте сетевые дни, если вы хотите подсчитывать рабочие дни только между двумя датами, используя функцию ЧИСТРАБДНИ().
• Последовательно используйте один и тот же способ форматирования дат на всем листе, чтобы избежать путаницы и ошибок.
• Избегайте ручных вычислений, используя методы, описанные в этой статье.

Заключение

В этой статье мы рассмотрели различные методы вычитания дат в Excel и вычисления разницы в днях между двумя датами.

К настоящему времени вы должны быть уверены в использовании функции ДНИ, функции ЧИСТРАБДНИ и простого вычитания для получения точных результатов.

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

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

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

Продолжайте практиковаться и экспериментировать с различными форматами и функциями даты, чтобы использовать весь потенциал возможностей даты Excel. Удачных расчетов!

Готовы вывести свои навыки работы с Excel на новый уровень? Ознакомьтесь с нашим