Пример данных:
Привет, я изо всех сил пытаюсь найти правильную формулу Excel, чтобы вернуть количество дней в определенном году в диапазоне дат. Был похожий пост в 2010 году, но он не работал для меня.
Пример данных:
Привет, я изо всех сил пытаюсь найти правильную формулу Excel, чтобы вернуть количество дней в определенном году в диапазоне дат. Был похожий пост в 2010 году, но он не работал для меня.
Формула решения довольно проста:
Введите следующую формулу в 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
))))
Ключом к решению проблемы является разделение ее на пять отдельных случаев:
РЕДАКТИРОВАТЬ:
Наблюдение за решением 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)))
Я вижу простое решение, которое не требует 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)))
Попробуйте следующую пользовательскую функцию:
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) очень просты в установке и использовании:
Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx
Чтобы удалить UDF:
Чтобы использовать 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
Макросы должны быть включены, чтобы это работало!