leap years and date calculation

Hi there,

I have a problem to calculate the year in between dates. It is easy to get the days, but I need to devide an USD amount over the certain period and need to reflect how much money I need to pay per year. So the question is:

How much money do I need to pay per year between 01/01/2010 and 31/12/2019 if the total amount during the period is USD 10,485,223.00?

Initially I could just work with the general value of 365,25 days a year like this:

A1: 01/01/2010
A2: 31/12/2019
A3: 10,485,223.00
A4: =A2-A1 resulting in 3651 days
A5: =(A2-A1)/365,25 resulting in 10,00 years
A6: =A3/((A2-A1)/365,25) resulting in USD 1,048,953.08 per year.

but this is not correct because its not 365,25 days during that period but the average year comes to 365,2 days (I manually did a average calculation with the relevant leap years 2012+2016)

How can I consider the leap years in my calculation? i.e. the bolt value needs to be somehow a calculated average of the days per year during the A1&A2 period.

Woudl apprecaite your help.

Brgds
Kai

You can test for a leap year e.g. with =MONTH(DATE(G8,2,29))=2, if TRUE his is a leap year.
G8 = year such as 2010.

Regards

Holy formula.... My brain is too small to easily insert my data as I do not understand the formula. I tried to study but failed. Would it be possible to create an example and fill your formula on my above data given ?

sorry and thank you in advanced.

Brgds
Kai

Sorry, updated below -- leveraging logic outlined by @RaulSerg

So A1 is the start date, and A2 the end date -- you shouldn't need to modify anything else.

Formula:

days per year adjusted for leap year days in range

=365+SUMPRODUCT((MONTH(DATE(ROW(INDEX(A:A;YEAR(A1)+(TEXT(A1;"mmdd")>"0228")):INDEX(A:A;YEAR(A2)-(TEXT(A1;"mmdd")<="0228")));2;29))=2)+0)/DATEDIF(A1;A2+1;"y")

(same caveat as before for partial years - i.e. untested)

the above basically creates 1 Feb 29 date per year in your range, and if XL sees that as a Feb date (month = 2) then you know it's a leap year, if XL says it's March 1st then you know it's not a leap year.

Thank you very much. Yes, it looks like its working. still pretty difficult to understand how it works but yes, the result is good.

But I made an easy example to my problem. So actually the Dates are in different cells.

$B$6: 01.01.2020 (this data is fixed)

$AA17: 31.12.2026 (this data is fixed by the row)

So now I come across the error with the A:A in your formula. what is the solution?

=365+SUMPRODUCT((MONTH(DATE(ROW(INDEX(A:A;YEAR(A1)+(TEXT(A1;"mmdd")>"0228")):INDEX(A:A;YEAR(A2)-(TEXT(A1;"mmdd")<="0228")));2;29))=2)+0)/DATEDIF(A1;A2+1;"y")

no, you don't need to worry about the A:A references, trust me that they can be left as-is, and will do their job, the only references you need to change are A1 + A2

If B6 is always start then change all references to A1 to $B$6 and A2 to $AA17
If the dates can alternate between which is start and which is end you would need to modify the DATEDIF to use MIN of dates for start, and MAX of dates for end.

In XL Dates are just serial numbers, so the formula basically uses the ROW function to create an array of serial numbers where each serial number represents day 29 of month 2 for each year in the range.
these serial numbers are then evaluated in context of being dates - i.e. which month do those serial numbers belong to -- if Month 2 then it's a leap year.

people often use short (non-INDEX) syntax like below:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($B$6&":"&$AA17));"mmdd")="0229"))

this creates a serial number for every day in your range, and tests to see if it's Feb 29th.... but it uses iNDIRECT (so volatile), so I tend to avoid.

Ahh, ok. This makes more sense. This helps me a lot. I will work with this formula now. Thank you very much. And have a great day! Awesome