Home Loan Other Loan Calculation, Cell Referencing, and Age Calculation | Latest 2020 |

Home Loan Other Loan Calculation, Cell Referencing, and Age Calculation

 

Calculate Age in Excel – Years Only

Suppose you have the date of birth in cell B1, and you want to calculate how many years have elapsed since that date, here is the formula that’ll give you the result:

=DATEDIF(B1,TODAY(),"Y")

If you have the current date (or the end date) in a cell, you can use the reference instead of the TODAY function. For example, if you have the current date in cell B2, you can use the formula:

=DATEDIF(B1,B2,"Y")

DATEDIF function is provided for the compatibility with Lotus 1-2-3.

One of the things that you’ll notice when you use this function is that there is no IntelliSense available for this function. No tooltip appears when you use this function.

This means that while you can use this function in Excel, you need to know the syntax and how many arguments this function takes.

If you’re interested in knowing more about DATEDIF function, read the content of the box below. If not, you can skip this and move to the next section.

Syntax of DATEDIF function:

=DATEDIF(start_date, end_date, unit)

It takes 3 arguments:

• start_date: It’s a date that represents the starting date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().
• end_date: It’s a date that represents the end date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().
• unit: This would determine what type of result you get from this function. There are six different output that you can get from the DATEDIF function, based on what unit you use. Here are the units that you can use:
  • “Y” – returns the number of completed years in the specified time period.
  • “M” – returns the number of completed months in the specified time period.
  • “D” – returns the number of completed days in the specified period.
  • “MD” – returns the number of days in the period, but doesn’t count the ones in the Years and Months that have been completed.
  • “YM” – returns the number of months in the period, but doesn’t count the ones in the years that have been completed.
  • “YD” – returns the number of days in the period, but doesn’t count the ones in the years that have been completed. 

You can also use the YEARFRAC function to calculate the age in Excel (in years) in the specified date range.

Here is the formula:

=INT(YEARFRAC(B1,TODAY()))

The YEARFRAC function returns the number of years between the two specified dates and then the INT function returns only the integer part of the value.

NOTE: It’s a good practice to use the DATE function to get the date value. It avoids any erroneous results that may occur when entering the date as text or any other format (which is not an acceptable date format).

Calculate Age in Excel – Years, Months, & Days

Suppose you have the date of birth in cell A1, here are the formulas:

To get the year value:

=DATEDIF(B1,TODAY(),"Y")

To get the month value:

=DATEDIF(B1,TODAY(),"YM")

To get the day value:

=DATEDIF(B1,TODAY(),"MD")

Now that you know how to calculate the years, months and days, you can combine these three to get a text that says 26 Years, 2 Months, and 13 Days. Here is the formula that will get this done:

=DATEDIF(B1,TODAY(),"Y")&" Years "&DATEDIF(B1,TODAY(),"YM")&" Months "&DATEDIF(B1,TODAY(),"MD")&" Days"

Note that the TODAY function is volatile and its value would change every day whenever you open the workbook or there is a change in it. If you want to keep the result as is, convert the formula result to a static value.