Calculation of age in Excel

In Excel, to calculate the age we can use these two different functions: the YEAR or DATEDIF function. These two functions will return age results based on the date, month and year of birth that the user entered in the data table. However, there are quite a few people who say that when they calculate their Excel age, the error does not show the age. Besides, the results of the calculation of YEAR function and DATEDIF function give 2 different results, due to the nature of each function.

So how to use the YEAR and DATEDIF functions to calculate age on Excel? And what is the way to handle errors that do not calculate the age of Excel? The following article of Network Administrator will guide you to read how to calculate age on Excel, applicable to Excel versions.

An error in Excel age calculation that users often encounter, it is impossible to calculate the correct number of years but turn it into a sequence of numbers as shown below.

Calculation of age in Excel Picture 1

This is because we have formatted in the age result box according to the date format, so the result will be as above. You need to convert that cell to the Number format format . And before calculating the number of years in Excel, users need to proceed to switch to Number format.

Step 1:

Black out the Age results column in Excel and right-click and choose Format Cells .

Calculation of age in Excel Picture 2

Step 2:

The Format Cells panel appears to adjust the format for the column. In Category Category, select the format as Number . The Decimal places section will select the number of decimal points displayed. Here the age is definitely an integer so you enter 0 . Finally click OK below to save. So we have formatted the age attribute to Number.

Calculation of age in Excel Picture 3

1. Calculate Excel's age using the YEAR function:

We will calculate the Excel age in the table below.

Calculation of age in Excel Picture 4

Step 1:

We enter the formula = YEAR (NOW ()) - YEAR (C2) in the age box, which is C2 and then press Enter to calculate the age.

Calculation of age in Excel Picture 5

Step 2:

Soon we will get the result as shown below. Scroll down the remaining cells to display the results.

Calculation of age in Excel Picture 6

2. Calculating Excel age with the DATEDIF function:

Step 1:

We enter the DATEDIF function formula to calculate the age in the result box = DATEDIF (C4, NOW (), "y") and then press Enter to apply.

Calculation of age in Excel Picture 7

Step 2:

Soon you will also get the age results and just drag down to calculate the age of the rest.

Calculation of age in Excel Picture 8

Note to users , the YEAR function and the DATEDIF function can all calculate age in Excel. However, the YEAR function will be based on the number of years, while the DATEDIF function with the formula above will return the age result for the date entered in the data table, with the current date.

Calculation of age in Excel Picture 9

The above are two ways to calculate the number of ages on Excel with the YEAR function or the DATEDIF function, as well as the precautions before entering the age calculation function. In order to be able to produce accurate age results, we need to convert the column to calculate the age number to the Number format instead of the date format in Excel.

I wish you all success!