This page calculates age in Excel, using the following built-in functions:
'How do I calculate an age in Excel?' Marion Urban, a French journalist and student on the MA in Online Journalism in Birmingham, was preparing data for the forthcoming UK General Election. Checking whether a birthday comes before or after a particular date. Find out the ages of people using Excel ” dontbringlulubook November 19, 2014.
Yearfrac | - | Calculates the number of years between two dates. |
Int | - | Truncates a supplied number down to the closest integer. |
Today | - | Returns the current date. |
Age Calculation Using the Excel Yearfrac Function
When entering dates into your Excel age calculation, these should be entered as either:
- References to cells containing dates
- Dates returned from formulas.
It is not recommended that dates be entered as text representations of dates, as these may be interpreted differently, depending on the date settings on your computer.
The simplest way to calculate age in Excel is to use the Excel Yearfrac Function, which returns the number of years between two dates.
I.e. The number of years between two dates, start_date and end_date is calculated as follows:
=YEARFRAC( start_date, end_date )
The following spreadsheet shows the Excel Yearfrac function, used to calculate the age, on 01/01/2017, of an individual who was born on 03/03/1960:
A | B | |
---|---|---|
1 | Birth Date: | 03/03/1960 |
2 | Current Date: | 01/01/2017 |
3 | Age: | =YEARFRAC( B1, B2 ) |
The function in cell B3 of the above spreadsheet returns the value 56.82777778.
I.e. on the date 01/01/2017, a person who was born on 03/03/1960, was 56 years old.
How to Return an Integer From the Excel Age Calculation
If you want to tidy up the above age calculation, so that it returns an integer, rather than a decimal value, this can easily be done using the Excel Int Function, which truncates a supplied number down to the closest integer.
In the spreadsheet below, the Int function is used to truncate the result of the Yearfrac function to an integer.
A | B | |
---|---|---|
1 | Birth Date: | 03/03/1960 |
2 | Current Date: | 01/01/2017 |
3 | Age: | =INT( YEARFRAC( B1, B2 ) ) |
The function in cell B3 of the above spreadsheet now returns the value 56.
Use of the Today Function to Automatically Update an Age Calculation in Excel
If you want your age formula to calculate the age of an individual on the current date, and to update automatically each day, this can be done using the Excel Today function, which returns the current date.
Therefore, if you replace the value in cell B2 of the above spreadsheet, with the formula =TODAY(), this will be used in the age formula in cell B3.
Alternatively, you could use the Today function directly in the age formula, so that the Excel age calculation formula becomes:
Return to the Excel Formulas Page
Return to the ExcelFunctions.net Home Page
Return to the ExcelFunctions.net Home Page
This Excel tutorial explains how to use the Excel YEARFRAC function with syntax and examples.
Description
The Microsoft Excel YEARFRAC function returns the number of days between 2 dates as a year fraction.
The YEARFRAC function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the YEARFRAC function can be entered as part of a formula in a cell of a worksheet.
If you want to follow along with this tutorial, download the example spreadsheet.
Syntax
The syntax for the YEARFRAC function in Microsoft Excel is:
Parameters or Arguments
Optional. It is the basis used to calculate the year fraction. It can be one of the following values:
Value | Explanation |
---|---|
0 (default) | US 30 (NASD)/360 |
1 | Actual days/Actual of days in year |
2 | Actual days/360 |
3 | Actual days/365 |
4 | European 30/360 |
Note
- Only the date portion (not time) of start_date and end_date is used in the calculation.
- Only the integer portion of basis is used in the calculation. If a decimal value is provided, it will be truncated.
- If either start_date or end_date is not a valid date, the YEARFRAC function will return the #VALUE! error.
- If the integer portion of basis is not between 0 and 4, the YEARFRAC function will return the #NUM! error.
- A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900.
Returns
The YEARFRAC function returns a numeric value.
Applies To
- Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007
Example (as Worksheet Function)
Let's look at some Excel YEARFRAC function examples and explore how to use the YEARFRAC function as a worksheet function in Microsoft Excel:
In the spreadsheet above, the dates in column A and B have been entered as serial dates. To see the numeric value for any serial date, change the format of the cell to General.
Based on the Excel spreadsheet above, the following YEARFRAC examples would return:
You can also enter the date values using the DATE function as follows:
This formula would return the year fraction between Jan 1, 2016 and Dec 31, 2016.