Tanjim Reza
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)
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.
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. 👇
📌 Steps:
- First and foremost, click on the D5 cell where you want to calculate your age.
- 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"
🔎 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.
Thus, you can calculate anyone’s age today in Excel in dd/mm/yyyy format. And the whole result sheet will look like this. 👇
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. 👇
📌 Steps:
- First and foremost, click on the E5 cell where you want to calculate your age.
- 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"
🔎 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.
Thus, you can calculate anyone’s age today in Excel in dd/mm/yyyy format. And the whale result sheet will look like this. 👇
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.
- Following, put an equal sign (=) to start the formula. Subsequently, write the following formula and press the Enter button.
=INT((TODAY()-C5)/365)
🔎 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.
Thus, you can calculate everyone’s age in years. For example, the result sheet will look like this. 👇
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.
- 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)
🔎 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.
Thus, you can calculate everyone’s age in years. For example, the result sheet will look like this. 👇
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!
