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.
Download Practice Workbook
Here, I have shared the practice workbook. You can download it from the link below.
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.
5 Easy Methods to Calculate Age on a Specific Date with Formula in Excel
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. Use Generic Formula with INT Function to Get Age on a 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 (6 Useful Methods)
2. Employ IF Function to Calculate Age on a 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.
Read More: How to Subtract Dates in Excel to Get Years (7 Simple Methods)
3. Age Calculation on a 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.
Read More: How to Calculate Average Age in Excel (7 Easy Methods)
Similar Readings
- Excel Formula to Calculate Number of Days Between Today and Another Date
- How to Calculate Years in Excel from Today (4 Ways)
- Excel Formula for Number of Days Between Two Dates
- How to Count Days from Date to Today Automatically Using Excel Formula
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
4. Employ Excel Formula with DATEDIF Function to Get Age on a 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.
Read More: How to Calculate Age from Birthday in Excel (8 Easy Methods)
5. Determine 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 (5 Easy Ways)
How to Calculate 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.
Practice Section
Here. I have provided a practice sheet for you to practice how to calculate age on a specific date using an Excel formula.
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.
