Расчет количества дней в определенном году в пределах диапазона

Question

Пример данных:

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

Answer 1

Формула решения довольно проста:

Введите следующую формулу в D2 и ctrl-enter/copy-paste/fill-down в D2:K4:

=
IF(AND(YEAR(D$1)=YEAR($A2),YEAR(D$1)=YEAR($B2)),$B2-$A2+1,
IF(YEAR(D$1)=YEAR($A2),EDATE(D$1,12)-$A2,
IF(YEAR(D$1)=YEAR($B2),$B2-D$1+1,
IF(AND(YEAR(D$1)>YEAR($A2),YEAR(D$1)<YEAR($B2)),EDATE(D$1,12)-D$1,
0
))))

Ключом к решению проблемы является разделение ее на пять отдельных случаев:

1. Начальный и конечный годы соответствуют соответствующему году
2. Только стартовые года матчей
3. Только финишные матчи
4. Рассматриваемый год находится между годами начала и окончания
5. Этот год не входит в начало и конец года

---

РЕДАКТИРОВАТЬ:

Наблюдение за решением fixer1234 заставило задуматься, почему я отказался от своей первоначальной идеи использования MIN() и MAX() для решения проблемы - по-видимому, без веской причины.

Вот решение, которое я должен был изначально предложить:

=(EDATE(D$1,12)>=$A2)*(D$1<=$B2)*(MIN($B2,EDATE(D$1,12))-MAX($A2,D$1)+(YEAR(D$1)=YEAR($B2)))

Answer 2

Я вижу простое решение, которое не требует VBA (это длинный ответ для простого решения, потому что оно включает в себя варианты, охватывающие несколько вещей, которые неоднозначны в вопросе).

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

Определение количества дней

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

Начало и конец - просто даты против даты / времени

Если диапазоны Start и Finish являются просто датами, диапазон будет храниться в единицах целых дней, поэтому вы можете использовать простую арифметику для расчета длительностей. Если они на самом деле хранятся как дата / время, а вы просто отображаете часть даты, скрытая информация о времени может вызвать неожиданные ошибки округления. Я покажу решение для обоих случаев.

Решения: основа длительности

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

Если диапазон Start и Finish - просто даты, а цель - "duration", формула в D2 (перетащите, чтобы заполнить матрицу):

=IF(OR($A2>=E$1,$B2<D$1),0,MIN($B2,E$1)-MAX($A2,D$1))

Если диапазон Start и Finish содержит информацию о дате / времени, а целью является "продолжительность", формула в D2:

=IF(OR($A2>=E$1,$B2<D$1),0,DATEDIF(MAX($A2,D$1),MIN($B2,E$1),"D"))

Встроенная функция DATEDIF дает вам разницу между датами (игнорируя информацию о времени), и вы можете указать отображаемые единицы измерения.

объяснение

В этих двух формулах тест IF проверяет, является ли дата начала после конца текущего года или дата окончания до текущего года. Если это так, он заполняет ноль.

В противном случае рассчитывается разница дат. Он использует более позднюю часть начала года (продолжение диапазона предыдущего года) или начало диапазона (начинается где-то в течение года), а также более раннюю часть конца диапазона (заканчивается в течение года) или начало периода. в следующем году (диапазон продолжается в следующем году).

Решение: Основа подсчета дней

Если ваша цель состоит в том, чтобы считать каждый день в диапазоне как полный день, нам нужно добавить один день к прошлогодней записи для диапазона. Простой способ сделать это с помощью логического значения (true = 1, false = 0) теста на то, равен ли год окончания "текущему" году: (YEAR($B2)=YEAR(D$1)) , который можно добавить к любой из приведенных выше формул. Используя первую формулу, например:

=IF(OR($A2>=E$1,$B2<D$1),0,MIN($B2,E$1)-MAX($A2,D$1)+(YEAR($B2)=YEAR(D$1)))

Answer 3

Попробуйте следующую пользовательскую функцию:

Public Function DateCounter(StartDate As Date, EndDate As Date, yeer As Long) As Long
    Dim d As Date
    DateCounter = 0

    For d = StartDate To EndDate
        If Year(d) = yeer Then DateCounter = DateCounter + 1
    Next d
End Function

Первый аргумент - дата начала, второй аргумент - дата окончания, а третий аргумент - год. Например:

Таким образом, общее количество дней между (и включая) 20 ноября 2016 года и 15 марта 2017 года, которые приходятся на 2016 год, составляет 42.

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=myfunction(A1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!