excel пересечение интервалов дат

В качестве примера подсчета чисел возьмем список с числовыми значениями от 4 до 30 (См. файл примера ).

Будем подсчитывать значения, попадающие в интервал, например, (4;15]. Причем, границы интервала «включает [ ]» и «не включает ( )» будем выбирать из Выпадающего (раскрывающегося) списка.

Примечание: решение без выбора интервалов = СЧЁТЕСЛИМН(A2:A12;”>”&D2;A2:A12;” Предполагается, что границы интервала введены в ячейки D2 и F2. Эти ячейки не должны быть пустыми, даже если одна из границ =0. Если в диапазоне A2:A12 содержатся числовые значения в текстовом формате, то они будут проигнорированы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Аргумент «Дата» — это ваша дата, которая принадлежит к искомой неделе (обязательный аргумент)
• Базис – необязательный аргумент, указывающий какой день недели считается первым. По умолчанию для функции – это воскресенье.

Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.

Пересекаются или нет?

Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:

Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT) .

Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:

На сколько дней пересечение?

Если принципиально не просто понимать – пересекаются наши интервалы или нет, а точно знать сколько именно дней попадает в пересечение, то задача усложняется. Рассуждая логически, необходимо “прокачать” аж 3 разных ситуации в одной формуле:

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

На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.

Если условно обозначить начало первого интервала за Н1 , а конец за К1 , и начало второго за Н2 и конец за К2 , то в общем виде наша формула может быть записана как:

[expert_bq id=»1570″]Функция DAYS360 может использоваться для расчета количества дней между двумя датами на основе 360-дневного года двенадцать 30-дневных месяцев. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос (рис. 7.13).

Расчет количества дней между датами в Excel — Excel — это просто!

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

Возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ используется для исключения выходных дней или праздников при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней.