YEARFRAC is a valuable formula in Excel. It calculates the fraction of a year represented by the number of whole days between two dates.
The primary purpose of the YEARFRAC formula is to calculate the difference between two dates as a fraction of a year. This proves particularly helpful when calculating ages or the time difference between two specific events.
The syntax for the YEARFRAC formula is as follows:
=YEARFRAC(start_date, end_date, [basis])
The YEARFRAC formula consists of three parameters:
start_date: This mandatory parameter is the starting date of the period.
end_date: Another compulsory parameter, this is the ending date of the period.
basis: An optional parameter that determines the counting basis to be used.
The YEARFRAC formula returns the year fraction representing the number of whole days between
The YEARFRAC formula handles dates according to the calendar specified by the optional
basis parameter. If
basis is omitted, the function uses the US (NASD) 30/360 basis.
The YEARFRAC formula is available in all versions of Excel. Its versatility makes it a go-to for users handling date-related calculations.
Let’s calculate the fraction of a year between “2023-01-01” (cell A1) and “2023-06-01” (cell B1):
This will return approximately 0.416667, representing roughly 41.5% of a year.
If you want to calculate the fraction of a year based on the actual number of days in months and years, use the
basis parameter as 1:
=YEARFRAC(A1, B1, 1)
To calculate the fraction of a year based on a 30-day month and a 360-day year, use
basis parameter as 0:
=YEARFRAC(A1, B1, 0)
You can also calculate the fraction of a year between the current date and a future date:
This will return the fraction of a year from today’s date to the date in cell B1.
You can use the YEARFRAC formula to calculate someone’s age:
If cell A1 contains the person’s birth date, this will return the person’s age in years.
Tips and Tricks
The YEARFRAC formula is perfect for age calculations. Remember to use the INT formula to round down to the nearest whole number if you need the age in complete years.
YEARFRAC may return a result slightly different from an expected result due to specific calendar scales’ rounding issues.
Common Errors and Solutions
A common error is the #VALUE! error. This occurs when the date inputs are non-numeric. Always ensure your dates are in a format that Excel recognizes.
Always check that your date inputs are correct and in a recognized format before running the YEARFRAC formula. This practice will save you from errors and give accurate results.
List of Related Functions
DATEDIF: Calculates the difference between two dates.
DATE: Returns the serial number of a particular date.
DAY: Returns the day of a date, from 1 to 31.
NOW: Returns the current date and time.
Frequently Used with the Formulas
YEARFRAC is often used in combination with
ROUND formulas to perform various date-related calculations.
Frequently Asked Questions
Q. How does the YEARFRAC formula handle leap years?
Leap years are considered when
basis is 1. If any other
basis is used, each year is considered to have 360 days.
Q. What happens if the start_date is larger than the end_date?
YEARFRAC will return a negative fraction if the
start_date is larger than the
Q. How can I calculate the precise age with the YEARFRAC formula?
INT(YEARFRAC(start_date, end_date)) to calculate the precise age.
Q. What is the default value of the ‘basis’ parameter in the YEARFRAC formula?
If omitted, the default
basis value is 0, which corresponds to the US (NASD) 30/360 basis.
Q. Can I use the YEARFRAC formula to calculate the number of years between two dates?
Yes, the YEARFRAC formula can calculate the number of years between two dates when combined with the
Visit our YouTube channel to learn step-by-step video tutorials