Excel: YEARFRAC Does Not Count Extra Day in Leap Years

ID: Q89031

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SUMMARY

In Microsoft Excel, the YEARFRAC function does not recognize leap years, such as 1992, unless the leap date (for example, 2/29/92) is between the starting and ending dates.

MORE INFORMATION

Note that the results in following examples are the results that are returned in Microsoft Excel version 5.0. The results returned in Microsoft Excel version 4.0 are slightly different, although the same behavior occurs.

For more information on the YEARFRAC function returning different values in Microsoft Excel version 5.0 than in Microsoft Excel version 4.0, query on the following words in the Microsoft Knowledge Base:


   yearfrac and different

Example 1

This example shows that YEARFRAC uses 365 days in the denominator instead of the 366 days in a leap year.


   YEARFRAC(DATEVALUE("01/01/92"), DATEVALUE("02/01/92"),1)

This function returns .084932, which is 31/365.

Example 2

This example shows that YEARFRAC uses 366 days in the denominator since the leap date falls between the starting and ending dates.


   YEARFRAC(DATEVALUE("01/01/92"), DATEVALUE("06/01/92"),1)

This function returns .415301, which is 152/366.

YEARFRAC uses 366 days in the denominator only when the leap day falls between the start date and the end date.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 469

Additional query words: 7.00

Keywords :
Version :
Platform :
Issue type :