In the article we will learn how to calculate the age in Excel and what is the formula we have in Excel to calculate the age.
Excel considers dates as numbers. Each date is assigned a unique serial number. For example, the 18th February 2014 was its serial number as 41684. Fortunately, you don’t need to know this but the fact that all dates have numerical values can be very useful. Windows uses the 1900 date system in which 2nd January 1900 is date serial 2 and so on.
When you type a date into a cell. Excel shows you a date but is thinking of a number.
To find out the serial number of a date, follow the below mentioned steps
- Select the cell containing the date.
- Press the key “CTRL+1”, the dialog box of “Format Cells” will appear.
- In “Number” tab select “General” from the category list, the serial number will appear in the sample box and click on OK.
You can make use of these numbers in all sorts of ways. You can add a number to a date to give a date that number of days later (or subtract a number to get a date before), you can subtract one date from another to find out how many days are in between. There are lots of ready-made date functions too.
To calculate age from date of birth we will use “Today” function along with the“INT” function.
Let’s take an example and understand how excel calculate age from date of birth.
I have employee data in which Column A contains“Employee Name”, Column B contains “Date of Birth” and in Column C we need to calculate the age as of today.
To calculate age from date of birth, follow the below mentioned steps:
- Select the Cell C2 and write the formula for age calculation
- Press enter on the keyboard
- The function will return the age of an employee
- If you want age along with the text (Years), you have to write this formula =INT ((TODAY ()-B2)/365) &” Years”.
- Copy the formula by pressing the key “CTRL+C” and paste in the range C3:C5 by pressing the key “CTRL+V” on the keyboard.
Paste Special: You can use the Paste Specialcommand to paste the specific cell content or attributes such as formula, formatting, comments etc.
- Select the range C2:C5 and copy by pressing the key “CTRL+C” on the keyboard, then right click on the mouse, select “Paste Special” from the popup.
- The dialog box will appear, click on “Values”, Click on OK.
Note:- If you want to return the retirement age, you just need to replace the today date from retirement date calculation will work to calculate the retirement age.