Background
As you know, "Laziness is the engine of progress." In my work, I once faced a problem when it was necessary to draw up a table for calculating interest on a loan agreement, where the actual number of days in a year should have been for the base. The inconvenience was that you had to not forget about leap years and separate the days that belong to the leap year and the days of non-leap years. A simple formula was written, but later I found out that calculating leap years is not so simple.
Description of the problem
I wanted to improve the formula. On the Internet, I found a lot of program texts where the number of leap or non-leap years and days in a period was calculated. But, unfortunately, I was not satisfied with the fact that the speed of these functions depended on the number of years in the period. And I wanted the function to work just as quickly, no matter how many years in the period. But during development, I had to limit the allowable period of the function.
Reason 1
Most countries live according to the Gregorian calendar, the rules for leap years for which were determined back in 1582 by Pope Gregory XIII :
1. A year whose number is divisible by 400 is a leap year;
2. The rest of the years, the number of which is a multiple of 100, are non-leap years (for example, the years 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 .
Below is a link to the github, where the full implementation of the functions for calculating leap and common days in a period in VBA is laid out, designed to work in Excel. You can easily port this function to your favorite language and use it in your projects.