A subscriber wrote to me recently asking if there was a way in which MS Excel could calculate ages for her. It seems she works in the Human Resource Department of a rather large law firm and the firm has a form that must be filled out for every new hire providing their date of birth and current age.
I was pleased to be able to tell this subscriber that she can calculate age in MS Excel with relative ease.
Follow the steps below to learn how:
- Format the column in which you would like to key in employees’ dates of birth.
- Select the column and in the Cells group of the Home tab, click the Format drop-down arrow.
- Click on Format Cells.
- You can also use the shortcut of right-clicking the selection and choose Format.
- On the Number tab, under Category, select Date and select the format you would like to use.
Now, we will format the cells that will contain the calculated age:
- Under the Number tab, in the Format Cells area, select Number and select 0 under Decimal Places.
- Now that you have finished your formatting, we can enter the necessary formula to calculate age.
- Select the first cell in the column where you would like your calculated ages to appear. For our purposes we will use Column A for dates of birth and Column B is where your calculated age will appear.
- In cell B1, enter the formula below:
If you key in your date of birth in cell A1 and click Enter, your age will now appear in cell B1.
You can copy the formula down using the fill handle or by simply double-clicking the fill handle.
As you can imagine, my subscriber was very happy to have this time-saving forumla!