While working in Microsoft Excel sometimes we need to calculate age from the current date. This is mostly needed when we are determining the ages of manpower. Today in this article, I am sharing with you ageing formula with current date in Excel. Stay tuned!
Compiling Ageing Formula with Current Date in Excel: 4 Quick Methods
In the following, I have described 4 simple and quick methods of ageing formula with current date in Excel.
Suppose we have a dataset of some Name, Date of Birth. Now we will calculate the Age from the current date using some simple tricks.
1. TODAY and INT Functions to Determine Age from Current Date
You can use the TODAY and INT functions to calculate age from the current date. Follow the instructions below-
Steps:
- First, choose a cell (D5) and apply the formula below-
=INT((TODAY()-C5)/365)
Where,
- The TODAY function calculates today’s date and (TODAY()-C5)/365) in this argument subtracting from a given date in cell (C5). In the last part, dividing with 365 thus showing an output of 29.4493150684931.
- The INT function returns an integer part of the decimal number which is 29.4493150684931. So, the final result stands at 29.
- Second, Hit the ENTER key and drag down the fill handle to fill.
- Finally, you will get the age in years from the current date.
2. Calculating Age from Current Date with YEARFRAC and ROUNDDOWN Functions
If you want you can also combine the YEARFRAC and ROUNDDOWN functions to determine age from the current date in Excel. Just go through the steps below-
Steps:
- Presently, choose a cell (D5) and write the formula down-
=ROUNDDOWN(YEARFRAC(C5,TODAY(),1),0)
Where,
- The TODAY function determines the date of today.
- The YEARFRAC function returns a decimal value in years between the given date in cell (C5) and today’s date.
- In this finishing part, the ROUNDDOWN function will round to the nearest integer from the given values which displays an output of 29.
- Next, press ENTER and pull the fill handle down.
- Within a glimpse of an eye, you will get the output with a rounded current date in your workbook.
3. Ageing Formula with DATEDIF and TODAY Functions
In some cases, to get the proper output you can apply the DATEDIF and TODAY functions to determine age in your worksheet.
Steps:
- Start with selecting a cell (D5) and applying the formula from below-
=DATEDIF(C5,TODAY(),"y")
Where,
- The DATEDIF function will provide the difference between two dates given inside an argument.
- Similarly, click the ENTER key from the keyboard and drag the fill handle down to fill the column.
- Within a moment, you will get the ages in the output column calculated with the current date.
4. IF, DATEDIF, and TODAY Functions for Ageing Formula
In order to calculate the exact years, months and days you need to combine the IF, DATEDIF, and TODAY functions.
Steps:
- Simply, choose a cell (D5) and put the below formula down-
=IF(DATEDIF(C5, TODAY(),"y")=0,"",DATEDIF(C5, TODAY(),"y")&" years, ")& IF(DATEDIF(C5, TODAY(),"ym")=0,"",DATEDIF(C5, TODAY(),"ym")&" months, ")& IF(DATEDIF(C5, TODAY(),"md")=0,"",DATEDIF(C5, TODAY(),"md")&" days")
- In this part, IF(DATEDIF(C5, TODAY(),”y”)=0,””,DATEDIF(C5, TODAY(),”y”)&” years, “) → The DATEDIF function returns the difference between two dates in years.
- Then, the IF function returns if the provided output is equal to 0 or not. If the output is False it will move to the next loop and provide the numeric value adding text -years with the output. Thus the result stands to 29 years.
- Similarly, for the other functions, it runs and finally returns an output of “29 years, 5 months, 4 days”.
- In the same fashion, press ENTER and pull the fill handle down to fill the other cells.
- In conclusion, we will get the exact years, months, and days calculated from the current date in Excel.
Download Practice Workbook
Conclusion
In this article, I have tried to cover all the methods of ageing formula with current date in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.
<< Go Back to Ageing | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!