The YEARFRAC function has an optional argument called “basis” that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The table below summarizes available options: Note that basis 0 (the default) and basis 4 both operate based on a 360 day year, but they handle the last day of the month differently. With the US convention, when the start date is the last day of the month, it is set to the 30th day of the same month. When the end date is the last day of the month, and the start date < 30, the end date is set to the 1st of the next month, otherwise the end date is set to the 30th of the same month. With the European convention, start dates and end dates equal to the 31st of a month are set to the 30th of the same month.
Examples
With a start date in A1, and an end date in B1, the YEARFRAC will return years between to two dates as a fractional number: To get a whole number only (not rounded), you can use the INT function like this: To get current age based on a birthdate, you can use a formula like this: Note: this formula can sometimes return incorrect results. See this example for more details. To get the percentage of the current year complete, you can use YEARFRAC like this: Full explanation here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
