Как вычесть даты в Excel

Если у вас есть лист Excel с большим количеством дат, скорее всего, вам в конечном итоге потребуется рассчитать разницу между некоторыми из этих дат. Может быть, вы хотите узнать, сколько месяцев вам потребовалось, чтобы погасить свой долг, или сколько дней понадобилось вам, чтобы потерять определенное количество веса?

Рассчитать разницу между датами в Excel легко, но это может стать сложным в зависимости от того, как вы хотите рассчитать определенные значения. Например, если вы хотите узнать количество месяцев между 5/5/2016 и 15.01.2016, должен ли ответ быть 0 или 1? Некоторые люди могут сказать 0, так как это не полный месяц между датами, а другие могут сказать 1, потому что это другой месяц.

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

Дни между двумя датами

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

В моем примере выше у меня есть две даты, хранящиеся в ячейках A2 и A3. Справа вы видите разницу между этими двумя датами - 802 дня. В Excel всегда есть несколько способов вычислить одно и то же значение, и это то, что я сделал здесь. Давайте посмотрим на формулы:

Первая формула представляет собой простое вычитание двух дат, A3 - A2. Excel знает, что это дата, и просто вычисляет количество дней между этими двумя датами. Легко и очень просто. Кроме того, вы также можете использовать ДНИ функция.

= ДНИ (A3, A2)

Эта функция принимает два аргумента: дату окончания и дату начала. Если вы переключите даты в формуле, вы просто получите отрицательное число. Наконец, вы можете использовать функцию под названием DATEDIF, который включен в Excel от Lotus 1-2-3 дня, но не является официально поддерживаемой формулой в Excel.

= DATEDIF (A2, A3, "D")

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

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

= ЧИСТРАБДНИ (StartDate, EndDate)

Подсчет количества дней и дней недели достаточно прост, поэтому давайте поговорим о месяцах.

Месяцы между двумя датами

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

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

Округление - = (ГОД (LDate) -ГОД (EDate)) * 12 + МЕСЯЦ (LDate) -МЕСЯЦ (EDate)

Округление вниз - = ЕСЛИ (ДЕНЬ (LDate)> = ДЕНЬ (EDate), 0, -1) + (ГОД (LDate) -ГОД (EDate)) * 12 + МЕСЯЦ (LDate) -МЕСЯЦ (EDate)

Теперь это две довольно длинные и сложные формулы, и вам действительно не нужно понимать, что происходит. Вот формулы в Excel:

Обратите внимание, что вы должны отредактировать формулу округления вниз в самой ячейке, потому что по какой-то странной причине вся формула не отображается на панели формул. Чтобы увидеть формулу в самой ячейке, нажмите на Формулы вкладка, а затем нажмите Показать формулы.

Итак, каков окончательный результат двух формул в нашем текущем примере? Давайте посмотрим ниже:

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

= DATEDIF (A2, A3, "M")

Годы между двумя датами

Как и в месяцах, годы также могут быть рассчитаны в зависимости от того, хотите ли вы считать 1 год полными 365 днями или учитывается ли изменение года. Итак, давайте возьмем наш пример, где я использовал две разные формулы для расчета количества лет:

Одна формула использует DATEDIF, а другая - функцию YEAR. Поскольку разница в количестве дней составляет всего 802, DATEDIF показывает 2 года, тогда как функция YEAR показывает 3 года..

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

Пока вы тщательно следите за тем, какие формулы вы используете, довольно легко рассчитать именно то, что вы ищете. Помимо перечисленных мною функций, имеется намного больше функций для датирования, поэтому вы можете проверить их на сайте поддержки Office. Если у вас есть какие-либо вопросы, не стесняйтесь комментировать. наслаждаться!