Предыстория
Как известно «Лень – двигатель прогресса». В своей работе однажды я столкнулся задачей, когда нужно было составить таблицу расчёта процентов по договору займа, где за базу должно было быть фактическое количество дней в году. Неудобство составляло то, что нужно было не забыть про високосные года и разделять дни, которые относятся к високосному году и дни не високосных лет. Была написана простенькая формула, но позже я выяснил, что расчётом високосных лет не всё так просто.
Описание проблемы
Мне захотелось улучшить формулу. В интернете я нашёл много текстов программ, где вычислялось количество високосных или невисокосных лет и дней в периоде. Но, к сожалению, мен не устраивал тот факт, что скорость работы данных функций зависела от количества лет в периоде. А хотелось, чтобы независимо от того, сколько лет в периоде, функция работала также быстро. Но в ходе разработки мне пришлось ограничить допустимый период работы функции.
Причина 1
Большинство стран живут по Григорианскому календарю, правила високосных лет для которого были определены ещё 1582 году римским папой Григорием XIII:
1. Год, номер которого кратен 400, - високосный;
2. Остальные годы, номер которых кратен 100, - невисокосные (например, годы 1700, 1800, 1900, 2100, 2200,2300)
3. Остальные годы, номер которых кратен 4, - високосные.
Также существует неоднозначность в мнениях определения високосных лет таких как 2900, 3200, 4000, поэтому я решил ограничить функцию максимальной датой 01.01.2900.
Причина 2
Пользовательская функция в Excel создаётся на языке VBA (Visual Basic for Applications). Несмотря на то, что интерпретатор данного языка встроен в MS Office, я обнаружил некоторые отличия в работе с датами.
Excel поддерживает две системы дат, так называемые системы 1900 и 1904. По умолчанию используется система 1900. Это означает, что число 1 введённое в ячейку соответствует 01 января 1900 года, 2 – 2 января и так далее.
В VBA есть функция CDate(expression), которая приводит к типу Date введённое значение. И если этой функции передать число 1, то она вернёт переменную типа Date с датой 31 декабря 1899 года. А вот для числа 60 функция CDate вернёт 28.02.1900, а то же значение введённое в ячейку будет отображать 29.02.1900 (хотя, конечно, 1900 год високосным не является). Далее, начиная с 01 марта 1900 года значения дат выравниваются.
Такое поведение Excel, давно известно компании Microsoft и было принято решение оставить как есть, нужно просто учитывать его. Поэтому и появилось второе ограничение минимальной даты периода 01 марта 1900 года.
Алгоритм решения
Для того, чтобы скорость вычисления количества (не)високосных не зависела от количества дней между датами мне нужно было разработать некую формулу или алгоритм, где не было бы никаких циклов.
Так как все делители, с помощью которых мы можем определить високосность года кратны 4, то я решил разбить все годы на блоки по 4 (квартеты) начиная с 1 года. То есть 1-й блок начинается с 1 года и заканчивается 4, 2-й блок с 5 по 8 и так далее.
В каждом блоке год будет иметь свой индекс от 1 до 4 (например, 2021 год это 506-й блок, индекс в блоке 1)
Теперь мы можем разделить вычисление на 3 блока:
В зависимости от года параметров и индексов квартетов формула расчёта количества дней високосного года будет следующей:
Если год начальной и конечной даты равны и год високосный:
Если год начальной даты невисокосный, а конечной високосный и индексы квартета начальной даты и конечной равны, то:
Если год начальной даты високосный, а конечной нет, то:
Ну и наконец, если год начальной даты невисокосный и конечной тоже, индексы квартетов разные и в текущем индексе квартета есть високосный год, то в 1-м квартете количество дней високосного года лежащего внутри периода будет 366 (так как невисокосные года квартета с 1 по 3, а вторая дата лежит в одном из следующих квартетов).
Выше указанная логика расчёта количества дней високосных лет для первого квартета реализована следующей функцией на VBA:
Функция високосных дней для первого квартета
Private Function first_quartet_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long
Dim result As Long
result = 0
Dim year_diff As Long
Dim quartet_index_diff As Long
year_diff = year(d_end) - year(d_begin)
quartet_index_diff = quartet_index(year(d_end)) - quartet_index(year(d_begin))
If year_diff = 0 And is_year_leap(d_begin) Then
result = DateDiff("d", d_begin, d_end)
first_quartet_leap_year_days = result
Exit Function
End If
If quartet_index_diff = 0 Then
If is_year_leap(d_begin) Then
result = DateDiff("d", d_begin, CDate(DateSerial(year(d_begin), 12, 31)))
first_quartet_leap_year_days = result
Exit Function
End If
If is_year_leap(d_end) Then
result = DateDiff("d", CDate(DateSerial(year(d_end) - 1, 12, 31)), d_end)
first_quartet_leap_year_days = result
Exit Function
End If
Else
If is_year_leap(d_begin) Then
result = DateDiff("d", d_begin, CDate(DateSerial(year(d_begin), 12, 31)))
first_quartet_leap_year_days = result
Exit Function
Else
If Not is_quartet_noleap(quartet_index(year(d_begin))) Then
result = 366
first_quartet_leap_year_days = result
Exit Function
End If
End If
End If
first_quartet_leap_year_days = result
End FunctionЕсли разница индексов квартетов начальной и конечной даты >0, то рассчитывается 3-й блок формулы "Количество високосных дней в последнем квартете".
Здесь формула только одна, где при условии, что год конечной даты високосный:
Функция високосных дней для последнего квартета
Private Function last_quartet_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long
Dim result As Long
result = 0
Dim quartet_index_diff As Long
quartet_index_diff = quartet_index(year(d_end)) - quartet_index(year(d_begin))
If quartet_index_diff > 0 Then
If is_year_leap(d_end) Then
result = DateDiff("d", CDate(DateSerial(year(d_end) - 1, 12, 31)), d_end)
End If
End If
last_quartet_leap_year_days = result
End FunctionЕсли разница индексов квартетов начальной и конечной даты >1, то рассчитывается 2-й блок формулы "Количество високосных дней в промежуточных квартетах".
При этом К полных столетий – означает разность индексов столетий между датами. Например 1999 – индекс столетия 19, а 2001 – 20, таким образом разность столетий 1.
Аналогично и 400-летий.
Функция для расчёта високосных дней в промежуточных квартетах
Private Function middle_quartets_leap_year_days(ByVal d_begin As Date, ByVal d_end As Date) As Long
Dim quartet_count As Long
quartet_count = middle_quartets_count(d_begin, d_end)
If quartet_count = 0 Then
middle_quartets_leap_year_days = 0
Exit Function
End If
Dim q_begin, q_end As Long
q_begin = quartet_index(year(d_begin))
q_end = quartet_index(year(d_end)) - 1
Dim quot_25, quot_100 As Integer
quot_25 = WorksheetFunction.Quotient(q_end, 25) - WorksheetFunction.Quotient(q_begin, 25)
quot_100 = WorksheetFunction.Quotient(q_end, 100) - WorksheetFunction.Quotient(q_begin, 100)
Dim result As Long
result = (quartet_count - quot_25 + quot_100) * 366
middle_quartets_leap_year_days = result
End FunctionРеализация функций
Функция вычисления високосных дней для периода:
Public Function LEAP_DAYS(ByVal val_begin As Long, ByVal val_end As Long, Optional count_first_day = 0, Optional count_last_day = 1) As Long
Dim d_begin, d_end As Date
count_first_day = IIf(count_first_day <> 0, 1, 0)
count_last_day = IIf(count_last_day <> 0, 1, 0)
d_begin = CDate(val_begin)
d_end = CDate(val_end)
Dim check_error As Variant
check_error = check_constrains(d_begin, d_end)
If IsError(check_error) Then
LEAP_DAYS = check_error
Exit Function
End If
Dim result As Long
result = 0
If is_year_leap(d_begin) And count_first_day = 1 Then result = result + 1
If is_year_leap(d_end) And count_last_day = 0 Then result = result - 1
result = result + first_quartet_leap_year_days(d_begin, d_end) _
+ middle_quartets_leap_year_days(d_begin, d_end) _
+ last_quartet_leap_year_days(d_begin, d_end)
LEAP_DAYS = result
End FunctionВ приведённом выше коде мы сначала приводим значения параметров count_first_day и count_last_day к значению 1 или 0. Затем мы объявляем переменные типа Date для даты начала и окончания периода и задаём значения. Далее следует проверка параметров на ограничения.
По умолчанию функция не учитывает первый день периода, но учитывает последний день периода, но с помощью необязательных параметров указанных абзацем выше можно изменить это поведение. В строках 23-24 мы корректируем результат функции в зависимости от параметров.
Далее идёт сложение результатов промежуточных функций, которые были выше описаны.
Заключение
Таким образом мы получили формулы расчёта количества дней високосных и невисокосных лет в заданном периоде, скорость которой не зависит от количества дней в периоде. Единственный минус это то, что формула способна корректно работать только в рамках одной тысячи лет (2900 - 1900). Думаю, что до 2900 года у нас есть ещё время усовершенствовать такую функцию.
Ниже ссылка на гитхаб, где выложена полная реализация функций подсчёта високосных и невисокосных дней в периоде на VBA предназначенная для работы в Excel. Вы легко сможете портировать эту функцию на Ваш любимый язык и пользоваться в своих проектах.
Гитхаб
Источники и дополнительные ссылки
Статья из журнала "Главная книга" "Считаем проценты по займу: день первый, день последний"
Excel неправильно предполагает, что 1900 год является високосным годом.
Статья из Википедии "Григорианский календарь"
