Для начисления процентов. Например, все, что рассчитывается как 30/365, накапливает проценты каждый день, кроме 31 числа месяца. Я хотел бы найти способ рассказать, как часто 31-е число происходило между двумя датами - например, между 15 марта 2017 года и сегодня было 2 31-го - 31 марта и 31 мая. Есть ли хороший способ выяснить это в Excel, кроме перечисления каждый месяц в формуле?
3 ответа
3
Вы можете использовать это:
=SUMPRODUCT(--(DAY(EOMONTH(B1,ROW(INDIRECT("1:" & (MONTH(TODAY())-MONTH(B1))+12*(YEAR(TODAY())-YEAR(B1))))-1))=31))
Для чего он возвращает последний день каждого месяца между желаемой датой и сегодняшним днем. Если оно равно 31, оно считается. SUMPRODUCT считает каждое значение true одним, а значение False - 0.
Таким образом, он считает месяцы, которые заканчиваются на 31.
2
Для интереса, даже если есть принятый ответ, это также будет работать, и немного короче:
=SUMPRODUCT(N(DAY(ROW(INDIRECT(StartDate &":"&EndDate)))=31))
Он создает массив всех дат от начала до конца включительно и считает количество дней = 31.
Этот метод столкнется с ужасной проблемой 25 Nov 4770 , если к тому времени Excel не увеличит количество строк :-)
1
Вы можете использовать тот факт, что в вашем распоряжении 7 31 сессий в год. Для даты начала в B1 и даты окончания в B2:
=(YEAR(B2)-YEAR(B1)+1)*7-INDEX({0,1,1,2,2,3,3,4,5,5,6,6},MONTH(B1))-INDEX({7,6,6,5,5,4,4,3,2,2,1,1},MONTH(B2))+(DAY(B2)=31)
Обоснование:
- Рассчитайте разницу в годах даты начала и окончания независимо от месяца. Умножьте разницу плюс 1 на 7. Это обеспечит базовый перерасчет 31-го числа, из которого мы вычтем.
- Затем найдите количество предшествующих 31-ых в том же календарном году, что и дата начала. Это обрабатывается массивом, который дает это значение для каждого месяца. Это значение вычитается из общей суммы.
- Затем выполните аналогичные действия для даты окончания, но вместо этого посмотрите количество оставшихся 31-ых в том же календарном году, что и дата окончания. Это значение также вычитается.
- Наконец, исправьте угловой случай, когда конечной датой является 31-е число месяца. В этом случае добавьте 1 к итогу.
Пример:
Начало: 9/6/2013
Конец: 12/31/2016
- Разница в годах = 2016 - 2013 = 3 -> Добавить (3 + 1) * 7 = 28 к числу 31st.
- Поиск, предшествующий 31-му в дате начала календарного года: в сентябре 5 предшествуют 31-му. -> Вычтите 5 из числа 31: 28 - 5 = 23
- Поиск оставшихся 31 числа в дате окончания календарного года: в датах декабря осталось 1 оставшееся 31-е число. -> Вычтите 1 из числа 31: 23 - 1 = 22
- Проверьте 31-ую дату окончания: Конечная дата - 31-го, поэтому добавьте 1 к числу 31-ых: 22 + 1 = 23.