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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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

Read More: How to Calculate Current Age in Excel


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 Handledown 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

Read More: How to Calculate Age Between Two Dates in Excel


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

Read More: How to Calculate Age in Excel in Years and Months


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

Read More: How to Calculate Age on a Specific Date with Formula in Excel


Download Practice Workbook

You can download our practice workbook here for free.


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.


Related Articles


<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo