Excel: YEARFRAC Does Not Count Extra Day in Leap Years

 Last reviewed: July 16, 1997
Article 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 4.00 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.