Рассчитать количество дней в году между двумя разными датами Excel

Question

У меня есть лист данных Excel, который содержит около 200 строк, в каждой строке есть даты начала и окончания. Мне нужно рассчитать разницу между датами, но за год. Предположим, что начальная дата - 3/3/2017, а конечная - 3/2/2019. Мне нужно рассчитать количество дней в 2017, 2018 и 2019 годах между обеими датами.

Answer 1

Эта формула должна работать до 25 ноября 4770 года, к этому времени Excel может быть заменен чем-то другим.

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))

где C1 содержит год, о котором идет речь.

Например:

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

Если вам действительно нужна "разница", а не количество дней от начала до конца, то добавьте один к дате начала в формуле:

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))

Изменить: (спровоцировано @ fixer1234) Чтобы понять, как это работает, вам нужно понять, что

• даты хранятся в Excel как серийные номера, обычно 1 января 1-jan-1900 равное 1.
• ROW(INDIRECT(n:m)) - это способ вернуть массив чисел, равный номерам строк, представленным n и m
• Затем функция YEAR возвращает эквивалентный год для каждого из этих значений, и мы сравниваем его с годом в верхней части соответствующего столбца, создавая массив TRUE;FALSE .
• Так, в Excel, --TRUE = 1 SUMPRODUCT эффективно возвращает SUM всех дат, которые в этом году в верхней части колонны.
• Я упомянул ограничение даты, потому что в Excel только 2^20 строк. Этого можно преодолеть, применив смещение к датам в ROW(INDIRECT(… , а затем добавив его обратно после, если соответствующие даты более поздние, чем 25.114770.