Shakil Ahmed
Excel can be handy while calculating age, you may wonder how you can do that easily. In this article, I will show you how to calculate age on a specific date with a formula in Excel. For conducting the session, I used Office 365. You can use any version.
Basics of Date Related Functions
It will be fun for you when you get used to several Excel functions. Same as date or time-related functions.
- There is a function called the TODAY function. This function returns the current date If you just write TODAY() and then hit Enter you will get the date of the current day.
- Another function I prefer mentioning here is the NOW function. This function returns the current date and time with formatting. If you write NOW() and then press Enter then the function will return the current date and time.
Before diving into the bigger picture, let’s get to know about today’s practice workbook. There are 3 columns, Name, Date of Birth, and Specific Date. Using the data in this table we will calculate the age on a specific date. I will show you how to calculate age on a specific date using Excel formula in 5 different ways.
1. Using Excel Formula with INT Function to Calculate Age on Specific Date
To calculate age you simply need to get the difference between two dates (Date of Birth and Calculating Date). Since age is calculated on a yearly basis, you will need to divide the difference by 365.
So, the formula will be like this,
But this may give us value in fractions. We don’t want age as a fraction value (unless any rear case). So while writing in Excel we will calculate this within the INT function. So the formula will be,
Let’s see how to calculate age on a specific date using the generic formula in Excel.
Steps:
- In the beginning, select the cell where you want to calculate the Age. here, I selected cell E5.
- Then, in cell E5 write the following formula.
=INT((D5-C5)/365)
- After that, press Enter to get the Age.
🔎 How Does the Formula Work?
- (D5-C5)/365: Here, the value in cell C5 is subtracted from the value in cell D5. And then, the result is divided by 365.
- INT((D5-C5)/365): Now, the INT function returns the integer portion of the decimal number.
- Next, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells.
Read More: How to Calculate Age Between Two Dates in Excel
2. Applying IF Formula to Calculate Age on Specific Date in Excel
If there is a situation where anyone inserts a date older than the Date of Birth, what will happen then? To nullify that kind of situation you can use the IF function. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to calculate Age.
- Secondly, write the following formula in that selected cell.
=IF(D5>C5,INT((D5-C5)/365),"Not Born")
- Thirdly, press Enter and you will get the Age.
🔎 How Does the Formula Work?
- (D5-C5)/365: Here, the value in cell C5 is subtracted from the value in cell D5. And then, the result is divided by 365.
- INT((D5-C5)/365): Now, the INT function returns the integer portion of the decimal number.
- IF(D5>C5,INT((D5-C5)/365),”Not Born”): The IF function checks if the value in cell D5 is greater than the value in cell C5. If the logical_test is True then the formula will return the Age. Otherwise, It will return “Not Born”.
- After that, drag the Fill Handle down to copy the formula to the other cells.
- Lastly, in the following picture, you can see that I have copied the formula to all the other cells and got my desired output.
3. Calculating Age on Specific Date with Excel YEARFRAC Function
You can calculate age using the YEARFRAC function. This function takes three parameters, start_date, end_date, and basis. The basis is the day count base. Like if you use 0 it will count setting the base 360 days in a year. If you use 3, it will count on the base of 365 days in a year. Here, I will use 3.
Writing this function may give a fraction value. So, I will round this using the ROUNDDOWN function. This function takes two parameters. First, the value you want to round, and the second one is how many decimal points you want. Since I don’t want to see any decimal in the Age value, I will use 0.
Let’s see how you can use the YEARFRAC function and the ROUNDDOWN function in Excel formula to calculate age on a specific date.
Steps:
- First, select the cell where you want to calculate the Age. Here, I selected cell E5.
- Next, in cell E5 write the following formula.
=ROUNDDOWN(YEARFRAC(C5,D5,3),0)
- Afterward, press Enter.
🔎 How Does the Formula Work?
- YEARFRAC(C5,D5,3): Here, the YEARFRAC function returns a decimal value that represents the years between the date in cells C5 and D5.
- ROUNDDOWN(YEARFRAC(C5,D5,3),0): Now, the ROUNDDOWN function round downs the decimal value to zero decimal point or you can say integer.
- Then, drag the Fill Handle down to copy the formula to the other cells.
- Consequently, you can see that I have copied the formula to the other cells.
Similar Readings
- How to Calculate Age in Excel in dd/mm/yyyy
- How to Convert Date of Birth to Age in Excel
- Excel VBA: Calculate Age from Date of Birth
4. Applying Excel DATEDIF Formula to Get Age on Specific Date
You can get the age by using the DATEDIF function. This function has three parameters, start_date, end_date, and format. Let’s see how you can employ the DATEDIF function in the Excel formula to calculate the age on a specific date.
Steps:
- Firstly, select the cell where you want the Age. Here, I selected cell E5.
- Secondly, in cell E5 write the following formula.
=DATEDIF(C5,D5,"Y")
- Thirdly, press Enter to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells.
- Finally, you can see that I have copied the formula to the other cells.
5. Determining Age with Years, Months, and Days in Excel
Now, in some situations, you may need to present the age with years, months, and days. Let’s see how you can do that. I will use the DATEDIF function and the Ampersand Operator (&) for this calculation. The Ampersand Operator (&) is used for joining more than one function together. In the beginning, I will calculate the difference in year format, then in months, and then in days.
Let’s see how you can calculate age with years, months, and days on a specific date by using an Excel formula.
Steps:
- In the beginning, select the cell where you want to calculate the Age.
- Then, write the following formula in that selected cell.
=DATEDIF(C5,D5,"Y")&"Y " & DATEDIF(C5,D5,"YM")&"M " &DATEDIF(C5,D5,"MD")&"D"
- Next, press Enter to get the Age.
🔎 How Does the Formula Work?
- DATEDIF(C5,D5,”Y”): Here, the DATEDIF function returns the years between the dates in cells C5 and D5.
- DATEDIF(C5,D5,”YM”): Now, the DATEDIF function returns the number of months between the dates in cells C5 and D5 ignoring the days and years.
- DATEDIF(C5,D5,”MD”): Here, the DATEDIF function returns the number of days between the dates in cells C5 and D5 ignoring the months and years.
- DATEDIF(C5,D5,”Y”)&”Y ” & DATEDIF(C5,D5,”YM”)&”M ” &DATEDIF(C5,D5,”MD”)&”D”: Now, the Ampersand Operator (&) joins the formulas.
- Afterward, drag the Fill Handle down to copy the formula to the other cells.
- In the end, you can see that I have copied the formula and got my desired output.
Read More: How to Calculate Age in Excel in Years and Months
6. Calculating Age from Date of Birth in Excel
In this section, I will show you how you can calculate the age when only the Date of Birth is given. I will use the DATEDIF function and the TODAY function for this calculation. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to calculate the Age. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=DATEDIF(C5,TODAY(),"Y")
- After that, press Enter to get the Age.
🔎 How Does the Formula Work?
- TODAY(): Here, the TODAY function returns the current date.
- DATEDIF(C5,TODAY(),”Y”): Now, the DATEDIF function returns the years between the date in cell C5 and the current date.
- Then, drag the Fill Handle to copy the formula.
- Finally, you can see that I have copied the formula to the other cells and got Age from the Date of Birth.
Download Practice Workbook
Here, I have shared the practice workbook. You can download it from the link below.
Conclusion
That’s all for today. I’ve tried listing a couple of ways to calculate age on a specific date with an Excel formula. Hope that will help you. Feel free to comment if something seems hard to understand. Let us know which of the methods you are going to use. You can write your own way of calculating age.
Related Articles
- How to Calculate Current Age in Excel
- How to Calculate Age in Excel for Entire Column
- How to Calculate Age in Excel from ID Number
- How to Calculate Retirement Age in Excel
- Metabolic Age Calculator in Excel
- How to Calculate Average Age in Excel
- How to Group Age Range in Excel with VLOOKUP
- How to Make Age Pyramid in Excel
- How to Create Age and Gender Chart in Excel
- How to Create Age Distribution Graph in Excel
