Если у вас есть лист Excel с большим количеством дат, скорее всего, вам в конечном итоге придется вычислить разницу между некоторыми из этих дат. Может быть, вы хотите узнать, сколько месяцев вам понадобилось, чтобы погасить свой долг, или сколько дней вам понадобилось, чтобы похудеть на определенное количество?
Вычислить разницу между датами в Excel легко, но это может усложниться в зависимости от того, как вы хотите рассчитать определенные значения. Например, если вы хотите узнать количество месяцев между 05.02.2016 и 15.01.2016, ответ должен быть 0 или 1? Некоторые люди могут сказать 0, поскольку между датами проходит не полный месяц, а другие могут сказать 1, потому что это другой месяц.
В этой статье я покажу вам, как вычислить разницу между двумя датами, чтобы найти количество дней, месяцев и лет, с помощью разных формул в зависимости от ваших предпочтений.
Дни между двумя датами
Самый простой расчет, который мы можем сделать, — это получить количество дней между двумя датами. Преимущество расчета дней в том, что на самом деле существует только один способ расчета значения, поэтому вам не придется беспокоиться о том, что разные формулы дадут разные ответы.
В моем примере выше в ячейках A2 и A3 хранятся две даты. Справа вы можете видеть, что разница между этими двумя датами составляет 802 дня. В Excel всегда есть несколько способов вычислить одно и то же значение, и я это сделал здесь. Давайте посмотрим на формулы:
Первая формула представляет собой простое вычитание двух дат: A3 – A2. Excel знает, что это дата, и просто вычисляет количество дней между этими двумя датами. Легко и очень прямолинейно. Кроме того, вы также можете использовать функцию ДНИ .
=DAYS(A3, A2)
Эта функция принимает два аргумента: дату окончания и дату начала. Если вы поменяете даты в формуле, вы получите просто отрицательное число. Наконец, вы можете использовать функцию DATEDIF , которая включена в Excel из Lotus 1-2-3 дней, но не является официально поддерживаемой формулой в Excel.
=DATEDIF(A2, A3, "D")
Когда вы введете формулу, вы увидите, что Excel не предлагает никаких предложений по полям и т. д. К счастью, здесь вы можете увидеть синтаксис и все поддерживаемые аргументы для Функция РАЗНДАТ.
В качестве дополнительного бонуса: что, если вы хотите посчитать количество дней недели между двумя датами? Это достаточно просто еще и потому, что у нас есть встроенная формула:.
=NETWORKDAYS(startDate,endDate)
Рассчитать количество дней и дней недели достаточно просто, поэтому давайте теперь поговорим о месяцах.
Месяцы между двумя датами
Самый сложный расчет — это количество месяцев, поскольку его можно округлить в большую или меньшую сторону в зависимости от того, полный это месяц или неполный месяц. Теперь в Excel есть функция МЕСЯЦЫ, но она очень ограничена, поскольку при вычислении разницы она будет учитывать только месяц, а не год. Это означает, что его можно использовать только для расчета разницы между двумя месяцами одного года.
Поскольку это бессмысленно, давайте посмотрим на некоторые формулы, которые дадут нам правильный ответ. Microsoft предоставила эти здесь, но если вам лень зайти по ссылке, я также предоставил их ниже.
Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Это две довольно длинные и сложные формулы, и вам действительно не нужно понимать, что происходит. Вот формулы в Excel:
Обратите внимание: вам следует отредактировать формулу округления в меньшую сторону в самой ячейке, поскольку по какой-то странной причине вся формула не отображается в строке формул. Чтобы увидеть формулу в самой ячейке, перейдите на вкладку Формулы и выберите Показать формулы .
Итак, каков конечный результат двух формул в нашем текущем примере? Давайте посмотрим ниже:
Округление вверх дает мне 27 месяцев, а округление вниз дает мне 26 месяцев, что является точным, в зависимости от того, как вы хотите на это посмотреть. Наконец, вы также можете использовать функцию РАЗНДАТ, но она рассчитывает только полные месяцы, поэтому в нашем случае она возвращает ответ — 26.
=DATEDIF(A2, A3, "M")
Годы между двумя датами
Как и в случае с месяцами, годы также можно рассчитать в зависимости от того, хотите ли вы считать 1 год полными 365 днями или учитывается изменение года. Итак, давайте возьмем наш пример, где я использовал две разные формулы для расчета количества лет:
Одна формула использует РАЗДАТ, а другая — функцию ГОД. Поскольку разница в количестве дней составляет всего 802, функция РАЗДАТ показывает 2 года, тогда как функция ГОД показывает 3 года.
Опять же, это вопрос предпочтений и зависит от того, что именно вы пытаетесь вычислить. Полезно знать оба метода, чтобы решать разные задачи с помощью разных формул.
Если вы внимательно относитесь к используемым формулам, вычислить именно то, что вам нужно, довольно легко. Помимо упомянутых мною функций даты существует гораздо больше, поэтому не стесняйтесь проверить их на сайте Поддержка офиса. Если у вас есть какие-либо вопросы, не стесняйтесь комментировать. Наслаждайтесь!.