How to Calculate Age in Excel in dd/mm/yyyy (2 Easy Ways)

It is one of the most frequent needs for us to calculate someone’s age for various purposes. We can use Excel in this regard very easily and quickly. In this article, I will show you how to calculate age in Excel in dd/mm/yyyy format.


Download Practice Workbook

You can download our practice workbook here for free.


2 Formulas to Calculate Age in Excel in dd/mm/yyyy

You can calculate age in Excel in years, months, or even dates. You can use several functions to calculate age in any of these formats. To calculate age in Excel in specifically dd/mm/yyyy format and to know about the details, go through the full article below.


1. Calculate Current Age in Excel by Combining TODAY and DATEDIF Functions

If you want to calculate age in Excel today you can use the DATEDIF function and the TODAY function.

DATEDIF function is a function that calculates the difference between two dates. It has mainly 3 arguments.

Syntax:  DATEDIF(start_date,end_date,unit)

Syntax of the DATEDIF Function

Start_date: This is the date from which the difference will be calculated

End_date: This is the date to which the difference will be calculated

Unit: This is the first letter of years, months, or dates inside the double-quoted marks to declare the difference in dates will be calculated with respect to days, months, or years.

TODAY function is a function in Excel that returns today’s date. It has no argument.

Syntax of TODAY Function

Say, you have a dataset of 6 persons with their names and birthdays. Now, you want to calculate their age today. Follow the steps below to accomplish this. 👇

Dataset to Calculate Age in Excel

📌 Steps:

  • First and foremost, click on the D5 cell where you want to calculate your age.

Select The Required Cell

  • Following, put an equal sign (=) to start the formula. Subsequently, write the following formula and press the Enter button.
=DATEDIF(C5,TODAY(),"Y")&" Years, "&DATEDIF(C5,TODAY(),"YM")&" Months, "&DATEDIF(C5,TODAY(),"MD")&" Days"

Calculate Age Today in Excel in dd/mm/yyyy

🔎 Formula Breakdown: 

=DATEDIF(C5,TODAY(),”Y”)

This calculates the difference between the C5 cell’s date and today’s date in years.

Result: 35

=DATEDIF(C5,TODAY(),”Y”)&” Years, “

This will concatenate a space, then write Years, add a comma and add another space.

Result: 35 Years,

=DATEDIF(C5,TODAY(),”Y”)&” Years, “&DATEDIF(C5,TODAY(),”YM”)

This will calculate the difference between the C5 cell’s date and today’s date in the remaining months after the completed years and add that with the years’ result.

Result: 35 Years, 9

=DATEDIF(C5,TODAY(),”Y”)&” Years, “&DATEDIF(C5,TODAY(),”YM”)&” Months, “

This will concatenate a space, then write Months, add a comma and add another space.

Result: 35 Years, 9 Months,

=DATEDIF(C5,TODAY(),”Y”)&” Years, “&DATEDIF(C5,TODAY(),”YM”)&” Months, “&DATEDIF(C5,TODAY(),”MD”)

This will calculate the difference between the C5 cell’s date and today’s date in the remaining days after the completed years and months and add that with the years and months result.

Result: 35 Years, 9 Months, 25

=DATEDIF(C5,TODAY(),”Y”)&” Years, “&DATEDIF(C5,TODAY(),”YM”)&” Months, “&DATEDIF(C5,TODAY(),”MD”)&” Days”

This will concatenate a space, then write Days.

Result: 35 Years, 9 Months, 25 Days

  • Consequently, you have calculated the age today for Alex. Following, place your cursor to the bottom right position of the D5 cell. Subsequently, the fill handle will appear. Last but not least, drag the fill handle down to copy the formula for all the other cells.

Drag Fill Handle to Copy Formula

Thus, you can calculate anyone’s age today in Excel in dd/mm/yyyy format. And the whole result sheet will look like this. 👇

Calculated Age in Excel in dd/mm/yyyy


2. Calculate Age Between Any Two Dates in dd/mm/yyyy

Now, suppose you have another dataset with 6 persons’ names and birthdays. But, along with these, here is a fixed date given, on which you have to calculate their age. You can find the age between two given dates using the DATEDIF function. Go through the steps below to do this. 👇

Dataset to Calculate Age at Given Date

📌 Steps:

  • First and foremost, click on the E5 cell where you want to calculate your age.

Select the Required Cell

  • Following, put an equal sign (=) to start the formula. Subsequently, write the following formula and press the Enter button.

=DATEDIF(C5,D5,"Y")&" Years, "&DATEDIF(C5,D5,"YM")&" Months, "&DATEDIF(C5,D5,"MD")&" Days"

