YEARFRAC issue

[jh_0108]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]Yearfrac formula[/TD]
[TD]output[/TD]
[/TR]
[TR]
[TD]11/10/2012[/TD]
[TD][/TD]
[TD]12/30/2015[/TD]
[TD]Yearfrac(A,C,1)[/TD]
[TD]3.1348391513[/TD]
[/TR]
[TR]
[TD]11/10/2012[/TD]
[TD]11/10/2015[/TD]
[TD]12/30/2015[/TD]
[TD]Yearfrac(A,B,1)+Yearfrac(B,C,1)[/TD]
[TD]3.1349329133[/TD]
[/TR]
</tbody>[/TABLE]

the date is in mm/dd/yyyy format

Anyone know why the outputs is different?

it happen when there is leap year. If column A are year 2013, the output for row 1 & 2 is the same.

 

[hicksi]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]Yearfrac formula
[/TD]
[TD]output
[/TD]
[/TR]
[TR]
[TD]11/10/2012
[/TD]
[TD][/TD]
[TD]12/30/2015
[/TD]
[TD]Yearfrac(A,C,1)
[/TD]
[TD]3.1348391513
[/TD]
[/TR]
[TR]
[TD]11/10/2012
[/TD]
[TD]11/10/2015
[/TD]
[TD]12/30/2015
[/TD]
[TD]Yearfrac(A,B,1)+Yearfrac(B,C,1)
[/TD]
[TD]3.1349329133
[/TD]
[/TR]
</tbody>[/TABLE]

the date is in mm/dd/yyyy format

Anyone know why the outputs is different?

it happen when there is leap year. If column A are year 2013, the output for row 1 & 2 is the same.

OK... WHAT IS the fraction of a 'year'?
For some years, each day makes up 1/365th. On others it is 1/366th.
The number of days between November 10 2012 and December 20 2015 is 1145
1145/3.1348391513 is 365.25. Oh great. That is what YearFrac uses

OK... The number of days between November 10 2012 and November 10 2015 is 1095.
YearFrac seems to calculate that as 2.997947. WHAT... Lo, that's 1095/365.25. And there's the anomoly (or at least half of it)

The other half...
The number of days between November 10 2015 and December 30 2015 is 50. Great, 1095+50 = 1145.
YearFrac calculates that as 0.136986. Which means it's using 365 days for that portion.

The short answer...
Ber VEWWY CAREFOOL when using YearFrac.

 

[hicksi]

And just to confuse you more.
Think in your head what the fractional years are between November 10 1012 and December 30 2015.
Is it 3 years and a bit?
But what's the bit?

Ie, is it November 10 2012 until November 10 2015 PLUS November 11 2015 to December 30 2015? That is 3 years plus 50/365 (there are 365 days in 2015)
Or is it November 10 2012 intil December 30 2012 PLUS January 1 2013 ro December 30 2015? That is 50/366 plus 3 years (there are 366 days in 2012)


The reality is that there is NO correct answer.

 

[steve the fish]

It doesn't really matter that there are very small differences. The formula provides a guide as to what proportion of a year is between two dates.

 

[jh_0108]

Thanks for the great explanation! hicksi

 

[hicksi]

I kinda agree.

Most would expect it to get the EXACT-points right, though...

{see my comment about 2.997947 above. That is exactly 3 years}