8 Ways to Calculate Age Using Formula on a Specific Date in Excel

Want to Calculate Age on a Specific Date using Excel formula but don’t know how? We are here for you. We have accumulated 8 different methods for you to choose from in calculating age. Hope you like them.


📁 Download Excel File

You can download this workbook to practice for yourself.


Calculate Age on a Specific Date Using Excel Formula with These 8 Methods

Today we have discussed 8 simple and innovative methods to Calculate Age on a Specific Date using an Excel formula. We will use a dataset of different girls with their birthdates and we will calculate age on the current date and also on any given date. Hope you will enjoy them.

dataset age on a specific date formula

Method 1

1. Applying TODAY and INT Functions

Let’s start with a simple formula. If we want to calculate someone’s age from the current date, we usually subtract the birth year from the present year. That is what we are going to do in this method by using the TODAY function.

Let’s see how to use the TODAY formula to calculate age from the current date in Excel. In our example, we have C column where there are some Birth dates. We want to show these girls’ age in years in column D.

⬇️⬇️ STEPS ⬇️⬇️

  • First, Select Cell C6.
  • Next, we go to the Formula bar where we type the following formula:

=(TODAY()-C6)/365

today function age on a specific date formula

  • Then, when you hit Enter, you will see the age in fractions. We will use the Fill Handle to do the same for every Row.

today result age on a specific date formula

If you don’t want your age to be in fractions we can remedy that too. We will use the INT Function. This function takes any number as argument and returns the closest integer to the argument.

⬇️⬇️ STEPS ⬇️⬇️

  • Select, Cell E6.
  • Then, we go to Formula Bar where we type this formula:

=INT((TODAY()-C6)/365)

int today age on a specific date formula

  • At last, we hit Enter and you will see the age in integer format. We use Fill Handle to copy the formula in every Row.

int today result age on a specific date formula

In the first formula,(TODAY()-C6) calculates the difference between the present date and date of birth in days which is why we divided the result by 365 to get the Years. To include leap years in your results divide by 365.25. As you can see it returns our age in fractions so we used the INT function before the original formula in the second one to get our age in integer format.

Method 2

📕 Read More: 4 Examples to Calculate Dividend Growth Rate in Excel


2. Using Year Function

We can also use another simple Function which is the YEAR(your data cell reference) Function. Using the YEAR Function, we will be able to calculate age by subtracting the date of birth from the current year. Along with that, we will also use the NOW function for our calculations.  This function returns the current date and time. Follow the following steps to see how.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want to see your age. In the following example, we selected E6.
  • Now, type the following formula in the Formula Bar.

=(YEAR(NOW())-YEAR(C6))

year now age on a specific date formula

  • Then we hit Enter to see our age. To show everyone’s age, use the Fill Handle to copy the formula in every Row.

year now result age on a specific date formula

We can also calculate age on a specific date other than the current date. Let’s do that step by step.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell no you want your result to be in. We selected F6.
  • After that, we use the following formula in the Formula Bar. In cell D5, we have our specific dates and in cell C6 we have our birth dates.

=YEAR(D5)-YEAR(C6)

year age on a specific date formula

  • Finally, we hit Enter to get our age. Like always, use the Fill Handle to copy the formula till F12.

year result age on a specific date formula

Method 3

📕 Read More: How to Calculate Average Tenure of Employees in Excel


3. Combining YEARFRAC and ROUNDDOWN Functions

Another prudent way to Calculate age on a specific date in Excel is the YEARFRAC function. But before using that, let’s know about this function in depth.

The format of the function is as follows:

YEARFRAC(start_date, end_date, [basis])

Here,

Start date– Date of Birth

End date– The Date you want to calculate your age on

Basis– Basis has a couple of numbers to represent different things. In this example,we will use 1 which dictates Excel to divide the actual number of days per month by the actual number of days per year.

Let’s dive into Excel to see how it works.

⬇️⬇️ STEPS ⬇️⬇️

  • We’ll select E6 in our example.
  • Then, we go to the Formula Bar where we type =YEARFRAC(. We select the cell where our Date of Birth is, C6. Next type comma(,) and Today() and again comma(,).
  • Finally, we give basis number 1 and close the bracket.
=YEARFRAC(C6,TODAY(),1)

yearfrac today age on a specific date formula

  • Hit Enter to see the result. Use the Fill Handle to do the same thing for every row.

yearfrac result age on a specific date formula

Here, you can see we have found a fraction result. We can avoid that by using the ROUNDDOWN function as shown here.

⬇️⬇️ STEPS ⬇️⬇️

  • Select
  • Now,in the Formula Bar,we type =ROUNDDOWN(YEARFRAC( and then select our cells where we have our Date of Birth,type TODAY() and give a Basis number.Then we close bracket for the YEARFRAC function.
  • Before closing the bracket for the ROUNDDOWN function we will give 0 so that the function doesn’t return any decimal places. Follow the below formula if you have any confusion.

=ROUNDDOWN(YEARFRAC(C6,TODAY(),1),0)

rounddown yearfrac today age on a specific date formula

  • Hit Enter to see your desired result. As always, use Fill Handle to cover every Row.

rounddown yearfrac today result age on a specific date formula

We can also Calculate Age on a specific date using this Excel formula. Just use the cell reference where your date is on the ‘end date’ section instead of using Today(). We have done that in our example.

=ROUNDDOWN(YEARFRAC(C6,D6,1),0)

rounddown yearfrac date age on a specific date formula

Apply Fill Handle to copy the formula to the entire Column.

rounddown yearfrac date result age on a specific date formula

Method 4

📕 Read More: How to Calculate Overdue Days in Excel


4. Applying DATEDIF function

One of the convenient ways of calculating age on a specific date in Excel is with the DATEDIF function. Let’s discuss this function first.

Format of the Function:

DATEDIF(start_date, end_date, unit)

This function provides the age difference on a specific date with years, months, and days. It depends on the Unit you supply in the unit argument. The arguments are as follows.

  • Y – returns the age in years between the start and end dates.
  • M – returns the age in months between the dates.
  • D – returns the age in days between the two dates.
  • YM – returns months of the last year without days and years.
  • MD – returns the days of the last month without months and years.
  • YD – returns the difference in days between the start and end dates, without calculating years.

We are going to show you how you can find out age in years, and months and also we will find the last months in the last provided dates. As you can see in our example we have three different columns for finding Years, Months, and Last months.

⬇️⬇️ STEPS ⬇️⬇️

  • First, Select E6 and go to Formula Bar.
  • Second, Type in the following formula. We selected C6 as it contains the Date of Birth and as the end date, we selected a specific date which is in D5.

=DATEDIF(C6,D6,"y")

  • We also put “Y” as the unit to tell Excel to give us Years between our dates.

Datedif year age on a specific date formula

  • Hit Enter and you will get your result. Drag your mouse to the end of the column using Fill Handle to copy the formula.

Datedif year result age on a specific date formula

  • You can enter “M” to get the total months.

Datedif month age on a specific date formula

  • Repeat Enter and Fill Handle combination.

Datedif month result age on a specific date formula

  • You can also enter “YM” to get the last months as a unit.

Datedif r month age on a specific date formula

  • Again, Press Enter and use the Fill Handle combination.

Datedif r month result age on a specific date formula

Method 5

5. Utilizing DATE Function with DATEDIF

When your date is split among 3 different cells, you can use the DATE function in conjunction with DATEDIF. But we have to let Excel find those values as Years, Months, and days so that with the DATE function it can formulate the Date. Like this,

DATE(year,month,day)

In the year we have to input the cell no, where the year resides and likewise in month and day we will provide the cell references to where months and days’ data are located. Let’s calculate age using DATEDIF and DATE functions.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select G5 and place your mouse in Formula Bar.
  • Then type the following formula.
=DATEDIF(DATE(C7,D7,E7),F7,"y")

datedif date age on a specific date formula

  • Press the Enter key and you will get your result. Use Fill Handle to copy the formula to the entire column.

datedif date result age on a specific date formula

  • To get Years, Months, and Days all at once, just type the following formula.

=DATEDIF(DATE(C7,D7,E7),F7,"Y") &" Years " & DATEDIF(DATE(C7,D7,E7),F7,"YM") & " Months " & DATEDIF(DATE(C7,D7,E7),F7,"MD") & " Days"

datedif ymd age on a specific date formula

  • Finally, Press Enter and use Fill Handle to drag the formula to the entire column.

datedif ymd result age on a specific date formula

As you can see we used the DATEDIF function three times to get Years, Months, and Dates. You can also notice that after each time we provided “ years”,” months” and “ days” text strings to show the results.

Method 6

📕 Read More: 5 Ways to Use Formula to Find Next Month in Excel


6. Working with DATEDIF and CONCATENATE Functions

If your dates are in one cell, you can use CONCATENATE with DATEDIF functions to calculate age on a specific date. Let’s see how it’s done.

⬇️⬇️ STEPS ⬇️⬇️

  • First, Select Cell E6, and in the Formula Bar, enter the following formula.

=CONCATENATE(DATEDIF(C6,D6,"Y")," Years ",DATEDIF(C6,D6,"YM")," Months ",DATEDIF(C6,D6,"MD")," Days")

concatenate datedif age on a specific date

  • Hit Enter and use Fill Handle to copy the formula on the entire column.

concatenate datedif result age on a specific date

Method 7

7. Applying IF with DATEDIF Functions

You may have noticed that In Methods 5 and 6, in a couple of Rows, we found 0 months or 0 days. Now we can command Excel to hide Zero Values. To do that, we will have to add IF statements to each of the DATEDIF functions.IF returns only non-zero values. Let’s dive into the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • As usual, Selecting our Display cell E6, we put our mouse cursor in the Formula Bar.
  • Now, copy or type the following formula which will eliminate non-zero values.

=IF(DATEDIF(C6,D6,"y")=0,"",DATEDIF(C6,D6,"Y")&" Years ")& IF(DATEDIF(C6,D6,"ym")=0,"",DATEDIF(C6,D6,"YM")&" Months ")& IF(DATEDIF(C6,D6,"md")=0,"",DATEDIF(C6,D6,"MD")&" days")

If datedif age on a specific date

  • Press Enter and use Fill Handle to copy the formula on the entire column. You’ll notice all the Zeros have been omitted in the results.

if datedif result age on a specific date

So how does IF work, you may wonder. To understand that Let’s see the format of the IF function.

IF(logical test, [value_if_true], [value_if_false])

The IF function determines whether a condition is true or false and returns one value if the condition is true and returns another if it is false. So in the formula,

Logical Test- we give the conditions we want Excel to check.

Value if true- What we want Excel to print if our given condition is met

Value if false- What we want Excela to print if our given condition is not met

Now if look at the formula we wrote for our example, you will understand that we wanted to avoid any non-zero values. So We give a condition like this: DATEDIF(C6,D5,”y”)=0, meaning ‘if Excel finds any Zero value’, in the value_if_true section we kept it blank which will tell Excel not to print anything and finally in the value_if_false section we gave the formula to print our non-zero results.

Method 8

8. Using VBA

Finally, we have come to our last method where we will be using the VBA script to calculate age on a specific date in Excel. To write VBA code we need to enable the Developer option in our Ribbon. Find here how to access the Developer tab.

After you enable the Developer tab, follow these steps to write your code.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the Developer Tab and click Visual Basic at the left corner of the Ribbon.

  • A window(Microsoft Visual Basic for Applications) will appear where we will write our code to calculate age all at once. Go to Insert and click Module.

  • A white module will show up to write your code. I have already included the code so that you may copy and paste it into your module.
Sub agecalculation()
Dim tillend As Long 
With ThisWorkbook.Worksheets("VBA")
tillend = .Cells(Rows.Count, "C").End(xlUp).Row 
.Range("E6:E" & tillend) = "=YEAR(TODAY())-YEAR(C6)" .Range("F6:F" & tillend) = "=YEAR(D6)-YEAR(C6)"
End With 
End Sub

  • Finally, press F5 or click on Run and click Run Sub/Userform.

You will see all of your results in the Excel workbook.

In the Rows. Count, we gave column C because that’s where our dates of birth are and in the Range property we are telling Excel Three things. First, which Row to start from, second, which Column to print our results, and finally to calculate as long as it gets the Date of Birth. After that, we wrote our formula to calculate age.

📕 Read More: 5 Quick Ways to Use Excel Formula to Count Days from Date


📄 Important Notes

🖊️  Excel date functions will only work from dates 1/1/1900.

🖊️  Before 1900, any date you provide will be stored as text.


📝 Takeaway from This Article

📌  Readers will learn the use of different functions which are explained in simple language.

📌  Readers will be able to use these functions in other works as well.


Conclusion

We have come to the conclusion of our article.I hope with these  Excel functions and formulas you will be able to calculate age on a specific date with ease. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel and you can visit Excelden.com for more Tutorials. Thank you.


Related Articles

(Visited 104 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo