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!
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Quick Methods of Compiling Ageing Formula with Current Date in Excel
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
- First, choose a cell (D5) and apply the formula below-
- 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
- Presently, choose a cell (D5) and write the formula down-
- The TODAY function is determining the date of today.
- The YEARFRAC function is returning 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.
- How to Calculate Aging of Accounts Receivable in Excel
- Applying Formula to Find Defect Aging in Excel (with Easy Steps)
- How to Make an Accelerated Aging Calculator in Excel
- Use Ageing Formula for 30 60 90 Days in Excel (5 Effective Ways)
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.
- Start with selecting a cell (D5) and applying the formula from below-
- 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.
- 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 stand 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.
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. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.