The reasons and purposes for which it is necessary to calculate the age of a person can be many. Using a Microsoft Excel function and the "Date" format of the cells you can do this quickly and easily. Microsoft Excel stores dates as simple serial numbers, which represent the number of days that have elapsed since the reference start date set to January 1, 1900. The "DATA. DIFF ()" function compares two dates and returns the existing difference, so it's perfect for quickly determine a person's age.
Steps
Step 1. Create a column named "Name"
It doesn't matter if it has this exact header, but remember that this column will be the one that identifies each individual registered person whose age you want to calculate.
Step 2. Create a second column named "Date of Birth"
In this column the dates of birth of each of the people entered in the Excel sheet must be entered.
In this case we are calculating the age of a person, but if you need to use this function to calculate a deadline, the shipping date of an order, etc., you can rename the column in question as you see fit, according to your needs. personal. For example, you can call it "Payment Due Date", "Shipment Date", "Purchase Date" etc
Step 3. Enter the date of birth using the standard format
Make sure each date is entered in the same format. Here in Italy you have to use the DD / MM / YYYY format; conversely, if you live in the US, you must use the MM / DD / YYYY format. Excel should automatically recognize that the data entered are dates and format it according to the standard format adopted by the operating system.
If the data entered should be formatted as other entities, select the relevant cells, then choose the drop-down menu located in the "Number" group of the "Home" tab of Excel. At this point, select the "Short date" format from the list of options that appeared
Step 4. Create a column named "Age"
The cells of this column will display the result of our calculations, that is, the age of each of the people surveyed. But first you need to create the formula to perform the calculation.
Step 5. Select the first blank cell of the "Age" column
At this point you will have to enter the formula for calculating the age.
Step 6. Enter the formula that calculates a person's age in years
To do this, type the following string assuming the earliest useful date of birth has been stored in cell "B2":
- = DATA. DIFF (B2, TODAY (), "Y")
- = DATA. DIFF () is an Excel function that returns the difference between two reference dates. The parameters (B2, TODAY (), "Y") tell the "DATA. DIFF" function to calculate the difference between the date in cell "B2" (the date of birth of the first person registered in the list) and the date today, which is automatically calculated using the TODAY () function. The result of the calculation is expressed in years, as indicated by the "Y" parameter. If you need to express the difference between dates in days or months, use the "D" or "M" format respectively.
Step 7. Select the small square present in the lower right corner of the cell where you just entered the formula, then drag it down
This way the age calculation formula will be automatically copied and adapted to all subsequent cells.
Step 8. Troubleshoot an incorrect formula
If the formula provided in the previous step displays an unexpected result, such as # VALUE! or #NAME ?, it means there is an error. Make sure the formula syntax is correct and points to the cell set in the worksheet where the dates entered are. Note that the DATA. DIFF () function does not work with dates earlier than the reference date 1900-01-01.
Step 9. Edit the formula to calculate the exact age in years, months and days
If you need more precision, you can tell Excel to express the calculated age in years, months, and days. Again, the basic formula seen in the previous steps will be used, but with more parameters needed to achieve adequate accuracy:
