YEARFRAC in Excel
For example, suppose we have “Date 1” as 30-June-2020 in cell A2 and 19-Sep-2021 as “Date 2” in cell B2. Then, the YEARFRAC function in cell C1 =(YEARFRAC A2,B2) will return the difference as 1.219 years.
Syntax
- start_date: What is the start_date or end_date of the available two dates?end_date: What is the end_date or greater date of the available two dates?[basis]: This is an optional argument. This argument needs to specify the day count method below available predetermined options.
Examples to use Excel YEARFRAC Function
Example #1 – Get the Difference between Two Dates
The YEARFRAC function is very useful when we want to know the difference between two dates in terms of years. For example, look at the two dates below.
We use the YEAR function to find the year difference between these dates. So, let us apply the same.
As we can see, we got the result as a full integer value, 1.
But the difference is more than one year, so we will use the YEARFRAC function in the C2 cell to get the full difference of a year.
Now, we have the difference between these two dates as 1.272 years.
- Open the YEARFRAC function. The start_date is the first argument, so select “Date 1” as the cell reference, A2 cell. The second argument is end_date, so select the B2 cell as the reference. We need the default result, so leave it as it is. Close the bracket and see the result.
We need the default result, so leave it as it is.
Example #2 – Get Age Based on Date of Birth
Now, we will find the age of persons based on their date of birth. Below is the data for this example.
From the above data, we need to find the age of each person as of today.
Let us open the YEARFRAC function.
The start_date will be DOB, so select B2 cell.
For End Date, insert the TODAY function in excelInsert The TODAY Function In ExcelToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time.read more because TODAY can return the current date per the system. Also, it is a volatile function that changes every day automatically, which makes the formula dynamic.
Close the bracket and see the result.
Now, drag the formula to cell C9 for the other values to be determined.
So now the age of each person in years, first-person “John” age is 31.378 years, “Peter” age is 20.647 years, and so on…
So far, so good with the YEARFRAC function, but if you tell the age in year fractions like 31.378, 20.647, and so on, people will understand accurately. So how about telling the age like this “30 years 4 months 15 days”.
It makes a lot of sense.
However, to tell the age like this, we need to use different formulas, including the DATEDIF functionDATEDIF FunctionDATEDIf is a date function that finds the difference between two dates, which can now be expressed in years, months, or days. This function’s syntax is =DATEDIF (Start Date, End Date, Unit).read more. Therefore, we have applied the formula already below to find the difference, as said above.
So this will tell the exact age in terms of year, months, and days. Since we have applied the TODAY function as the end_date, it will change every day, and the result also will change automatically.
Example #3 – Using IF Condition
Now, we will see the YEARFRAC function as a supporting function for other functions. But, first, take a look at the below data.
It is the date of employees who work in an organization. This data has an employee’s name and their respective joining date.
With this data, we need to find eligible persons for the bonus. To be eligible for the bonus, the person has to complete the 4.5 years tenure in the company. As we know, the YEARFRAC function can get the difference between two date infractions. Therefore, we will incorporate this with the IF condition.
Now, we have applied the logical test as if the YEARFRAC returns the year of service as >= 4.5 years, then we should get the result as “Eligible” or else “Not Eligible.”
We get the following result.
Here, we have the result. Like this using the YEARFRAC function, we can make it in several ways.
Things to Remember
- The [basis] argument should be within 0 to 4, anything more than 4 or anything less than 0 supplied, then we will get “#NUM! Error.The TODAY function is the volatile function used to get the current date per system.The start_date should be less than the end_date. Otherwise, we will get the error.
Recommended Articles
This article has been a guide to YEARFRAC in Excel. Here, we discuss how to use the YEARFRAC Excel function to find the difference between two dates, get age based on the date of birth, and use it with IF Condition, examples, and a downloadable template. You may also look at these useful functions in Excel: –
- YEAR Formula in ExcelYEAR Formula In ExcelThe year function in excel is a date function to calculate the year from a given date. This function takes a serial number as an argument and returns a four-digit numeric value representing the year of the given date, formula = year (serial number)read moreSubtract Date In ExcelSubtract Date In ExcelIn Excel, there are two ways to subtract dates: direct subtraction and the DATEDIF function. The number of years, months, or days, or all, are some of the common scenarios for subtracting dates.read moreCompare Dates in ExcelCompare Dates In ExcelComparing two dates in Excel can be useful in a variety of situations, such as determining whether one date is equal to the other, or determining whether one date is greater than or less than another, and so on.read moreSUMIF Between Two DatesSUMIF Between Two DatesWhen we wish to work with data that has serial numbers with different dates and the condition to sum the values is based between two dates, we use Sumif between two dates. read more