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).

Age Function: How to Calculate Age in Excel from Date of Birth

  • Type =Today() in another cell, cell C3 (see screenshot below).

Age Function: How to Calculate Age in Excel from Date of Birth

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)

Age Function: How to Calculate Age in Excel from Date of Birth

The formula is explained:

Syntax: DATEDIF(start_date, end_date, unit)

Formula actually used=DATEDIF(B3,C3,Y)

Function DATEDIF takes the following arguments:

  1. 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.
  2. 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).
  3. 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:

Age Function: How to Calculate Age in Excel from Date of Birth

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.

Or

=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:

Age Function: How to Calculate Age in Excel from Date of Birth

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.

Age Function: How to Calculate Age in Excel from Date of Birth

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.

Age Function: How to Calculate Age in Excel from Date of Birth

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 excelexcel formula to calculate age on a specific datehow do i calculate age in mm/dd/yyyy in excelhow to calculate age from date of birthcalculate age in excel in years and monthshow to calculate age manuallyformula to calculate age from date of birth manuallyage calculator in excel sheet free download

You May Also Like

How To Apply Online Checking Account

Chequing accounts in America are essentially accounts with different banks for expenses.…

android studio “Use gradle wrapper” grayed out –

Development issue/problem: I’m trying to import an Eclipse project into Android Studio.…

How to enable Reader View in Chrome on Mac [2 Best Ways]

If you have already used Safari or Firefox on the Mac, you…

Stylus Pen Compatibility For Touchscreen Tablets – Best Tablets For Best Prices

December 2020 Ernest If you are a seasoned graphic designer, a tablet…