YEARFRAC
Detailed Process Flow to Use the YEARFRAC Function in Excel
What is the YEARFRAC Function?
The YEARFRAC function in Excel calculates the fraction of the year between two dates. It is useful for:
✅ Calculating age from the date of birth (as seen in the image).
✅ Determining the exact number of years between two dates.
✅ Financial calculations like interest accruals or service periods.
Syntax of the YEARFRAC Function
- start_date → The beginning date (e.g., Date of Joining).
- end_date → The ending date (e.g., TODAY() for current age).
- [basis] (optional) → Determines the method of calculation. Default is 0 (US 30/360).
| Basis | Description |
|---|---|
| 0 | US (NASD) 30/360 |
| 1 | Actual/Actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
To calculate age, we use basis 1 (Actual/Actual).
Step-by-Step Process to Use YEARFRAC in Excel
Step 1: Select the Cell for the Age Calculation
Click on the first cell in the "Age" column where the result should appear (e.g., E2 in the table).
Step 2: Enter the YEARFRAC Formula
Use the formula to calculate age:
- D2 → Contains the Date of Joining (DOJ).
- TODAY() → Returns the current date dynamically.
- 1 → Uses the Actual/Actual basis for precise calculation.
Step 3: Round the Age to the Nearest Whole Number
Since age is typically represented in whole years, we round it using:
This ensures the result is a whole number.
Step 4: Drag the Formula Down
- Click on the small square at the bottom-right corner of the selected cell.
- Drag it down to apply the formula to the entire column.
Sample Data with Applied Function
| Sr.No | Emp.Code | Name | D.O.J | Formula Applied | Age (Result) |
|---|---|---|---|---|---|
| 1 | 4023 | Employee 13 | 04/07/1984 | =ROUND(YEARFRAC(D2, TODAY(), 1),0) | 41 |
| 2 | 4023 | Employee 7 | 05/05/2003 | =ROUND(YEARFRAC(D3, TODAY(), 1),0) | 22 |
| 3 | 4011 | Employee 11 | 05/05/2003 | =ROUND(YEARFRAC(D4, TODAY(), 1),0) | 22 |
| 4 | 4017 | Employee 5 | 05/04/2003 | =ROUND(YEARFRAC(D5, TODAY(), 1),0) | 22 |
| 5 | 4015 | Employee 4 | 05/02/1984 | =ROUND(YEARFRAC(D6, TODAY(), 1),0) | 41 |
| 6 | 4015 | Employee 8 | 03/01/2003 | =ROUND(YEARFRAC(D7, TODAY(), 1),0) | 22 |
| 7 | 4016 | Employee 6 | 18/11/2002 | =ROUND(YEARFRAC(D8, TODAY(), 1),0) | 22 |
| 8 | 4024 | Employee 15 | 16/09/2002 | =ROUND(YEARFRAC(D9, TODAY(), 1),0) | 22 |
| 9 | 4003 | Employee 12 | 05/06/2002 | =ROUND(YEARFRAC(D10, TODAY(), 1),0) | 23 |
| 10 | 4025 | Employee 9 | 30/04/2002 | =ROUND(YEARFRAC(D11, TODAY(), 1),0) | 23 |
| 11 | 4014 | Employee 1 | 21/03/2002 | =ROUND(YEARFRAC(D12, TODAY(), 1),0) | 23 |
| 12 | 4022 | Employee 10 | 16/01/2002 | =ROUND(YEARFRAC(D13, TODAY(), 1),0) | 23 |
| 13 | 4007 | Employee 3 | 25/10/2001 | =ROUND(YEARFRAC(D14, TODAY(), 1),0) | 23 |
| 14 | 4040 | Employee 2 | 06/05/2001 | =ROUND(YEARFRAC(D15, TODAY(), 1),0) | 24 |
| 15 | 4029 | Employee 14 | 08/05/2000 | =ROUND(YEARFRAC(D16, TODAY(), 1),0) | 25 |
Key Benefits of Using YEARFRAC
✅ Accurate Age Calculation → Works for birthdays and employment years.
✅ Real-time Updates → Uses TODAY() to keep values current.
✅ Flexible Basis Options → Can be adjusted for financial applications.
Conclusion
The YEARFRAC function is an efficient way to calculate age or duration between two dates. Combining it with ROUND ensures the values are in whole years.