Интервал дат в 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") вернет простую разницу в днях между двумя датами.
Пример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 хранит дату и время ).
Примечание : Если интересуют только рабочие дни, то к оличество рабочих дней между двумя датами можно посчитать по формуле =ЧИСТРАБДНИ(B2;A2)
2. Разница в полных месяцах ("m")
Формула =РАЗНДАТ(A2;B2;"m") вернет количество полных месяцев между двумя датами.
Пример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") вернет количество полных лет между двумя датами.
Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 2 (года)
Пример2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Результат: 1 (год)
Подробнее читайте в статье Полный возраст или стаж .
Формула может быть заменена альтернативным выражением: =ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))
4. Разница в полных месяцах без учета лет ("ym")
Формула =РАЗНДАТ(A2;B2;"ym") вернет количество полных месяцев между двумя датами без учета лет (см. примеры ниже).
Пример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") вернет количество дней между двумя датами без учета месяцев и лет. Использовать функцию РАЗНДАТ() с этим аргументом не рекомендуется (см. примеры ниже).
Пример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") вернет количество дней между двумя датами без учета лет. Использовать ее не рекомендуется по причинам, изложенным в предыдущем пункте.
Результат, возвращаемый формулой =РАЗНДАТ(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 дней. Так ли это на самом деле?
Модифицируем формулу для расчета дней разницы без учета месяцев и лет:
Пусть задана начальная дата (ячейка B7 ) и длительность периода (ячейка B8 ), выведем диапазон дат в одной ячейке в формате 21-25.10.2012 (см. файл примера ).
Это можно сделать с помощью формулы = ТЕКСТ(B7;"дд")&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")
Совет : О пользовательском формате дат можно прочитать в статье Пользовательский формат ДАТЫ и ВРЕМЕНИ в MS EXCEL
Это решение, однако, не учитывает, что начальная и конечная дата могут принадлежать разным месяцам. В этом случае формула может вернуть, например 21-05.10.2012, что довольно сложно для понимания. Поэтому, изменим формулу: =ТЕКСТ(B7;ЕСЛИ(МЕСЯЦ(B7)-МЕСЯЦ(B7+B8);"дд.ММ";"дд"))&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")
Теперь. если начальная и конечная даты принадлежат разным месяцам, то у начальной даты будет дополнительно выводиться месяц.
Диапазон с указанием месяца и года
Более простым случаем является вывод начальной даты с указанием месяца и года (что позволяет корректно учесть случай, когда даты принадлежат разным годам). В этом случае нужно использовать формулу = ТЕКСТ(B13;"дд.ММ.гггг")&"-"&ТЕКСТ(B13+B14;"дд.ММ.гггг")
В формуле предполагается, что начальная дата введена в ячейку B13 , а длительность периода введена в ячейку B14 .
Диапазон в пределах 1 месяца
Иногда требуется, чтобы начальная и конечная даты принадлежали одному месяцу. В этом случае в формуле необходимо провести проверку: не выходит ли конечная дата за границы месяца. Если конечная дата выходит за границы месяца, то она заменяется последней датой месяца.
= ЕСЛИ(B19=КОНМЕСЯЦА(B19;0);"Последний день месяца";ТЕКСТ(B19;"дд")&"-"&ТЕКСТ(B19+МИН(B20;КОНМЕСЯЦА(B19;0)-B19);"дд.ММ.гггг"))
В формуле предполагается, что начальная дата введена в ячейку B19 , а длительность периода введена в ячейку B20 .
Т.к. каждому значению даты соответствует определенное число (см. статью Как EXCEL хранит дату и время ), то подходы для формирования последовательностей, изложенные в статье Числовые последовательности , применимы и для дат. Однако, имеются и свои особенности.
Последовательность 01.01.09, 01.02.09, 01.03.09 (первые дни месяцев) можно сформировать формулой =ДАТАМЕС(B2;СТРОКА(A1)) , в ячейке B2 должна находиться дата - первый элемент последовательности ( 01.01.09 ).
Эту же последовательность можно сформировать используя правую клавишу мыши. Пусть в ячейку A2 введено значение 01.01.09 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши, появится контекстное меню, в котором нужно выбрать пункт Заполнить по месяцам .
Изменив формат ячеек, содержащих последовательность 01.01.09, 01.02.09, 01.03.09, на МММ (см. статью Пользовательский формат дат ) получим последовательность янв, фев, мар, .. .
Эту же последовательность можно ввести используя список автозаполения Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки (введите янв , затем Маркером заполнения скопируйте вниз).
Ячейки будут содержать не даты, как в предыдущем случае, а текстовые значения.
Аналогичным образом можно сформировать последовательность дней недели пн, вт, ср, .
Последовательность кварталов 1 кв., 2 кв. можно сформировать используя идеи из статьи Текстовые последовательности .
Используя инструмент Прогрессия , можно создать последовательности только рабочих дней. А также с шагом месяц и год.
Последовательность первых месяцев кварталов янв, апр, июл, окт, янв, . можно создать введя в две ячейки первые два элемента последовательности ( янв, апр ), затем (предварительно выделив их) скопировать вниз маркером заполнения . Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу =ДАТАМЕС($G$16;(СТРОКА(A2)-СТРОКА($A$1))*3) Предполагается, что последовательность начинается с ячейки G16 , формулу нужно ввести в ячейку G17 (см. файл примера ).
Временную последовательность 09:00, 10:00, 11:00, . можно сформировать используя Маркер заполнения . Пусть в ячейку A2 введено значение 09 : 00 . Выделим ячейку A2 . Скопируем Маркером заполнения , значение из A2 в ячейки ниже. Последовательность будет сформирована.
Если требуется сформировать временную последовательность с шагом 15 минут ( 09:00, 09:15, 09:30, . ), то можно использовать формулу =B15+1/24/60*15 (Предполагается, что последовательность начинается с ячейки B15 , формулу нужно ввести в B16 ). Формула вернет результат в формате даты.
Другая формула =ТЕКСТ(B15+1/24/60*15;"чч:мм") вернет результат в текстовом формате.
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О числовых последовательностях вида 1, 2, 3, . 1, 3, 5, 7, . I, II, III, IV, . можно прочитать в статье Числовые последовательности .
Как показано на скриншоте ниже, как можно рассчитать интервал дней, недель, месяцев или лет между двумя заданными датами в Excel? Эта статья покажет вам формулы для этого.
Вычислить интервал дней / недель / месяцев / лет между двумя датами с помощью формул
Пожалуйста, примените приведенные ниже формулы для вычисления интервала дней, недель, месяцев или лет между двумя заданными датами в Excel.
Рассчитать интервал дней
Выберите пустую ячейку, введите в нее формулу ниже и нажмите Enter ключ. Перетащите формулу вниз, чтобы применить ее к нужным ячейкам.
=DATEDIF(A2, B2, "d")
Рассчитать интервал недель
Выберите пустую ячейку, введите в нее формулу ниже и нажмите Enter ключ, чтобы получить интервал недель между двумя датами. Затем перетащите формулу вниз, чтобы применить ее к нужным ячейкам.
=ROUNDDOWN((DATEDIF(A2, B2, "d") / 7), 0)
Рассчитать интервал месяцев
Для расчета интервала в месяцах между двумя заданными датами, пожалуйста, примените формулу ниже.
=DATEDIF(A2, B2, "m")
Рассчитать интервал лет
Эта формула может помочь для получения интервальных лет.
=DATEDIF(A2,B2,"y")
Внимание: В формулах ячейки A2 и B2 содержат заданную дату начала и дату окончания. Пожалуйста, измените их по своему усмотрению.
Легко рассчитайте интервалы дней / месяцев / лет между двумя датами с помощью удивительного инструмента
Здесь рекомендую Помощник по дате и времени особенность Kutools for Excel. С помощью этой функции вы можете легко рассчитать интервал дней, месяцев или лет между двумя датами всего за несколько кликов.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для вывода результата, а затем щелкните Кутулс > Формула Помощник > Помощник по дате и времени. Смотрите скриншот:
2. в Помощник по дате и времени диалоговое окно, настройте следующим образом.
2.3). Date2 в поле выберите дату окончания, на основе которой вы будете рассчитывать интервальные дни;
Советы: Если вы хотите рассчитать интервальные дни, выберите день вариант, для расчета интервальных месяцев выберите месяц, для расчета интервальных лет выберите год.
3. Затем результат заносится в выбранную ячейку, а формула создается автоматически. Вы можете перетащить маркер заполнения в другие ячейки, чтобы получить результаты.
Внимание: Повторите вышеуказанные шаги, чтобы получить интервал в месяцах или годах между двумя датами. Окончательный результат показан ниже:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Используйте функцию РАЗДАТ, если требуется вычислить разницу между двумя датами. Сначала поместите дату начала в ячейку, а дату окончания в другую. Введите формулу, как по одной из следующих формул:
Разница в днях
В этом примере начальная дата находится в ячейке D9, а даты окончания — в ячейке E9. Формула находится в F9. "d" возвращает количество полных дней между двумя датами.
Разница в неделях
В этом примере начальная дата находится в ячейке D13, а даты окончания — в ячейке E13. "d" возвращает количество дней. Однако обратите внимание на /7 в конце. При этом количество дней делится на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также должен быть отформатирован как число. Нажмите CTRL+1. Затем щелкните Число>десятичных числах: 2.
Разница в месяцах
В этом примере начальная дата находится в ячейке D5, а даты окончания — в ячейке E5. В формуле "м" возвращает количество полных месяцев между двумя днями.
Разница в годах
В этом примере начальная дата находится в ячейке D2, а даты окончания — в ячейке E2. "y" возвращает количество полных лет между двумя днями.
Расчет возраста в накопленных годах, месяцах и днях
Вы также можете вычислить возраст или время службы другого человека. Результат может выглядеть так: "2 года, 4 месяца, 5 дней".
1. Чтобы найти общее количество лет, используйте dateDIF.
В этом примере дата начала находится в ячейке D17, а даты окончания — в ячейке E17. В формуле "y" возвращает количество полных лет между двумя днями.
2. Чтобы найти месяцы, снова используйте DATEDIF с "м".
В другой ячейке используйте формулу РАЗНОМЕР с параметром "м". "м" возвращает количество оставшихся месяцев за последний полный год.
3. Используйте другую формулу для поиска дней.
Теперь нужно найти количество оставшихся дней. Для этого мы напишем формулу другого типа, как показано выше. Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.
Предупреждение: Не рекомендуется использовать аргумент "мд" РАЗДАТ.
4. Необязательно: объединение трех формул в одну.
Все три вычисления можно поместить в одну ячейку, как в этом примере. Используйте амперанд, кавычка и текст. Это длинная формула, но по крайней мере она все в одной формуле. Совет. Нажмите ALT+ВВОД, чтобы ввести разрывы строк в формулу. Это упрощает чтение. Кроме того, если вы не видите всю формулу, нажмите CTRL+SHIFT+U.
Скачивание примеров
Вы можете скачать образец книги со всеми примерами из этой статьи. Вы можете следовать за ним или создать собственные формулы.
Другие вычисления даты и времени
Как было посчитано выше, функция РАЗДАТ.В.ДВ вычисляет разницу между датой начала и датой окончания. Однако вместо ввода определенных дат в формуле можно использовать функцию СЕГОДНЯ(). При использовании функции СЕГОДНЯ() Excel для даты используется текущая дата вашего компьютера. Имейте в виду, что эта переменная будет меняться при повторном открыть файле в будущем.
Обратите внимание, что на момент написания статьи день был 6 октября 2016 г.
Используйте NETWORKDAYS. Функция INTL, если вы хотите вычислить количество рабочих дней между двумя датами. Вы также можете исключить выходные дни и праздники.
Прежде чем начать: Решите, нужно ли исключить даты праздников. В этом случае введите список дат праздников в отдельной области или на отдельном листе. Поместите каждую праздниковую дату в свою ячейку. Затем выберем эти ячейки и выберем формулы > определить имя. Привяжем диапазон MyHolidaysи нажмите кнопку ОК. Затем создайте формулу с помощью действий ниже.
1. Введите даты начала и окончания.
В этом примере дата начала находится в ячейке D53, а даты окончания — в ячейке E53.
2. В другой ячейке введите формулу такого вида:
Введите формулу, как в примере выше. Число 1 в формуле устанавливает субботы и воскресенье в качестве выходных и исключает их из общего числа.
Примечание. Excel 2007 не имеет networkDAYS. Функция INTL. Однако в нем есть NETWORKDAYS. В этом примере в Excel 2007 г. будет выглядеть так: =NETWORKDAYS(D53;E53). Вы не указываете 1, так как в networkDAYS предполагается, что выходные — в субботу и воскресенье.
3. При необходимости измените 1.
Если суббота и воскресенье не являются выходными, измените число 1 в списке IntelliSense. Например, 2 дня по понедельникам и понедельникам являются выходными.
Если вы используете Excel 2007, пропустите этот шаг. Excel 2007 г. функция NETWORKDAYS всегда предполагает, что выходные дни — в субботу и воскресенье.
4. Введите название диапазона праздников.
Если вы создали имя диапазона праздников в разделе "Начало работы" выше, введите его в конце так: Если у вас нет праздников, оставьте запятую и запятую MyHolidays. Если вы используете Excel 2007, пример будет такой: =ДНЕЙМН(D53;E53;MyHolidays).
Совет: Если вы не хотите ссылаться на название диапазона праздников, можно также ввести диапазон, например D35:E:39. Можно также ввести в формулу каждый праздник. Например, если ваши праздники были 1 и 2 января 2016 г., введите их так: =СЕТЕВЫЕДНИ. INTL(D53;E53;1;). В Excel 2007 г. она будет выглядеть так: =ДНЕЙМН(D53;E53;)
Вы можете вычесть затращенное время, вычитая одно время из другого. Сначала поместите время начала в ячейку, а время окончания в другую. Обязательно введите полные часы, минуты и пробелы до am или PM. Ниже рассказывается, как это сделать.
1. Введите время начала и окончания.
В этом примере время начала находится в ячейке D80, а время окончания — в ячейке E80. Введите часы, минуты и пробелы до am или PM.
2. Задать формат ч:мм.AM/PM.
Выберите обе даты и нажмите CTRL+1 (или + 1 на Компьютере Mac). Выберите настраиваемый >ч:мм/PM, если он еще не установлен.
3. Вычитать два раза.
В другой ячейке вычитайте ячейку времени начала из ячейки времени окончания.
4. За установите формат ч:мм.
Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите Настраиваемые > ч:мм, чтобы исключить из результатов am и PM.
Чтобы вычислить время между двумя датами и временем, можно просто вычесть одну из другой. Однако необходимо применить форматирование к каждой ячейке, чтобы Excel возвращал нужный результат.
1. Введите две полные даты и время.
В одной ячейке введите полную дату и время начала. В другой ячейке введите дату и время окончания. В каждой ячейке до am или PM должен быть месяц, день, год, час, минута и пробел.
2. Задать формат 14.03.2012, 13:30.
Выберем обе ячейки и нажмите CTRL+1 (или + 1 на компьютере Mac). Затем выберите Дата >14.03.2012, 13:30. Это не та дата, которая будет заданной вами, а только пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другой пример даты, например14.03.2011 13:30.
3. Вычитать эти два.
4. За установите формат [ч]:мм.
Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите пункт (все форматы). В поле Тип введите [ч]:мм.
Читайте также: