Расчеты в экселе с датами. Вычисление разности двух дат

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

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

Синтаксис

=DATEDIF(start_date,end_date,unit) – английская версия

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

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

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

Дополнительная информация

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

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

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

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

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

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

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

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

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

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

Как посчитать разницу между датами в Excel

Необходимо выделить цветом названия задач, у которых сроки заканчиваются уже через 7 дней. Пример таблицы плана задач:



Финальный эффект выделения окончания сроков через 7 дней:



Выделены зеленым цветом все задачи, которые должны быть выполнены через 7 дней. Если изменить значения в ячейке D2 тогда будут выделены уже другие задачи.

Полезный совет! В ячейке D2 можно использовать функцию для получения сегодняшней даты: =СЕГОДНЯ().

Формула разница дат в Excel

Формула возвращает разницу дат между сегодняшней и установленной планом в днях. Для решения данной задачи используется функция РАЗНДАТ в Excel: где найти эту формулу?

Данную функцию вы не найдете в мастере функций и даже на панели «ФОРМУЛЫ». Ее всегда нужно вводить вручную. Первым аргументом функции всегда должна быть новейшая дата, а вторым всегда – старшая дата. Третий аргумент функции определяет единицу измерения количества, которое возвращает функция =РАЗНДАТ(). В данном случае это символ "d" – дней. Это значит, что функция возвращает количество дней. Дальше следует оператор <7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.

При необходимости можно к данному диапазону добавить новое правило условного форматирования, которое будет предупреждать нас об окончании сроков за 2 недели. Для этого нужно добавить новое правило условного форматирования для диапазона A2:A7 и всего лишь немного изменить формулу в новом правиле:



Цвет форматирования для второго правила можно задать желтый. К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами». Так как у нас сначала выполняется верхнее правило следует изменить их порядок в появившемся окне: «Диспетчер правил условного форматирования». Иначе все выделенные задачи будут иметь желтую заливку ячеек. Просто выделите первое правило и нажмите кнопку вниз (CTRL+стрелка вниз), как показано на рисунке:



В результате план предупреждает нас сначала за две недели, а потом за неделю до окончания сроков выполнения задач:



Полезный совет! Если к одному и тому же диапазону присвоено много правил – соблюдайте иерархию приоритетов порядка их выполнения в диспетчере управления правилами. Чем выше правило, тем выше его приоритет выполнения по отношению к другим расположенных ниже под ним.

09.02.2016

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

Как прибавить несколько месяцев к дате Эксель

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

Например, чтобы прибавить к дате в ячейке А1 пять месяцев, мы использовали формулу: =ДАТА(ГОД(А1 );МЕСЯЦ(А1 )+5;ДЕНЬ(А1 )) . На самом деле, есть более простой и наглядный способ выполнить эту операцию – используем функцию ДАТАМЕС(Дата ; Количество_месяцев) .

У функции два обязательных аргумента :

  • Дата – исходная дата, к которой будем прибавлять заданное количество месяцев
  • Количество месяцев – число месяцев, которое нужно прибавить к дате. Если нужно вычесть месяца – укажите отрицательное число

Приведенный выше пример можно решить с помощью простой формулы: =ДАТАМЕС(А1;5) . Согласитесь, такая запись короче и легче для восприятия.

Как определить день недели в Excel

Часто нужно знать – какой день недели был (будет) в определенную дату. Как бы вы решали такую задачу? Вручную сложно, если нужно обработать несколько десятков, сотен, тысяч дат.

Воспользуйтесь функцией ДЕНЬНЕД(Дата ; Тип ) . Она возвращает порядковый номер дня недели и имеет два аргумента :

  1. Дата , для которой нужно определить день недели – обязательный аргумент
  2. Тип – необязательный параметр, который указывает какой день недели считать первым. Например, в странах восточной Европы первый день недели – понедельник, в США – воскресенье. В любом случае, формула может считать первым днем любой день недели. Если аргумент не указан – первым днем считается воскресенье. При записи формулы – Excel выведет подсказку с перечнем возможных параметров


Функция ДЕНЬНЕД в Эксель

Когда вы получили порядковый номер дня недели, можно использовать, например, функцию условия ЕСЛИ для присвоения ему текстового имени, или обработать как-то иначе.

Как определить количество дней между датами в Эксель

Нет ничего проще, чем определить количество дней между датами. Просто вычтите более позднюю дату из ранней. Например, в ячейке А1 – дата начала работы над проектом, а в А2 – дата сдачи проекта. Тогда количество дней между ними можно посчитать так: =А2-А1 .

Эту же процедуру можно выполнить с помощью функции ДНИ(Конечная дата ; Начальная дата ) . Видимой разницы между первым и вторым способами нет, они возвращают одинаковые результаты. Пользуйтесь этими способами ими по ситуации.

Как посчитать долю от года в Microsoft Excel

Если вам известен некий период, и нужно знать, какую часть календарного года он занимает, используйте функцию ДОЛЯГОДА(Начальная_дата ; Конечная_дата ; Базис ) . Как видите, у функции 3 агрумента :

  1. Начальная дата – дата старта изучаемого периода – обязательный аргумент
  2. Конечная дата – дата окончания период – обязательный аргумент
  3. Базис – базовые значения длительности года. При введении параметра программа выведет подсказку по выбору этого аргумента.

Например, проект начался 10.08.2015 и закончился 08.05.2016. Чтобы определить долю периода от календарного года, запишем формулу: =ДОЛЯГОДА(«10.08.2015″;»08.05.2016»;1) . Получим результат 0,7432. и получим 74% года.

Как получить последний день месяца

Чтобы получить дату последнего дня месяца – используйте функцию КОНМЕСЯЦА(Дата ; Количество_месяцев ) . Эта функция возвращает последний день заданной даты, или отстоящей от нее на определенное количество месяцев. Она использует 2 обязательных аргумента :

  • Дата – базовый день месяца, к которому нужно прибавить месяца и вывести последний итогового месяца
  • Количество месяцев – сколько месяцев нужно прибавить к дате. Укажите этот параметр равным нулю, если хотите получить последний день месяца, заданного в аргументом «Дата».

Как узнать номер недели в году

Если у вас есть дата, и вам нужно узнать порядковый номер этой недели в году, используйте функцию НОМНЕДЕЛИ(Дата ;Базис ).