Calculate Age at Given Date in Excel in dd/mm/yyyy

🔎 Formula Breakdown:

=DATEDIF(C5,D5,”Y”)

This calculates the difference between the C5 and D5 cell’s date in years.

Result: 35

=DATEDIF(C5,D5,”Y”)&” Years, “

This will concatenate a space, then write Years, add a comma and add another space.

Result: 35 Years,

=DATEDIF(C5,D5,”Y”)&” Years, “&DATEDIF(C5,D5,”YM”)

This will calculate the difference between the C5 and D5 cell dates in the remaining months after the completed years and add that with the years’ results.

Result: 35 Years, 8

=DATEDIF(C5,D5,”Y”)&” Years, “&DATEDIF(C5,D5,”YM”)&” Months, “

This will concatenate a space, then write Months, add a comma and add another space.

Result: 35 Years, 8 Months,

=DATEDIF(C5,D5,”Y”)&” Years, “&DATEDIF(C5,D5,”YM”)&” Months, “&DATEDIF(C5,D5,”MD”)

This will calculate the difference between the C5 and D5 cell’s date in the remaining days after the completed years and months and add that with the years and months result.

Result: 35 Years, 8 Months, 5

=DATEDIF(C5,D5,”Y”)&” Years, “&DATEDIF(C5,D5,”YM”)&” Months, “&DATEDIF(C5,D5,”MD”)&” Days”

This will concatenate a space, then write Days.

Result: 35 Years, 8 Months, 5 Days

  • Consequently, you have calculated the age at this given date for Alex. Following, place your cursor to the bottom right position of the E5 cell. Subsequently, the fill handle will appear. Last but not least, drag the fill handle down to copy the formula for all the other cells.

Drag Fill Handle to Copy Formula

Thus, you can calculate anyone’s age today in Excel in dd/mm/yyyy format. And the whale result sheet will look like this. 👇

Calculated Age in Excel in dd/mm/yyyy


Some Other Formulas to Calculate Age in Years Only

Apart from the way described earlier, you can also use some other formulas to calculate age in Excel if you want to find your age in years.

1. Using INT Function

You can find the age of a person in years by using the INT function simply. Follow the steps below to do so. 👇

📌 Steps:

  • First and foremost, click on the D5 cell where you want to calculate your age.

Select the Required Cell

  • Following, put an equal sign (=) to start the formula. Subsequently, write the following formula and press the Enter button.
=INT((TODAY()-C5)/365)

Use INT Function to Calculate Age in Excel

🔎 Formula Breakdown:

(TODAY()-C5)

This will calculate the difference between today’s date and the C5 cell’s date in days.

Result:13082

(TODAY()-C5)/365

This will make the days result into years result. 

Result: 35.84.

INT((TODAY()-C5)/365)

This will make the year’s decimal result in the nearest smaller integer number.

Result: 35

  • Consequently, you have calculated the age in years today for Alex. Following, place your cursor to the bottom right position of the D5 cell. Subsequently, the fill handle will appear. Last but not least, drag the fill handle down to copy the formula for all the other cells.

Drag the Fill Handle to Copy the Formula

Thus, you can calculate everyone’s age in years. For example, the result sheet will look like this. 👇

Calculated Age in Excel


2. Using YEARFRAC Function

Besides, you can use the YEARFRAC function to calculate age in Excel if you want to find your age in years. Follow the steps below to accomplish this. 👇

📌 Steps:

  • First and foremost, click on the D5 cell where you want to calculate your age.

Select the Required Cell

  • Following, put an equal sign (=) to start the formula. Subsequently, write the following formula and press the Enter button.
=ROUNDDOWN(YEARFRAC(C5,TODAY(),1),0)

Use the YEARFRAC Function to Calculate Age in Excel

🔎 Formula Breakdown:

YEARFRAC(C5,TODAY(),1)

This calculates the actual year difference between the C5 cell date and today’s date.

Result: 35.81

ROUNDDOWN(YEARFRAC(C5,TODAY(),1),0)

This round downs the previous result with zero decimal points.

Result: 35

  • Consequently, you have calculated the age in years today for Alex. Following, place your cursor to the bottom right position of the D5 cell. Subsequently, the fill handle will appear. Last but not least, drag the fill handle down to copy the formula for all the other cells.

Drag the Fill Handle to Copy the Formula

Thus, you can calculate everyone’s age in years. For example, the result sheet will look like this. 👇

Calculated Age in Excel


Conclusion

So, in this article, I have shown you how to calculate age in Excel in dd/mm/yyyy format. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo