Human Resource Managers can benefit from this week’s tip. The YEARFRAC function can help with calculating the current life span of an asset, or the age of employees based upon their dates of birth. The YEARFRAC function calculates an accurate difference between two dates because unlike other methods that return a whole number, the YEARFRAC function returns a decimal result to indicate fractions of a year. Our example exercise is on how to calculate the life span of an asset based on date it was manufactured.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft Excel 2007, 2010 and 2013

The screen shot below will be used for this example.

 

 

 

 

 

1.         Select cell H4.

2.         Select the Formulas tab and then Date & Time as below.

 

 

3.         Select YEARFRAC from the Date & Time drop down list.

 

 

 

 

 

 

 

 

 

4.         Enter the formula arguments as below.

 

 

 

 

 

 

 

5.         Select OK and copy the formula down.

Notes:

·         Today() on the Start Date text box refers to the current system date

• Start_date  (required). A date that represents the start date.
• End_date  (required). A date that represents the end date.
• Basis  (optional). The type of day count basis to use.

 

 

 

 

 

6.         The answer will be as below.

 

 

 

 

 

Note:

Round off the figures to one decimal place. This enables the figures to be interpreted as years and months, i.e. 6.8 means 6 years and 8 months.