Date of birth is an important attribute that we often need to share while joining a new company, getting a course, health or term insurance, and in a number of other daily life activities. However, it is not the date of birth or the DOB that matters. It is the age that makes the difference. If you have a sheet containing the information of all your clients, it can be useful if you can highlight, or at least find out those clients of a particular age. 

Calculating age is a piece of cake, but it is impractical to calculate the individual age of all the clients, if you have, say, hundreds, or thousands of them. That’s when you need a tool that will automatically update the age for you. So, if you are on Google Sheets, Microsoft Excel, or any other popular spreadsheet program, I am here with how to calculate age with Excel, or how to calculate age with Google Sheets, whatever the program might be in your case.

Once you know the age of a customer, you can send promotional and curated offers to those customers, in the case of an organization, where certain bonuses or benefits need to be paid based on age, this tutorial is going to be helpful for you.

So, without any further delay, let’s get started with how to calculate age with Google Sheets or Excel. I will be illustrating it on Google Sheets, but it is the same on other similar programs.

Calculating age from date of birth in Google Sheets or Excel

Let’s get started with a specific date in cell A2, and we want the age, wrt. the value in the cell A2 in B2.

The formula in cell B2 will go as follows.

=TRUNC(YEARFRAC(A2,today()))

In the same way, if you have multiple cells having multiple date-of-birth, simply replace the cell A2, with the specific cell reference in your case.

The best part of using the above formula is that you will get an exact and real-time age. It means, that if you have entered a record today, and the person has this birthday after 10 days, the age will automatically be updated after 10 days.

What if the input is not recognized as a date?

In the case the input is not recognized as a date, or the date is interpreted as a text, you must make Google Sheets, or your spreadsheet program understand the format.

The process is going to vary from program to program.

Simply select the cell(s) containing the dates, click on ‘Format’, hover over to ‘Number’, and hence click on ‘Custom date and time’.

Now, you can choose any date format of your choice, or enter a new one, by clicking on the different elements of the date, i.e. day, month, and year, or shuffle the positions of the day, month, and year, using the options, as shown in the screenshot below.

However, if the cell content is not recognized as a date, you can use the cell formatting option to format the cell contents as a date, or any other data type.

On Excel, right-click on the cell and select Format Cell. After that, select the Number Tab and set the Date format, if it is not as per the Date of Birth Formula, we used in this article.

Now that you have the age of all the records, from the date of birth, you can use other tools or formulas to highlight specifically those records of a specific age, records where the age is more or less than a specific value, and so on.

The best you can do is, use conditional formatting to highlight specific records, where the age meets a specific criterion. 

So, that’s how to calculate age with Google Sheets, Microsoft Excel, or any other spreadsheet app of your choice. Do you have any further questions regarding the topic? Feel free to comment on the same below.