Ageing Formula with Current Date in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

TODAY and INT Functions to Determine Age from Current Date

  • 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.

TODAY and INT Functions to Determine Age from 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.

Calculating Age from Current Date with YEARFRAC and ROUNDDOWN Functions

  • 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.

Ageing Formula with DATEDIF and TODAY Functions

  • 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.

ageing formula in excel with 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")
Formula Breakdown:
  • 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”.

IF, DATEDIF, and TODAY Functions for Ageing Formula

  • 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo