Вычисление разности дат в Microsoft Excel

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

Расчет количества дней

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

Выделяем место листа, на котором вы планируете создавать вычисления. Кликаем правой клавишей мыши по выделению. Активизируется контекстное меню. В нём избираем пункт «Формат ячейки…». Как вариант, можно набрать на клавиатуре сочетание кнопок Ctrl+1.

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

Метод 1: обычное вычисление

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

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

Метод 2: функция РАЗНДАТ

Для вычисления разности в датах можно также использовать специальную функцию РАЗНДАТ. Неувязка в том, что в перечне Мастера функций её нет, потому придется вводить формулу вручную. Её синтаксис смотрится последующим образом:

«Единица» — это формат, в котором в выделенную ячейку будет выводиться итог. От того, какой знак будет подставлен в данный параметр, зависит, в каких единицах будет ворачиваться результат:

• «y» — полные года;
• «m» — полные месяцы;
• «d» — деньки;
• «YM» — разница в месяцах;
• «MD» — разница в деньках (месяцы и годы не учитываются);
• «YD» — разница в деньках (годы не учитываются).

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

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

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

Метод 3: вычисление количеств рабочих дней

В Экселе также имеется возможность произвести вычисление рабочих дней меж 2-мя датами, другими словами, исключая выходные и торжественные. Для этого употребляется функция ЧИСТРАБНИ. В отличие от предшествующего оператора, она находится в перечне Мастера функций. Синтаксис у данной нам функции последующий:

В данной нам функции главные аргументы, такие же, как и у оператора РАЗНДАТ – исходная и конечная дата. Не считая того, имеется необязательный аргумент «Празднички».

Заместо него следует подставлять даты торжественных нерабочих дней, если таковые имеются за охватываемый период. Функция производит расчет всех дней обозначенного спектра, исключая субботы, воскресенья, также те деньки, которые добавлены юзером в аргумент «Празднички».

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

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

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

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Функция РАЗНДАТ() — Вычисление разности 2-ух дат в деньках, месяцах, годах в EXCEL

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

Функции РАЗНДАТ( ) нет в справке EXCEL2007 и в Мастере функций ( SHIFT + F 3 ), но она работает, хотя и не без огрех.

Синтаксис функции:

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Аргумент начальная_дата обязана быть ранее аргумента конечная_дата .

Аргумент способ_измерения описывает, как и в каких единицах будет измеряться интервал меж исходной и конечной датами. Этот аргумент может принимать последующие значения:

Значение

Описание

разница в полных месяцах

разница в полных годах

разница в полных месяцах без учета лет

разница в деньках без учета месяцев и лет ВНИМАНИЕ! Функция для неких версий EXCEL возвращает неверное значение, если денек исходной даты больше денька конечной даты (к примеру, в EXCEL 2007 при сопоставлении дат 28.02.2009 и 01.03.2009 итог будет 4 денька, а не 1 денек). Опасайтесь использования функции с сиим аргументом. Другая формула приведена ниже.

разница в деньках без учета лет ВНИМАНИЕ! Функция для неких версий EXCEL возвращает неверное значение. Опасайтесь использования функции с сиим аргументом.

Ниже приведено подробное описание всех 6 значений аргумента способ_измерения , также других формул (функцию РАЗНДАТ() можно поменять иными формулами (правда довольно массивными). Это изготовлено в файле примера ).

В файле примера значение аргумента начальная_дата помещена в ячейке А2 , а значение аргумента конечная_дата – в ячейке В2 .

1. Разница в деньках («d»)

Формула =РАЗНДАТ(A2;B2;»d») возвратит ординарную разницу в деньках меж 2-мя датами.

Пример1: начальная_дата 25.02.2007, конечная_дата 26.02.2007 Итог: 1 (денек).

Этот пример показыват, что при подсчете стажа нужно применять функцию РАЗНДАТ() с осторожностью. Разумеется, что если сотрудник работал 25 и 26 февраля, то отработал он 2 денька, а не 1. То же относится и к расчету полных месяцев (см. ниже).

Пример2: начальная_дата 01.02.2007, конечная_дата 01.03.2007 Итог: 28 (дней)

Пример3: начальная_дата 28.02.2008, конечная_дата 01.03.2008 Итог: 2 (денька), т.к. 2008 год — високосный

Эта формула быть может заменена обычным выражением =ЦЕЛОЕ(B2)-ЦЕЛОЕ(A2) . Функция ЦЕЛОЕ() округляет значение до наименьшего целого и применена для того варианта, если начальные даты введены совместно с временем суток ( РАЗНДАТ() игнорирует время, т.е. дробную часть числа, см. статью Как Excel хранит дату и время ).

Примечание : Если заинтересовывают лишь рабочие деньки, то к оличество рабочих дней меж 2-мя датами можно посчитать по формуле =ЧИСТРАБДНИ(B2;A2)

2. Разница в полных месяцах («m»)

Формула =РАЗНДАТ(A2;B2;»m») возвратит количество полных месяцев меж 2-мя датами.

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2007 Итог: 1 (месяц)

Пример2: начальная_дата 01.03.2007, конечная_дата 31.03.2007 Итог: 0

При расчете стажа, считается, что сотрудник отработавший все деньки месяца — отработал 1 полный месяц. Функция РАЗНДАТ() так не считает!

Пример3: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Итог: 25 месяцев

Формула быть может заменена другим выражением: =12*(ГОД(B2)-ГОД(A2))-(МЕСЯЦ(A2)-МЕСЯЦ(B2))-(ДЕНЬ(B2)

Внимание : В справке MS EXCEL (см. раздел Вычисление возраста) имеется кривая формула для вычисления количества месяце меж 2-мя датами:

Если заместо функции ТДАТА() — текущая дата применять дату 31.10.1961, а в А3 ввести 01.11.1962, то формула возвратит 13, хотя практически прошло 12 месяцев и 1 денек (ноябрь и декабрь в 1961г. + 10 месяцев в 1962г.).

3. Разница в полных годах («y»)

Формула =РАЗНДАТ(A2;B2;»y») возвратит количество полных лет меж 2-мя датами.

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Итог: 2 (года)

Пример2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Итог: 1 (год)

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

Формула быть может заменена другим выражением: =ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))

4. Разница в полных месяцах без учета лет («ym»)

Формула =РАЗНДАТ(A2;B2;»ym») возвратит количество полных месяцев меж 2-мя датами без учета лет (см. примеры ниже).

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Итог: 1 (месяц), т.к. сравниваются конечная дата 01.03.2009 и измененная исходная дата 01.02. 2009 (год исходной даты заменяется годом конечной даты, т.к. 01.02 меньше чем 01.03)

Пример2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Итог: 11 (месяцев), т.к. сравниваются конечная дата 01.03.2009 и измененная исходная дата 01.04. 2008 (год исходной даты заменяется годом конечной даты за вычетом 1 года , т.к. 01.04 больше чем 01.03)

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

Формула быть может заменена другим выражением: =ОСТАТ(C7;12) В ячейке С7 обязана содержаться разница в полных месяцах (см. п.2).

5. Разница в деньках без учета месяцев и лет («md»)

Формула =РАЗНДАТ(A2;B2;»md») возвратит количество дней меж 2-мя датами без учета месяцев и лет. Применять функцию РАЗНДАТ() с сиим аргументом не рекомендуется (см. примеры ниже).

Пример1: начальная_дата 01.02.2007, конечная_дата 06.03.2009 Результат1: 5 (дней), т.к. сравниваются конечная дата 06.03.2009 и измененная исходная дата 01. 03 . 2009 (год и месяц исходной даты заменяется годом и месяцем конечной даты, т.к. 01 меньше чем 06)

Пример2: начальная_дата 28.02.2007, конечная_дата 28.03.2009 Результат2: 0, т.к. сравниваются конечная дата 28.03.2009 и измененная исходная дата 28. 03 . 2009 (год и месяц исходной даты заменяется годом и месяцем конечной даты)

Пример3: начальная_дата 28.02.2009, конечная_дата 01.03.2009 Результат3: 4 (денька) — совсем непонятный и НЕПРАВИЛЬНЫЙ итог. Ответ должен быть =1. Наиболее того, итог вычисления зависит от версии EXCEL.

Версия EXCEL 2007 с SP3:

Итог – 143 денька! Больше чем дней в месяце!

Версия EXCEL 2007:

Разница меж 28.02.2009 и 01.03.2009 – 4 денька!

При этом в EXCEL 2003 с SP3 формула возвращает верный итог 1 денек. Для значений 31.12.2009 и 01.02.2010 итог совершенно отрицательный (-2 денька)!

Не советую применять формулу с вышеуказанным значением аргумента. Формула быть может заменена другим выражением: =ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2); ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2); ДЕНЬ(B2)-ДЕНЬ(A2))

Данная формула только эквивалетное (в большинстве случаев) выражение для РАЗНДАТ() с параметром md. О правильности данной нам формуле читайте в разделе «Снова о кривизне РАЗНДАТ()» ниже.

6. Разница в деньках без учета лет («yd»)

Формула =РАЗНДАТ(A2;B2;»yd») возвратит количество дней меж 2-мя датами без учета лет. Применять ее не рекомендуется по причинам, изложенным в прошлом пт.

Итог, возвращаемый формулой =РАЗНДАТ(A2;B2;»yd») зависит от версии EXCEL.

Формула быть может заменена другим выражением: =ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))>B2; B2-ДАТА(ГОД(B2)-1;МЕСЯЦ(A2);ДЕНЬ(A2)); B2-ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2)))

Снова о кривизне РАЗНДАТ()

Найдем разницу дат 16.03.2015 и 30.01.15. Функция РАЗНДАТ() с параметрами md и ym подсчитает, что разница составляет 1 месяц и 14 дней. Так ли это по сути?

Имея формулу, эквивалентную РАЗНДАТ() , можно осознать ход вычисления. Разумеется, что в нашем случае количество полных месяцев меж датами = 1, т.е. весь февраль. Для вычисления дней, функция находит количество дней в прошлом месяце относительно конечной даты, т.е. 28 (конечная дата принадлежит марту, предшествующий месяц — февраль, а в 2015г. в феврале было 28 дней). Опосля этого отбирает денек начала и добавляет денек конечной даты = ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B6;-1);0))-ДЕНЬ(A6)+ДЕНЬ(B6) , т.е. 28-30+16=14. На наш взор, меж датами все таки 1 полный месяц и все деньки марта, т.е 16 дней, а не 14! Эта ошибка проявляется, когда в прошлом месяце относительно конечной даты, дней меньше, чем дней исходной даты. Как выйти из данной нам ситуации?

Модифицируем формулу для расчета дней различия без учета месяцев и лет:

При применении новейшей функции нужно учесть, что разница в деньках будет схожей для нескольких исходных дат (см. набросок выше, даты 28-31.01.2015). В других вариантах формулы эквивалентны. Какую формулу использовать? Это решать юзеру в зависимости от условия задачки.

Функция DATEDIF (РАЗНДАТ) в Excel. Как применять?

Функция DATEDIF (РАЗНДАТ) в Excel употребляется для подсчета количества лет, месяцев либо дней меж 2-мя датами. Неплохой пример – подсчет возраста.

Что возвращает функция

Число, обозначающее количество лет/месяцев/дней меж 2-мя датами. Какие значения будут отображаться (год, месяц либо деньки) зависит от того, что будет обозначено вами в атрибутах функции.

Синтаксис

=DATEDIF(start_date,end_date,unit) – британская версия

=РАЗНДАТ(нач_дата ; кон_дата ; единица) – российская версия

Аргументы функции

• start_date (нач_дата): дата, с которой начинается вычисление различия во времени меж 2-мя датами. Дата быть может указана как текст в кавычках, числовое значение, либо итог какого-нибудь вычисления (к примеру в функции ДАТА());
• end_date (кон_дата): дата, на которой вычисление различия во времени меж 2-мя датами будет окончено. Она, также, быть может указана как текст в кавычках, числовое значение, либо итог какого-нибудь вычисления;
• unit (единица): Этот аргумент описывает в каком измерении будет отображена разница меж 2-мя датами (год, месяц, денек). Существует 6 разных типов отражения данных:

• “Y” – возвращает данные в количестве лет меж 2-мя датами;
• “M” – возвращает данные в количестве месяцев меж 2-мя датами;
• “D” – возвращает данные в количестве дней меж 2-мя данными;
• “MD” – возвращает данные в количестве дней меж 2-мя датами, но не учитывает целые годы, месяца, которые уже прошли при расчете данных. К примеру, если вы рассчитываете количество дней меж 2-мя датами: 1 Января 2017 и 3 Марта 2017, функция выдаст значение “2”, потому что не будет учесть два прошедших полных месяцев (Январь, Февраль);
• “YM” – возвращает данные в количестве месяцев меж 2-мя датами, но не учитывает целые годы, которые уже прошли меж 2-мя расчетными данными;
• “YD” – возвращает данные в количестве дней меж 2-мя датами, но не учитывает целые годы, которые уже прошли меж 2-мя расчетными датами.

Доборная информация

• При вводе функции в ячейку, она не отображается в резвом меню выбора функций. Тем не наименее функция работает во всех версиях Excel. Принципиально знать аргументы функции и как ими воспользоваться;
• Данные даты в функции могут быть введены как:
  – дата обозначенная в виде текста в кавычках;
  – число;
  – как итог вычисления, формулы, функции.

Примеры использования функции РАЗНДАТ в Excel

Пример №1. Подсчет количества лет меж 2-мя датами

На примере выше, формула возвращает количество лет меж 2-мя датами. К примеру, функция возвратит значение “27” если вы возжелаете посчитать количество полных лет меж 2-мя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней меж датами.

Пример №2. Подсчет количества месяцев меж 2-мя датами

На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество полных месяцев меж 2-мя датами – 19 июня 1989 года и 26 марта 2017 года.

Функция возвращает значение “333”, что является количеством полных месяцев меж датами обозначенными выше.

Пример №3. Подсчет количества дней меж 2-мя датами

На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество дней меж 2-мя датами. К примеру, функция возвращает значение “10142” денька меж 2-мя датами – 19 июня 1989 года и 26 марта 2017 года.

Если вы желаете посчитать количество дней меж 2-мя датами, исключая количество лет, которые уже завершены, для вас необходимо применять аргумент “YD” в качестве третьего (пример на рисунке ниже):

В приведенном выше примере он возвращает значение “280”, другими словами число дней меж деньками, исключая 27 полных лет.

Если вы желаете посчитать количество дней меж 2-мя датами, исключая количество полных лет и месяцев, используйте атрибут “MD” в качестве третьего аргумента (как показано на рис. ниже):

На примере выше функция возвращает значение “7” означающие количество дней меж 2-мя деньками “19” и “26” число, исключая значения лет и месяцев.

Еще более нужных приемов в работе со перечнями данных и функциями в Excel вы узнаете в практическом курсе “От новенького до мастера Excel“. Успей зарегистрироваться по ссылке!