В Excel часто возникает необходимость вычислить разницу между двумя датами. Это может быть необходимо для определения возраста человека, продолжительности проекта, количества дней между двумя событиями и т.д. В Excel существует несколько способов выполнить эту задачу.
Вам интересно, сколько составляет разница в днях, месяцах и годах между двумя датами? Из этого обзора вы узнаете несколько быстрых и простых способов подсчета, сколько времени проходит между датами в Excel.
Какова бы ни была ваша проблема, один из приведенных ниже примеров в Excel наверняка подскажет вам решение.
- Простой калькулятор дней.
- Как рассчитать разность дат в днях?
- Расчет интервала между датами в Excel с помощью РАЗНДАТ (DATEDIF)
- Как посчитать разность в днях, месяцах и годах.
- Подсчет разности в днях с помощью функции ДНИ
- Как рассчитать количество дней между сегодняшней и другой датой.
- Мастер даты и времени - простой способ создавать формулы расчета дат в Excel
Простой калькулятор дней.
Если вы ищете быстрый ответ, просто укажите две даты в соответствующих ячейках, и наш калькулятор покажет вам, сколько суток между ними:
Хотите узнать формулу, которая рассчитала эти результаты? Это так же просто, как =B3-B2:)
Обратите внимание, что нужно четко знать, что именно вы хотите подсчитать: «дни включительно» или «дни между».
Как рассчитать разность дат в днях?
Самый простой способ посчитать разницу в днях между двумя датами в Эксель - вычесть одну из другой:
= Более новая - Более старая
Чтобы узнать, сколько времени пройдет в периоде между ячейками В3 и B2, используйте следующую формулу:
=B3 - В2
где В2 - более ранняя, а B3 - более.
Результатом подсчета является целое число дней.
Однако, будьте здесь внимательны: при подобном подсчете отработанных дней в том случае, если человек работал и в первый, и в последний день, то 1 день будет «потерян». Добавьте в вашу формулу +1 день.
Как работает эта формула?
Как вы, вероятно, знаете, Microsoft Excel хранит даты в виде порядковых номеров, начинающихся с 1 января 1900 года, которому соответствует номер 1. В этой системе 2 января 1900 года сохраняется как число 2, 3 января 1900 года как 3 и так далее. Таким образом, вычитая одну дату из другой, вы фактически вычитаете целые числа, представляющие их.
В нашем примере формула в C5 на самом деле вычитает 43892 (числовое значение 2 марта 2020г.) из 43900 (числовое значение 10 марта 2020г.) и возвращает результат 8 дней.
Положительный момент этого метода в том, что он отлично работает во всех случаях, независимо от того, какая дата старше, а какая новее. Если вы вычитаете более позднюю дату из более ранней, то формула просто возвращает разность в виде отрицательного числа.
Расчет интервала между датами в Excel с помощью РАЗНДАТ (DATEDIF)
Другой способ найти разницу между датами – использовать функцию РАЗНДАТ (в английском варианте – DATEDIF), которая специально разработана для расчета разности во времени и представления её в различных единицах: дни, месяцы и годы.
А вот ее синтаксис:
= РАЗНДАТ(дата_нач; дата_кон; аргумент)
где
дата_нач и дата_кон - две временных отметки, разность между которыми необходимо рассчитать,
аргумент – указывает, как представить результат; может иметь одно из следующих значений:
- Y – Число полных лет.
- M – Число полных месяцев.
- D – Число дней.
- MD – Дни (значения месяца и года не учитываются).
- YM – Месяцы (значения дня и года не учитываются).
- YD – Также дни, но игнорируя значения года.
Чтобы получить количество дней между двумя датами, укажите момент начала в первом аргументе функции, срок окончания во втором, и «d» в последнем третьем:
РАЗНДАТ(дата_нач; дата_кон; "d")
DATEDIF (start_date, end_date, "d")
Примечание. РАЗНДАТ (DATEDIF) - недокументированная функция, то есть ее нет в мастере функций в Excel. Вы не сможете ее вставить при помощи кнопки fx или меню Вставка — Функция. Чтобы создать формулу РАЗНДАТ в таблице Экселя, вам нужно вписать ее название руками и ввести все аргументы вручную, подсказок ввода не будет.
Вы можете указывать исходные данные различными способами, такими как:
- Ссылки на ячейки. Например, следующая формула вернет количество дней между A3 и B3:
=РАЗНДАТ(A3; B3; "D")
Текстовые выражения. Excel понимает даты в различных форматах, например, «2 март 2020», «20.03.2020», «20/03/2020», «2020/03/20», «20-03-2020», «2020-03-20» и т.д.
Вот примеры расчета количества дней во временных промежутках:
=РАЗНДАТ("2 март 2020"; "2020-03-20"; "D")
=РАЗНДАТ("02-03-2020"; "20/03/2020"; "D")
Естественно, эти значения могут быть записаны и в ячейках, а формула на них будет ссылаться.
- Порядковые номера. Поскольку Microsoft Excel хранит каждую дату как порядковый номер, начинающийся с 1 января 1900 года, вы можете поместить числа, соответствующие датам, непосредственно в формулу. Хотя этот метод полностью поддерживается, все же он не является надежным, поскольку нумерация различается в разных компьютерных системах (Windows и MacOS).
В системе дат 1900 года следующая формула является еще одним способом вычисления месяцев по условиям из предыдущего примера (2 марта 2020 года и 10 марта 2020 года):
=РАЗНДАТ(43900; 43892; "D")
- Результаты выполнения других функций. Например, следующая формула подсчитывает, срок между сегодня (18 марта 2020г.) и 9 мая 2020 года.
=РАЗНДАТ(СЕГОДНЯ(); «9-05-2020»; "D")
В отличие от операции вычитания, функция РАЗНДАТ может только вычесть более старую дату из более новой, но не наоборот. Если точка начала позже точки окончания, расчет выдает ошибку #ЧИСЛО!, как в строке 4 на скриншоте ниже:
1. В днях.
В нашем примере формула расчета в D3 выглядит следующим образом:
=РАЗНДАТ(A3; B3; "D")
Обратите также внимание, что при простом вычитании дат, которые также содержат время, мы получаем в результате дробное число. Так произошло в С5 и С6. Что делать с такими дробями – поговорим чуть позже. А функция РАЗНДАТ время просто игнорирует. В этом вы можете убедиться в диапазоне D5:I6.
Аналогичный результат можно получить математическими формулами:
=ЦЕЛОЕ(В2)-ЦЕЛОЕ(А2)
Время, то есть дробная часть, будет отброшено.
Кстати, если нужно подсчитать только рабочие дни, то следует использовать выражение
=ЧИСТРАБДНИ(В2;А2)
Более подробно как посчитать количество рабочих дней между датами - читайте здесь.
2. В полных месяцах.
Формула в ячейке Е3:
=РАЗНДАТ(A3;B3;"m")
Обратите внимание, выражение вернет количество полных месяцев. В нашем примере 13 месяцев и 7 дней «округлены» ровно до 13. И последний день интервала, как мы уже говорили, эта функция не учитывает. В частности,
=РАЗНДАТ(“01.03.2020”;”31.03.2020”;"m")
возвращает результат 0, так как последний день не включается в расчет. Вы это видите в ячейке E7. Это также будет важно при расчете стажа и отработанного времени. Учитывайте эту особенность и просто добавляйте 1 дополнительный день!
Формула может быть заменена альтернативным выражением:
= 12*(ГОД(B3)-ГОД(A3))- (МЕСЯЦ(A3)-МЕСЯЦ(B3))-(ДЕНЬ(B3)<ДЕНЬ(A3))
3. В полных годах.
Формула в ячейке F3 вернет количество полных лет:
=РАЗНДАТ(A3;B3;"Y")
Опять же, считается только полный год, остальное отбрасывается.
Ну и альтернативный способ расчета —
= ЕСЛИ(ДАТА(ГОД(B3); МЕСЯЦ(A3);ДЕНЬ(A3))<= B3; ГОД(B3)-ГОД(A3);ГОД(B3)-ГОД(A3)-1)
4. В полных месяцах без учета лет.
Используем в I3 аргумент "YM".
=РАЗНДАТ(A3;B3;"YM")
Второй параметр и конечная точка у нас – 10.04.2020 г. Поскольку годы мы здесь игнорируем, то год первого параметра также становится 2020. И мы теперь сравниваем 10.04.2020 и 2.03.2020г.
Результат – 1 полный месяц.
А вот если начало, предположим, будет 01.09.2015г.? Ведь ее нельзя модифицировать до 01.09.2020, поскольку тогда функция выдаст ошибку. И программа ее меняет на 01.09.2019. Результат здесь – 7 полных месяцев.
Я очень сомневаюсь, что кто-то будет проводить подобный расчет. Но он нам будет нужен, чтобы подсчитать количество дней, месяцев и лет между двумя точками. Об этом мы поговорим чуть ниже.
Ну и альтернативный расчет –
=ОСТАТ((12*(ГОД(B3)-ГОД(A3))- (МЕСЯЦ(A3)-МЕСЯЦ(B3))-(ДЕНЬ(B3)<ДЕНЬ(A3)))/12)
5. В днях без учета месяцев и лет.
Здесь немного сложнее. Итак, в G3 пишем:
=РАЗНДАТ(A3;B3;"MD")
Разберем, как получен результат в G3. Поскольку для нас не важны месяц и год, мы в начальной дате меняем их на текущие. То есть вместо 02.03.2019 рассматриваем 02.04.2020. Сравниваем с 10.04.2020 и получаем 8 дней. Есть ли в таком результате смысл? Вряд ли. Поэтому и не рекомендуется применение этого аргумента как отдельного расчета.
Быть может, поэтому РАЗНДАТ и нет в списке официальных функций Excel.
6. В днях без учета лет.
То же самое, что и в предыдущем аргументе. Считаем количество суток между двумя точками без учета лет.
=РАЗНДАТ(A3;B3;"YD")
Для начального момента — 02.03.2019 и конечного – 10.04.2020, разность в 39 дней выглядит явной ошибкой. Аналогично – между 01.09.2015 и 10.04.2020 совсем не 222 дня.
Следовательно, использование этого аргумента также не рекомендуем.
А зачем же всё это нужно? Ответ читайте в следующем разделе.
Как посчитать разность в днях, месяцах и годах.
Чтобы подсчитать, сколько полных лет, месяцев и дней составляет временной интервал "от" и "до" с использованием одной формулы, вы просто объединяете три функции в одно выражение:
=РАЗНДАТ(B2;B3;"Y")&" лет, "&РАЗНДАТ(B2;B3;"YM")&" месяцев, "&РАЗНДАТ(B2;B3;"MD")&" дней"
Если вы не хотите отображать нулевые значения лет, месяцев и дней, то выражение можно изменить, добавив условие ЕСЛИ:
=ЕСЛИ(РАЗНДАТ(B2;B3;"Y")>0;РАЗНДАТ(B2;B3;"Y")&" лет ";"")&ЕСЛИ(РАЗНДАТ(B2;B3;"YM")>0;РАЗНДАТ(B2;B3;"YM")&" месяцев ";"")&ЕСЛИ(РАЗНДАТ(B2;B3;"MD")>0;РАЗНДАТ(B2;B3;"MD")&" дней";"")
Более подробно об условиях и функции ЕСЛИ читайте здесь.
А вот еще один более сложный, но зато более универсальный вариант посчитать разницу между датами в днях, месяцах и годах с учетом склонений и падежей:
=ЕСЛИ(РАЗНДАТ(B2;B3;"y");РАЗНДАТ(B2;B3;"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;B3;"y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(B2;B3;"ym");РАЗНДАТ(B2;B3;"ym")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;B3;"ym")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(B2;B3;"md")>0;РАЗНДАТ(B2;B3;"md")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;B3;"md")-11;100);9); 10);"[<1]ень;[<4]ня;ней");)
Выглядит угрожающе, но зато работает.
Подсчет разности в днях с помощью функции ДНИ
У пользователей Excel 2013 и более новых версий есть еще один удивительно простой способ посчитать разницу между датами в днях — функция ДНИ.
ДНИ(дата_окончания; дата_начала)
Обратите внимание, что по сравнению с РАЗНДАТ, функция ДНИ требует указания дат в обратном порядке.
Итак, наше выражение принимает вид:
=ДНИ(B3;A3)
Как и обычное вычитание, оно возвращает разность в виде положительного или отрицательного числа, в зависимости от того, больше или меньше срок окончания, чем начало:
Думаю, в большинстве случаев можно использовать простое вычитание. Возможно, при этом только нужно будет изменить формат ячейки, убрав дробную часть.
Как рассчитать количество дней между сегодняшней и другой датой.
Для этого расчета вы можете использовать любую из формул, описанных выше, и применить функцию СЕГОДНЯ (TODAY в английском варианте) в качестве одного из аргументов.
Чтобы рассчитать количество прошедших дней с определенного момента, то есть между прошлым и сегодняшним днем:
=СЕГОДНЯ() – Дата_в_прошлом
Для подсчета количества дней, оставшихся до наступления какого-то события, то есть между будущим временем и сегодняшним днем:
=Дата_в_будущем - СЕГОДНЯ()
В качестве примера, давайте посчитаем разницу между текущим моментом времени и более ранним - в A4 (сегодня 18 марта 2020г.):
=СЕГОДНЯ()–A3
А теперь давайте выясним, сколько осталось от сегодняшнего дня (18.03.2020г.) до более позднего срока:
Ну а если не менять выражение по сравнению с предыдущим расчетом, то просто получите отрицательное количество дней.
Мастер даты и времени - простой способ создавать формулы расчета дат в Excel
Как показано в первой части этого руководства, функция РАЗНДАТ (DATEDIF) — довольно универсальная, подходящая для самых разных целей. Однако есть один существенный недостаток — она не документирована Microsoft, то есть вы не найдете РАЗНДАТ в списке функций и не увидите всплывающих подсказок для аргументов, когда начнете вводить формулу в ячейку. Чтобы иметь возможность использовать функцию РАЗНДАТ в своих таблицах, вы должны запомнить ее синтаксис и ввести все аргументы вручную, что может занять много времени и привести к ошибкам, особенно у начинающих.
Надстройка Ultimate Suite радикально меняет это положение, поскольку там имеется мастер даты и времени, который может мгновенно создать практически любую формулу разницы дат. Если вы не уверены, какую формулу использовать, позвольте мастеру даты и времени вычислить количество дней между двумя датами за вас.
Думаю, теперь посчитать в Excel разницу между датами стало для вас намного проще и понятнее.
Если вы не нашли ответ на интересующий вас вопрос, напишите нам в комментариях – постараемся ответить.
Спасибо за рабочую формулу с учетом склонений и падежей при расчете разницы между двумя датами, давно думал как сделать, но вас все гораздо удобней
Добрый день! Спасибо за формулу с учетом склонений и падежей при расчете разницы между двумя датами. Все получается, за исключением февраля.
Например: разница между 31.12.2021 и 28.02.2022 составляет 2 месяца 28 дней (вместо 3х месяцев), а между 31.12.2021 и 01.03.2021: 2 месяца -2 дней (дословно). Такая же ситуация с месяцами, в которых 30 дней.
Есть ли решение этой проблемы?
Спасибо за ваше замечание! Формулу подправил, добавил проверку на положительное число. Вроде все работает нормально.
Что касается интервала с 31 декабря по 28 февраля 1922 года, то здесь считается 1 день декабря, весь январь и 27 дней февраля. Вот и получается 1 мес 28 дн. Но эти 28 дней собраны из разных месяцев. Пока не придумал, что с ними можно сделать.