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

На рабочем листе 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 обязательных аргумента :

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

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

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

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

Разность в днях

В этом примере начальная дата находится в ячейке D9, а конечная - в ячейке E9. Формула помещена в ячейку F9. Параметр "d" возвращает количество полных дней между двумя датами.

Разность в неделях



В этом примере начальная дата находится в ячейке D13, а конечная - в ячейке E13. Параметр "d" возвращает количество дней. Но в конце формулы указано выражение /7 . Это выражение делит количество дней на 7, поскольку в неделе 7 дней. Учтите, что этот результат также должен иметь числовой формат. Нажмите клавиши CTRL+1. Затем выберите Числовой > Число десятичных знаков: 2 .

Разность в месяцах



В этом примере начальная дата находится в ячейке D5, а конечная - в ячейке E5. Параметр "m" в формуле возвращает количество полных месяцев между двумя датами.

Разность в годах



В этом примере начальная дата находится в ячейке D2, а конечная - в ячейке E2. Параметр "y" возвращает количество полных лет между двумя датами.

Вычисление возраста в годах, месяцах и днях

Можно также вычислить возраст или срок службы. Результат при этом будет иметь следующий вид: "2 года, 4 месяца, 5 дней".

1. Используйте функцию РАЗНДАТ для вычисления суммарного количества лет.



В этом примере начальная дата находится в ячейке D17, а конечная - в ячейке E17. Параметр "y" в формуле возвращает количество полных лет между двумя датами.

2. Используйте функцию РАЗНДАТ с параметром "ym" для вычисления количества месяцев.



В еще одну ячейку поместите формулу РАЗНДАТ с параметром "ym" . Параметр "ym" возвращает количество месяцев, остающихся после последнего полного года.

3. Используйте другую формулу, чтобы найти дни.



Теперь нужно определить количество оставшихся дней. Мы сделаем это, написав другую формулу, чем приведенная выше. Эта формула вычитает первый день конечного месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Ниже описано, как это делается. Сначала, функция ДАТА создает дату, 01.05.2016. Она делает это на основе года и месяца в ячейке E17 (число 1 означает первый день месяца). Функция ДАТА отображает 01.05.2016. Затем мы вычитаем эту дату из исходной конечной даты в ячейке E17, т. е. 06.05.2016. "06.05.2016" минус "01.05.2016" равняется 5 дням.

4. Необязательно: объедините три формулы в одну.



Все три вычисления можно поместить в одну ячейку, как в этом примере. Можно использовать амперсанды, кавычки и текст. Такую формулу дольше вводить, но зато здесь все вычисления в одном месте. Совет. Чтобы добавить в формулу разрыв строки, нажмите клавиши ALT+ВВОД. Это упростит чтение. Если не видно всю формулу целиком, нажмите клавиши CTRL+SHIFT+U.

Скачивание образцов

Вы можете скачать образец книги со всеми примерами из этой статьи и использовать как готовые, так и свои собственные формулы.

Другие вычисления даты и времени

Разность между сегодняшней и другой датой

Как было показано выше, функция РАЗНДАТ вычисляет разность между начальной и конечной датами. Но вместо ввода конкретной даты можно использовать в формуле функцию СЕГОДНЯ() . При этом Excel в качестве даты берет текущую дату компьютера. Помните, что она изменится, когда файл будет снова открыт в другой день.



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

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

Перед началом. Решите, нужно ли исключить праздничные даты. Если да, введите список праздничных дней в отдельной области или на отдельном листе. Поместите каждую праздничную дату в отдельную ячейку. Затем выделите эти ячейки, выберите Формулы > Присвоить имя . Присвойте диапазону имя МоиПраздники и нажмите кнопку ОК . Затем создайте формулу, выполнив указанные ниже действия.

1. Введите начальную и конечную даты.



В этом примере начальная дата находится в ячейке D53, а конечная - в ячейке E53.

2. Еще в одной ячейке введите формулу такого вида:



Введите формулу, как в приведенном выше примере. Параметр "1" в формуле назначает субботы и воскресенья выходными днями и исключает их из общей суммы.

Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Но есть функция ЧИСТРАБДНИ. Функция из примера выше в Excel 2007 имела бы такой вид: =ЧИСТРАБДНИ(D53;E53) . Не нужно указывать 1, потому что функция ЧИСТРАБДНИ по умолчанию считает субботу и воскресенье выходными днями.

3. При необходимости измените параметр "1".



Если у вас выходные дни не в субботу и воскресенье, укажите вместо "1" другое число из списка IntelliSense. Например, "2" назначает выходными днями воскресенья и понедельники.

Если у вас Excel 2007, пропустите это действие. Функция ЧИСТРАБДНИ в Excel 2007 всегда считает выходными днями субботу и воскресенье.

4. Введите имя диапазона праздничных дней.



Если вы присвоили имя диапазону праздничных дней в соответствии с инструкциями раздела "Перед началом", введите в конце это имя, как здесь. Если у вас нет праздничных дней, можно оставить точку с запятой и не указывать МоиПраздники. В Excel 2007 формула из примера выше примет вид: =ЧИСТРАБДНИ(D53;E53;МоиПраздники) .

Совет. Если вы не хотите ссылаться на имя диапазона праздничных дней, можете вместо него указать диапазон D35:E:39 . Можно также ввести в формулу каждый праздничный день. Например, если праздничные дни приходятся на 1 и 2 января 2016 г., введите их следующим образом: =ЧИСТРАБДНИ.МЕЖД(D53;E53;1;{"01.01.2016";"02.01.2016"}) . В Excel 2007 нужно ввести: =ЧИСТРАБДНИ(D53;E53;{"01.01.2016";"02.01.2016"})

Вычисление затраченного времени

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

1. Введите начальное и конечное время.



В этом примере начальное время находится в ячейке D80, а конечное - в ячейке E80. Обязательно укажите полное значение времени, включая часы и минуты.

2. Задайте формат чч:мм.



Выберите обе даты и нажмите клавиши CTRL+1 (или +1 на компьютере с Mac OS). Выберите формат (все форматы) > чч:мм , если он еще не задан.

3. Вычислите разность двух значений времени.



В еще одной ячейке вычтите значение ячейки начального времени из значения ячейки конечного времени.

4. Задайте формат чч:мм.



Нажмите клавиши CTRL+1 (или +1 на компьютере с Mac OS). Выберите (все форматы) > чч:мм .

Вычисление затраченного времени между двумя датами и значениями времени

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

Функция 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 года.



Рекомендуем почитать