Do you need a formula or function to calculate age in Excel?
They found him.
In this tutorial I will show you some simple methods and functions to calculate a birth date or convert it into an age. So you can know the exact number of years, months or days from today or any given date.
Let’s just get started.
What is the Age function in Excel?
Excel does not yet have a function like age. Microsoft Excel does not have a function specifically designed to calculate age or expiration dates. However, using other relevant functions, you can successfully convert any birth date into an age. In fact, the features currently in use do the job so well that there is no need to make a new feature obsolete.
Here are some of the simplest methods or functions that can be used to create age formulas.
Method 1: Using the DATEDIF() and TODAY() functions
The Excel function DATEDIF() returns the difference between two dates in years, months, or days. And as we all know, you have to subtract your birth date from your current date to calculate someone’s age.
So thanks to these two functions (DATEDIF and TODAY), we can find the difference between today’s date and any date of birth.
So, to write a formula that calculates age in Excel, open Excel and follow these steps:
- For example, enter the date of birth in a single cell. B. in cell B3 (see screenshot below).
- Type =Today() in another cell, cell C3 (see screenshot below).
NOTE: If you want to calculate the age at a certain date, replace the TODAY() function with the target date.
- Now use the DATEDIF function below in cell D3 to calculate the age (which is the difference between your birth date and today’s date).
(See screenshot below)
The formula is explained:
Syntax: DATEDIF(start_date, end_date, unit)
Formula actually used=DATEDIF(B3,C3,Y)
Function DATEDIF takes the following arguments:
- Start_date is a necessary argument. As the name implies, this is the start date of the period. In this example, the date of birth indicated on the form is used.
- End_date is also a necessary argument. It is the last date or cut-off date of the period. Today’s date is used here. You can also use a specific date to find the age at that date (it can be an earlier or future date).
- The unit of time is the unit in which we need information. Age is usually counted in years. Therefore, we used the Y argument to give us the age in years. This last argument allows you to calculate a person’s age in years, months, and days, as in the following example.
How to calculate age in years, months and days
To calculate a person’s age using all three time units (years, months, and days), use the Concatenate function to combine the three different formulas, each for one time unit.
First, here is the formula for calculating the annual value for a given age:
=DATEDIF(B3, C3, Y) or =DATEDIF(B3, TODAY(), Y)
Use the following formula to calculate the monthly value of age:
=DATEDIF(B3,C3, YM) or =DATEDIF(B3,TODAY(), YM)
Use the following formula to calculate the daily value for age:
=DATEDIF(B3,C3, MD) or =DATEDIF(B3,TODAY(), MD)
When all three formulas are complete, you can now combine them into one formula to get a result of 35y 7m 10d. Where they stayed for years, I stayed for months and days.
Look at the example in the screenshot below:
The formula used here is shown in full below if you want to copy and paste it:
=DATEDIF(B3,C3,Y) & y & DATEDIF(B3,C3,YM) & m & DATEDIF(B3,C3,MD) & d.
=DATEDIF(B3,TODAY(), Y) & y & DATEDIF(B3,TODAY(), YM) & m & DATEDIF(B3,TODAY(), MD) & d.
You can use a cell reference to refer to the current date, or you can use TODAY() as shown in the formulas above.
Using DATEDIF() is just one way to calculate the age of people in Excel. We will explore the option in more detail below.
Option 2: Using functionTODAY()
When you use the TODAY() function, the date formula in Excel works as follows:
=(TODAY()-date of birth)/365.
Normally, to calculate a person’s age, the date of birth is subtracted from the current date. This is the idea of the above age formula.
To illustrate this, in the following worksheet we enter the date of birth in cell C3, and from cell C3 to cell C7.
The formula now looks like this:
The formula is explained:
(Today()-B2) – This is the first part of the formula. It is a matter of calculating the difference between today’s date and the date of birth in days. Since you get the difference in days, you have to divide this number by 365 to get the number of years.
The formula is very simple and uncomplicated. The only problem is that it returns decimal numbers. You will also notice that the first time you try to write a formula, a date is returned.
If your form returns a date, it’s because the form is subtracting dates. You just need to change the format of the formula’s cell number from date to date. To do this, select Number from the Number Format drop-down list in the Number group.
Another small problem you may notice is the decimal point problem. To avoid decimals and display the number of full years, use the INT function to round decimals to the nearest whole number.
Here are some of the simplest ways and formulas to calculate age in Excel.
If you don’t get the points, leave a comment below.
how to calculate date of birth from age in excel, excel formula to calculate age on a specific date, how do i calculate age in mm/dd/yyyy in excel, how to calculate age from date of birth, calculate age in excel in years and months, how to calculate age manually, formula to calculate age from date of birth manually, age calculator in excel sheet free download