Number of leap year days between two dates
Ray Proudfoot
I've created a Excel97 spreadsheet for colleagues which,
amongst other things, needs to ignore the number times
Feb 29 occurs between two supplied dates. This is so we
can accurately determine reckonable service for pension
purposes where leap years are ignored except in the final
year.
My rather crude effort of using the following formula is
not always accurate depending on the years involved...
A1 is the Start Date, A2 is the End Date.
=INT(YEAR(A2)-YEAR(A1))/4
This formula for a start date of 26 February 1992 and end
date of 20 March 2000 only computes 2 days, not 3.
Any help would be greatly appreciated.
merjet
Function CountLeapDays(pDay1 As Date, pDay2 As Date) As Integer
Dim iCt As Long
For iCt = CLng(pDay1) To CLng(pDay2)
If Day(iCt) = 29 And Month(iCt) = 2 Then CountLeapDays = CountLeapDays +
1
Next iCt
End Function
HTH,
Merjet
Dave Patton
One way is to create a range of dates with Feb 29; you could create the
range by formula (see below).
then use a formula like
=COUNTIF(rFeb29,">"&A1)-COUNTIF(rFeb29,">"&A2)
I obviously named the range rFeb29.
=DATE(YEAR(G3)+4,MONTH(G3),DAY(G3))
"Ray Proudfoot" <RayPro...@BTInternet.com> wrote in message
news:04bf01c2c949$cfde8ea0$89f82ecf@TK2MSFTNGXA01...
Ray Proudfoot
Thank you very much for your quick reply. I've made a note of your
suggestion and will work on it when I return to work on Monday.
What a great place this is! This problem has been driving me bonkers for
days.
Regards,
Ray.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Ray Proudfoot
Thanks for your suggestion. I'm fairly inexperienced with Excel but I'll
try your idea and will come back if I hit any snags.
Your quick reply is much appreciated!
Ray Proudfoot
Cheshire, England.
Myrna Larson
part of the Analysis TookPak
=YEARFRAC(StartDate,RetirementDate+1,3)
will return a value like 28.72, treating all years as having 365 days.
If you want to treat the current year differently,
=YEARFRAC(StartDate,DATE(YEAR(RetirementDate),1,1),3) +
YEARFRAC(DATE(YEAR(RetirementDate),1,1),RetirementDate+1,1)
The +1 is to include both the starting and ending dates as dates worked.
Check out this function in Help for more information.
Myrna Larson
loops, only 4 date functions called. It does have at least one disadvantage compared to yours --
it needs modifications to handle a timespan that includes a century year other than 1900 and
2000.
Function LeapDays(D1 As Date, D2 As Date) As Long
Dim L As Long
Dim N As Long
Dim Y1 As Long
Dim Y2 As Long
LeapDays = 0
Y1 = Year(D1)
'skip first year if D1 is 3/1 or later
If Month(D1) >= 3 Then Y1 = Y1 + 1
Y2 = Year(D2)
'skip last year if D2 is 2/28 or earlier
If D2 <= DateSerial(Y2, 2, 28) Then Y2 = Y2 - 1
'find the 1st leap year on/after Y1
If Y1 = 1900 Then Y1 = 1901
N = Y1 Mod 4
If N Then Y1 = Y1 + 4 - N
If Y1 > Y2 Then Exit Function
N = Y2 - Y1 + 1
L = N \ 4
If (N Mod 4) <> 0 Then L = L + 1
LeapDays = L
End Function
Ray Proudfoot
I have a question on your suggested solution. Should the formula of
=COUNTIF(rFeb29,">"&A1)-COUNTIF(rFeb29,">"&A2) be changed to..
=COUNTIF(rFeb29,">="&A1)-COUNTIF(rFeb29,">="&A2)
In short, should the condition be >= rather than > ? Entering dates of
29/2/1996 - 28/2/2000 returns 0 leap year dates with your formula but 1
with my amended one which is the correct number.
Thanks.
Dave Patton
=COUNTIF(rFeb29,">="&A1)-COUNTIF(rFeb29,">"&A2)
or you could use
=SUMPRODUCT((rFeb29>=A1)*(rFeb29<=A2))
As I stated, you can use a formula to put just a sequence of Feb 29s in that
range.
"Ray Proudfoot" <raypro...@btinternet.com> wrote in message
news:eyopuKYyCHA.2600@TK2MSFTNGP11...
merjet
> I don't know about easier, but for a timespan of 60 years, this code is
2000 times faster. No
> loops, only 4 date functions called. It does have at least one
disadvantage compared to yours --
> it needs modifications to handle a timespan that includes a century year
other than 1900 and
> 2000.
For a timespan of 60 years on my machine, my code was a little slower but
still very fast --
much less than 1 second. But if speed is an issue, my function could be sped
up as follows:
If Day(iCt) = 29 And Month(iCt) = 2 Then
CountLeapDays = CountLeapDays + 1
iCt = iCt + 4*360
End If
J.E. McGimpsey
=A2-A1-(DATE(2003,MONTH(A2),DAY(A2)) -
DATE(2003,MONTH(A1),DAY(A1)))-365*(YEAR(A2)-YEAR(A1))
In article <04bf01c2c949$cfde8ea0$89f82ecf@TK2MSFTNGXA01>, Ray
Myrna Larson
or so to get to the next one. You could change your multiplier to 365. Then you only have to
check 2 dates, Feb 28 and Feb 29.
If you don't have to worry about the year 2100, you can just add 1461 to iCt and 1 to the number
of leap days while iCt is less than the 2nd date, i.e.
L = 0
iCt = Day1
Do Until (Month(iCt) = 2 And Day(iCt) = 29) Or iCt > Day2
iCt = iCt + 1
Loop
If iCt <= Day2 Then
Do
L = L + 1
iCt = iCt + 1461
Loop While iCt <= Day2
End If
CountLeapDays = L
But I still don't like this because you must make from 0 to 1461 (average 730.5) passes through
the 1st loop. That's wasted time because you can determine from the year number whether it's a
leap year or not:
Function IsLeapYear(ADate As Date) As Boolean
Dim y As Long
y = Year(ADate)
If y Mod 4 <> 0 Then
IsLeapYear = False
Else
If y Mod 100 <> 0 Then
IsLeapYear = True
Else
IsLeapYear = ((y Mod 400) = 0)
End If
End If
End Function
So once you determine whether the starting date is before or after 2/28, you need to do at most
4 checks for a leap year.
BTW, the programming purists would say that since you are now changing your loop counter within
the loop (it's only supposed to be changed by the Next statement), you should use a Do
While/Loop construction instead of For/Next.
Myrna Larson
and 12/31/1969, it returns 10.000 years only if the 3rd argument is 0 (i.e. use 30/360). Does
anyone know how this function works internally? Is it perhaps intended only for calculating
partial years, i.e. range 0-1?
In the meantime, I realized that this formula will give the number of leap days in the number of
whole years prior to the last anniversary date. If the anniversary date is Jan 1 and the ending
date is March 1 in a leap year, that last leap day is not included (which is what Ray said he
wanted, I believe.)
=(A2-A1)-(DATEDIF(A1,A2,"y")*365+DATEDIF(A1,A2,"yd"))
The result is correct if the starting date is at least March 1 1900. If your starting date is in
the range Jan 1 1900 - Feb 28 1900, then you must subtract 1
If the ultimate goal is length of service, excluding leap days prior to the last anniversary
date, it's =DATEDIF(A1,A2,"y")*365 + DATEDIF(A1,A2,"yd"), where the 2nd part of the formula
gives the number of days in the last partial year. The result is in Days.
If you want years,
DATEDIF(A1,A2,"y") = number of whole years
DATEDIF(A1,A2,"yd")/365 or DATEDIF(A1,A2,"yd")/366 = the fractional year
The choice of divisor depends on whether the year period that begins on the last anniversary of
the start date includes a Feb 29 and if you care about that.
Myrna Larson
Here's a function I wrote some years back that calculates the number of whole and fractional
years between two dates, counting from the earlier date to the later date.
The divisor for the last partial year is determined by the number of days in the year beginning
on the last anniversary date prior to EndDate. It's equivalent to what I would expect (but don't
get!) from YEARFRAC with the 3rd argument = 1 (Actual/Actual).
It isn't inclusive, so 1/1/1960 - 12/31/1969 is < 10 years. If you want to include the end date,
too, your formula would be =YEARDIFF(A1,A2+1). In that case, 1/1/1960 and 12/31/1969 gives 10.00
years.
Function YearDiff(ByVal StartDate As Date, _
Optional ByVal EndDate As Date = #1/1/100#) As Double
'modified 02/01/2003
Dim AnnDay As Long
Dim AnnMonth As Long
Dim AnnYear As Long
Dim ltemp As Date
Dim NextAnn As Date
Dim PrevAnn As Date
If EndDate = #1/1/100# Then EndDate = Date
'put in right order if necessary
If StartDate > EndDate Then
ltemp = StartDate
StartDate = EndDate
EndDate = ltemp
End If
'get anniversary date in ending year
AnnYear = Year(EndDate)
AnnMonth = Month(StartDate)
AnnDay = Day(StartDate)
'assume it's already occurred
PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay)
If PrevAnn <= EndDate Then
'assumption that it's past was correct
'next anniversary is 1 year in the future
NextAnn = DateSerial(AnnYear + 1, AnnMonth, AnnDay)
Else
'wrong -- we calculated the *next* anniversary
NextAnn = PrevAnn
AnnYear = AnnYear - 1
PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay)
End If
YearDiff = AnnYear - Year(StartDate) + _
(EndDate - PrevAnn) / (NextAnn - PrevAnn)
End Function 'YearDiff
Norman Harker
Re YEARFRAC and calculations of fractional years.
I have a formula solution that appears to test OK against you YEARDIFF UDF
but I won't pretend that it is the most elegant one. It does however reveal
some useful points.
=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDI
F(A1,B1,"y"),MONTH(A1),DAY(A1)))
Here's the problems and logic of construction which someone might like to
improve upon.
I get the same conclusion as you did when you wrote your YearDiff UDF: i.e.
YEARFRAC isn't suitable for years of service.
Where YEARFRAC basis = 1 and the dates are more than a year apart it can be
shown that YEARFRAC uses as its divisor the average number of days in the
years start_date to end_date inclusive of the start_date and end_date years
irrespective of where in those years the start_date and end_date falls.
But where the dates are 1 year or less apart it uses either 365 or 366
depending upon whether either year is a Leap Year and where in the year the
start_date and end_date fall relative to 29-Feb.
That makes YEARFRAC basis 1 difficult to replicate as a formula and it
produces non-exact years of service for all anniversary dates except the
first.
I can't see how YEARFRAC can be used for years of service with basis 1 which
strikes me as the most likely candidate. YEARFRAC with basis = 3 similarly
fails to produce exact years for all anniversary dates except the first.
This is important in the context of finding a method that can be used for
legal entitlements and any alternative must have consistency. The method
must also be one that is capable of being translated to an acceptable
definition in terms of how it is calculated or lawyers will have a ball and
we will be that ball. (Not a bad mixed metaphor that one!)
So I go for a formula approach and hope that I can get a clear up definition
on the way.
Calculating whole years is easy:
=DATEDIF(A1,B1,"y")
I've tested this and it always seems to give the right answer and it's the
simplest approach.
Calculating residual days is not so easy!
=DATEDIF(A1,B1,"yd")
returns some annoying 1 day errors for example:
=DATEDIF("10-Apr-2003","9-Mar-2005","yd")
returns: 333
=DATEDIF("10-Apr-2003","10-Mar-2005","yd")
returns: 335
There's got to be something wrong there!
Also:
=DATEDIF("15-Sep-2002","14-Mar-2004","yd")
returns: 181
=DATEDIF("15-Sep-2002","15-Mar-2004","yd")
returns: 181
There's got to be something wrong there as well!
So to calculate the days since the last anniversary date I use:
=B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))
I've tested this and it doesn't appear to produce any errors.
So we have an algorithm for years and days between dates that yields the
correct answer:
=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
This in itself is a very useful formula as it is different from the one that
we usually use and which produces errors for dates such as those used above.
Determining the numerator for calculating the fraction of a year for the
residual days is a debatable point. Various arguments can be put forward for
different approaches but to my mind the most logical is to use the count of
days between the last anniversary and the next anniversary. In essence I'm
saying that we ask the question, "How many days are there between the last
anniversary and the next anniversary and what decimal fraction of those days
have I used up?" Since my formula and your UDF produce the same answers, it
appears that we are using the same logic (but not necessarily the correct
one <vbg>).
To calculate this number of days, I need to calculate the date of the next
anniversary and deduct the date of the previous anniversary and here, of
course I hit the same problem of potential for error in calculating days.
Date of Next Anniversary is:
=DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))
Date of Previous Anniversary is:
=DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))
So days between Anniversaries is:
=DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF
(A1,B1,"y"),MONTH(A1),DAY(A1))
So I can now calculate the fraction of the year between anniversaries.
=(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A1)+DAT
EDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(
A1),DAY(A1)))
Add that to the number of whole years:
=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDI
F(A1,B1,"y"),MONTH(A1),DAY(A1)))
I now run a test of my formula against your UDF.
A1 uses:
=DATE(RANDBETWEEN(1990,2110),RANDBETWEEN(1,12),RANDBETWEEN(1,31))
B1 uses:
A1+RANDBETWEEN(0,36600)
C1 uses:
My Formula
D1 uses:
=yeardiff(A1,B1)
E1 uses:
=IF(C1=D1,TRUE,FALSE)
I have a subroutine:
Sub TestYearFrac()
Dim n As Long
For n = 1 To 1000000
Application.Calculate
If Range("TestCell").Value = False Then End
Next
MsgBox "Success"
End Sub
I run the subroutine and go off for my daily swim (and I'll make it a long
one!).
It checks out OK. OK! So the test routine leaves a lot to be desired but it
served a one off purpose and I needed a good swim!
In passing we correct the old y - d error and I think that we have a
reasonably rigorously defined algorithm that can be used for calculating
entitlements subject to an appropriate form of words being attached to the
definition of fractions of a year.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Myrna Larson" <myrna...@charter.net> wrote in message
news:1m3o3vkmnr4ge1ubt...@4ax.com...
Ray Proudfoot
I'm bowled over at the response to my request on the
problem I posed you. Perhaps I should have said that my
calculated answer will not be used to determine the
pensionable pay of any employee but is being used as aide
to the software that will. This is a commercial software
package and we have to abide by its answers but clerks
require a way to easily calculate the number of
years/days between two supplied dates to ensure the
answer provided by the commercial software is accurate.
Despite this I wanted to give them a quality tool but the
problem of leap years is quite a difficult one as shown
by the depth of your replies and suggested solutions.
I've taken on board all your replies for which I'm very
grateful and will have a chat with my boss tomorrow on
the best way forward.
Thank you all once again. You have all been very helpful.
Myrna Larson
That's some project you undertook here!
Thanks for your insights on what's going on with YEARFRAC. I don't think I would have EVER
figured out what denominator they are using! Maybe the programmers' expectation was to use this
only for what the name implies -- a period which is a *fraction* of a year. The result for
longer periods certainly don't make sense, regardless of the 3rd argument.
I am unsettled by your findings re DATEDIF with the "yd" option. VBA doesn't have this
"remainder" argument for its DateDiff function. You have to do as we have -- calculate the last
anniversary date prior to EndDate, then get the number of days between them. Doing it that way
in VBA doesn't exhibit the errors you show. It gives 333 and 334 for your first example, and 181
and 182 for the 2nd.
Thanks for spending the time to compare the results of your formula and my function. (Formulas
like that are what induce me to just write it in VBA <g>.) I was heartened -- but not surprised
-- to see there were no differences, since the algorithms are the same.
As for whether we are right on the issue of the number of days in a final partial year, I look
at it this way: businesses and governments often use "fiscal years" which obviously begin on
some day other than Jan 1. I think we have the same principle here. Each employee has his own
fiscal year, which begins on the anniversary of his hire-date. Of course the fiscal year for a
business typically begins on the 1st day of a month, but I don't see why that should be a
requirement. The only starting date that would be problematic would be Feb 29.
Which reminds me -- if the start date falls on Feb 29, what do you suggest doing? With your
formula, adding 1 year returns Mar 1. I didn't consider this explicitly in my UDF, but it
calculates the anniversary dates the same way, so it's going to do the same thing.
Myrna
Myrna Larson
information?
Norman Harker
Replies in between yours.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Myrna Larson" <myrna...@charter.net> wrote in message
news:u7cr3vsf22g1jvbda...@4ax.com...
> Hi, Norman:
>
> That's some project you undertook here!
**
It's a crucial area in finance! We need a reliable, consistent and logical
method of calculating years that can be used as a basis of calculating and
apportioning rights and obligations. It's not a problem that we can put in
the 'too hard basket' and it won't go away. That method needed to be one
whose algorithm was consistent and could be 'translated' by lawyers into a
definition that can be incorporated into legislation and contracts. I prefer
a formula approach because of cross-platform robustness. I prefer a VBA
approach for user friendliness subject to need for top quality
documentation, which is sadly lacking from most issues of Add-Ins that
contain functions.
**
>
> Thanks for your insights on what's going on with YEARFRAC. I don't think I
would have EVER
> figured out what denominator they are using! Maybe the programmers'
expectation was to use this
> only for what the name implies -- a period which is a *fraction* of a
year. The result for
> longer periods certainly don't make sense, regardless of the 3rd argument.
>
**
I think you've hit the nail on the head! The YEARFRAC algorithm just gets
horrifically confused after 1 year and the 'anniversary results' just serve
to make that confusion patent.
**
> I am unsettled by your findings re DATEDIF with the "yd" option. VBA
doesn't have this
> "remainder" argument for its DateDiff function. You have to do as we
have -- calculate the last
> anniversary date prior to EndDate, then get the number of days between
them. Doing it that way
> in VBA doesn't exhibit the errors you show. It gives 333 and 334 for your
first example, and 181
> and 182 for the 2nd.
>
**
You and me both! Here's me and everyone else thinking that DATEDIF with "yd"
was OK! It was only one of my initial checks of sequence of returns that
threw up the problems and as a side effect I've had to re-write some of my
'age' formulas that rely upon "yd" for y-d or y-w-d reports. Perhaps we now
know why Microsoft didn't incorporate a "yd" in VBA's Datediff.
**
> Thanks for spending the time to compare the results of your formula and my
function. (Formulas
> like that are what induce me to just write it in VBA <g>.) I was
heartened -- but not surprised
> -- to see there were no differences, since the algorithms are the same.
>
**
No thanks required. I needed something to test a formula against and your
VBA function was there. As you say; same algorithm, same result. Has to be!
**
> As for whether we are right on the issue of the number of days in a final
partial year, I look
> at it this way: businesses and governments often use "fiscal years" which
obviously begin on
> some day other than Jan 1. I think we have the same principle here. Each
employee has his own
> fiscal year, which begins on the anniversary of his hire-date. Of course
the fiscal year for a
> business typically begins on the 1st day of a month, but I don't see why
that should be a
> requirement. The only starting date that would be problematic would be Feb
29.
>
**
Agreed. It is the crucial area for discussion now. You'll see the process
that I followed in building the formula solution and I spent a fair amount
of time looking for and at alternatives. TINA! I prefer the verbalized
version that you came up with when writing Yeardiff and indicated by you
annotations. How many days since my last anniversary? How many days between
last and next? A/B plus whole years.
**
> Which reminds me -- if the start date falls on Feb 29, what do you suggest
doing? With your
> formula, adding 1 year returns Mar 1. I didn't consider this explicitly in
my UDF, but it
> calculates the anniversary dates the same way, so it's going to do the
same thing.
>
**
You had me worried for a bit! Then I remembered my crude 1mil checks and
knew that they must be the same. The anniversary of 29th February is 1st
March. It must be if you follow the logic that it is the day after 28th
February. It's only if you follow the logic that it is the last day of
February that you have a problem. Where 29-Feb-1996 and 1-Mar-2003 are
compared the result is 7. With comparison of 29-Feb-1996 and 28-Feb-2003 we
both get 6.9972602739726 I think that what both solutions do is consistent
with, for example, =DATE(2003,2,29) where the number exceeds the number of
days in any month the month increments by 1 and days start at 1. Same is
done with TIME.
**
>=(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A1)+DA
T
>
>EDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH
Ray Proudfoot
I don't have access to the code or algorithm I'm afraid.
It's probably a closely guarded secret given how complex
it is to calculate with total accuracy.
Rocket scientists probably have it easy compared to this!
<g>
Regards,
Myrna Larson
and ends).
merjet
record? Ray can be proud. Oh, I see he is.
Merjet
Norman Harker
21 including your last one <vbg> Now we're on 22.
But it's no-where near the record.
The question raised some important issues and in passing we cracked a
problem with reporting ages in years and fractions of a year plus revealed
that there was an apparent error in DATEDIF with the "yd" argument. The
problem on years and fractions is resolved using Myrna's UDF or the formula
that I derived from the same algorithm.
The question has quite significant ramifications for calculation of
entitlements and apportionments for retirement benefits, long service leave
etc. At the same time we came up with a clearly specifiable definition of a
fraction of a year.
I reckon it was a good exercise although I think that Ray is constrained by
a definition that he has to conform to.
Well certainly be using the material developed here elsewhere! So it isn't
just a 'one off' solution.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"merjet" <mer...@attbi.com> wrote in message
news:6vP%9.93612$to3.2...@rwcrnsc51.ops.asp.att.net...
Ray Proudfoot
LOL! Yes, the reponse has been excellent even though the
discussion got a bit heavy at times. I've delivered a new
version to Pensions staff and I'm now awaiting their
responses.
In the end, because I had no way of knowing if a period
of service input to the spreadsheet was the first, one in
the middle or the last I added a checkbox which the clerk
ticks when the dates entered are the final ones in the
person's service. This allows me to take into account the
adjustment for a leap year if it's appropriate.
Thank you all once again for your help.
Kind regards,
Ray.
>.
>
Ray Proudfoot
Sorry I didn't get back to you before. I'm a bit confused about the
default MS software and this DevDex software which seems to show replies
that the default doesn't.
I'll experiment with the suggestion you made regarding the amended
original one and the SUMPRODUCT alternative. Things are looking pretty
good now so thanks once again.
mjoh...@nosm.ca
=DATE(YEAR(EndDate),1,1)-DATE(YEAR(StartDate),1,1)-((YEAR(EndDate)-YEAR(StartDate))*365)+AND(MONTH(DATE(YEAR(EndDate),2,29))=2,DATE(YEAR(EndDate),2,29)<EndDate)-AND(MONTH(DATE(YEAR(StartDate),2,29))=2,DATE(YEAR(StartDate),2,29)<StartDate)
Basically it finds full year days(Jan 1 StartYear to Jan 1 StartYear), then it figures out how many days above 365 per year the net of EndDate - StartDate(because Excel includes leap days), then it adjusts that number based on if the start or end dates are in leap years as well as the startDate being before Feb 29th(meaning the date range experienced that leap day) or the endDate is after Feb 29th(again maining the date range experienced the leap day).
If you are questioning why we minus when the start date is past Feb 29th, it is because when we normalize for a full year(very first part), we ASSUME the date range experienced the leap day... so if it didn't... we need to subtract 1 day.
Hopefully this helps somebody :P took me a few hours to figure it out...