Aishwarya Rai Pencil Sketch

Aishwarya Rai Pencil Sketch...

Find Your Age in Excel

Basic Excel formula for age in years
How do you normally figure out someone's age? Simply by subtracting the birth date from the current date. This conventional age formula can also be used in Excel.

Assuming a birthdate is in cell B2, the formula to calculate age in years goes as follows:

=(TODAY()-B2)/365

The first part of the formula (TODAY()-B2) returns the difference between the current date and date of birth is days, and then you divide that number by 365 to get the numbers of years.

The formula is obvious and easy-to-remember, however, there is a tiny problem. In most cases, it returns a decimal number as demonstrated in the screenshot below

To display the number of complete years, use the INT function to round a decimal down to the nearest integer:

=INT((TODAY()-B2)/365)

Drawbacks: Using this age formula in Excel produces pretty accurate results, but not flawless. Dividing by the average number of days in a year works fine most of the time, but sometimes it gets the age wrong. For example, if someone was born on February 29 and today is February 28, the formula will make a person one day older.

As an alternative, you can divide by 365.25 instead of 365 since every fourth year has 366 days. However, this approach is not perfect either. For example, if you are calculating the age of a child who hasn't yet lived through a leap year, dividing by 365.25 produces a wrong result.

Overall, subtracting the birth date from the current date works great in normal life, but is not the ideal approach in Excel. Further on in this tutorial, you will learn a couple of special functions that calculate age faultlessly regardless of the year.

Calculate age from date of birth with YEARFRAC function

A more reliable way to convert DOB to age in Excel is using the YEARFRAC function that returns the fraction of the year, i.e. the number of whole days between two dates.

The syntax of the YEARFRAC function is as follows:

YEARFRAC(start_date, end_date, [basis])

The first two arguments are obvious and hardly require any additional explanation. Basis is an optional argument that defines the day count basis to use.

To make a perfectly true age formula, supply the following values to the YEARFRAC function:

1. Start_date - date of birth.
2. End_date - TODAY() function to return the today's date.
3. Basis - use basis 1 that tells Excel to divide the actual number of days per month by the actual number of days per year.

Considering the above, an Excel formula to calculate age from date of birth is as follows:

YEARFRAC(date of birth, TODAY(), 1)

Assuming the birthdate is in cell B2, the formula takes the following shape:

=YEARFRAC(B2, TODAY(), 1)

Like in the previous example, the result of the YEARFRAC function is also a decimal number. To fix this, use the ROUNDDOWN function with 0 in the last argument because you don't want any decimal places.

So, here's an improved YEARFRAC formula to calculate age in Excel:

=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)

Calculate age in Excel with DATEDIF

One more way to convert date of birth to age in Excel is using the DATEDIF function:

DATEDIF(start_date, end_date, unit)

This function can return the difference between two dates in various time units such as years, months and days, depending on the value you supply in the unit argument:

1. Y - returns the number of complete years between the start and end dates.
2. M - returns the number of complete months between the dates.
3. D - returns the number of days between the two dates.
4. YM - returns months, ignoring days and years.
5. MD - returns the difference in days, ignoring months and years.
6. YD - returns the difference in days, ignoring years.

Since we aim to calculate age in years, we are using the "y" unit:

DATEDIF(date of birth, TODAY(), "y")

In this example, the DOB is in cell B2, and you reference this cell in your age formula:

=DATEDIF(B2, TODAY(), "y")

No additional rounding function is needed in this case because a DATEDIF formula with the "y" unit calculates the number of full years:

How to get age from birthday in years, months and days

As you have just seen, calculating age as the number of full years that the person has lived is easy, but it is not always sufficient. If you want to know the exact age, i.e. how many years, months and days there are between someone's birth date and the current date, write 3 different DATEDIF functions:

To get the number of years: =DATEDIF(B2, TODAY(), "Y")

To get the number of  months: =DATEDIF(B2, TODAY(), "YM")

To get the number of days: =DATEDIF(B2,TODAY(),"MD")

Where B2 is the date of birth.

And then, concatenate the above functions in a single formula, like this:

=DATEDIF(B2,TODAY(),"Y") & DATEDIF(B2,TODAY(),"YM") & DATEDIF(B2,TODAY(),"MD")

The above formula returns 3 numbers (years, months, and days) concatenated in a single text string, as shown in the screenshot below:

Does not make much sense, uh? To make the results more meaningful, separate the numbers with commas and define what each value means:

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

The result looks much better now:

The formula works great, but you can improve it even further by hiding zero values. For this, add 3 IF statements that check for 0's, one per each DATEDIF:

=IF(DATEDIF(B2, TODAY(),"y")=0,"",DATEDIF(B2, TODAY(),"y")&" years, ")& IF(DATEDIF(B2, TODAY(),"ym")=0,"",DATEDIF(B2, TODAY(),"ym")&" months, ")& IF(DATEDIF(B2, TODAY(),"md")=0,"",DATEDIF(B2, TODAY(),"md")&" days")

The following screenshot demonstrates the final Excel age formula in action - it returns age in years, months, and days, displaying only The formula works great, but you can improve it even further by hiding zero values. For this, add 3 IF statements that check for 0's, one per each DATEDIF:

=IF(DATEDIF(B2, TODAY(),"y")=0,"",DATEDIF(B2, TODAY(),"y")&" years, ")& IF(DATEDIF(B2, TODAY(),"ym")=0,"",DATEDIF(B2, TODAY(),"ym")&" months, ")& IF(DATEDIF(B2, TODAY(),"md")=0,"",DATEDIF(B2, TODAY(),"md")&" days")

The following screenshot demonstrates the final Excel age formula in action - it returns age in years, months, and days, displaying only non-zero values: values:

Tip. If you are looking for an Excel formula to calculate age in years and months, take the above formula and remove the last IF(DATEDIF()) block that computes days.

Specific formulas to calculate age in Excel

The generic age calculation formulas discussed above work great in most cases. In some situations, however, you may need something very specific. Of course, it's not possible to cover every and each scenario, but the following examples will give you some ideas on how you can tweak an age formula depending on your particular task.

How to calculate age on a specific date in Excel

If you want to know someone's age at a certain date, use the DATEDIF age formula discussed above, but replace the TODAY() function in the 2nd argument with the specific date.

Assuming the birth date is in B1, the following formula will return age as of 1 January 2020:

=DATEDIF(B1, "1/1/2020","Y") & " Years, " & DATEDIF(B1, "1/1/2020","YM") & " Months, " & DATEDIF(B1, "1/1/2020", "MD") & " Days"

To make your age formula more flexible, you can input the date in some cell and reference that cell in your formula:

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

Where B1 is the DOB, and B2 is the date on which you want to calculate age.

Calculate age in a certain year

This formula comes in handy in situations when the complete date to calculate at is not defined, and you know only the year.

Let's say you are working with a medical database, and your goal is to find out the patients' age at the time they underwent the last full medical examination.

Assuming the dates of birth are in column B beginning with row 3, and the year of the last medical examination is in column C, the age calculation formula goes as follows:

=DATEDIF(B3,DATE(C3, 1, 1),"y")

Because the exact date of the medical examination is not defined, you use the DATE function with an arbitrary date and month argument, e.g. DATE(C3, 1, 1).

The DATE function extracts the year from cell B3, makes a complete date using the month and day numbers that you supplied (1-Jan in this example), and passes that date to DATEDIF. As the result, you get the patient's age as of January 1 of a particular year:

Find out a date when a person attains N years of age

Supposing your friend was born on 8 March 1978. How do you know on what date he completes his 50 years of age? Usually, you'd simply add 50 years to the person's birthdate. In Excel, you do the same using the DATE function:

=DATE(YEAR(B2) + 50, MONTH(B2), DAY(B2))

Where B2 is the date of birth.

Instead of hard-coding the number of years in the formula, you can reference a certain cell where your users can input any number of years (F1 in the screenshot below):

Calculate age from day, month and year in different cells

When a birthdate is split into 3 different cells (e.g. year is in B3, month in C3 and day in D3), you can calculate age in this way:

Get the date of birth by using the DATE and DATEVALUE functions:

DATE(B3,MONTH(DATEVALUE(C3&"1")),D3)

Embed the above formula into DATEDIF to calculate age from date of birth in years, months, and days:

=DATEDIF(DATE(B3,MONTH(DATEVALUE(C3&"1")),D3),TODAY(),"y") & " Years, "& DATEDIF(DATE(B3,MONTH(DATEVALUE(C3&"1")),D3),TODAY(),"ym") & " Months, "&  DATEDIF(DATE(B3,MONTH(DATEVALUE(C3&"1")),D3),TODAY(),"md") & " Days"

Excel MATCH Function

Summary 

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Purpose 

Get the position of an item in an array

Return value 

A number representing a position in lookup_array.

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments 

• lookup_value - The value to match in lookup_array.
• lookup_array - A range of cells or an array reference.
• match_type - [optional] How to match, specified as -1, 0, or 1. Default is 1.

Usage notes 

Use the MATCH function to get the relative position of an item in an array. Match offers several different matching modes, which makes it more flexible than other lookup functions. Used together with INDEX, MATCH can retrieve the value at the matched position.

MATCH returns a position. To retrieve a value, see How to use INDEX and MATCH.

Match type information

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.
• If match_type is 0, MATCH finds the first value exactly equal to lookup_value. lookup_array does not need to be sorted.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.
• If match_type is omitted, it is assumed to be 1.
• Note: All match types will find an exact match.

Basic exact match

When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:

=MATCH(E2,B3:B11,0)

Basic approximate match

When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:
=MATCH(E2,B3:B11,1)





Basic wildcard match

When match type is set to zero, MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is:

=MATCH(E2,B3:B11,0)



This is equivalent to:

=MATCH("pq*",B3:B11,0)



Notes:

Match is not case-sensitive.
Match returns the #N/A error if no match is found.
The argument lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
If match_type is 0 and lookup_value is text, the wildcard characters question mark (?) and asterisk (*) can be used in lookup_value. 
If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Excel HLOOKUP Function

Summary 

HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

Purpose 

Look up a value in a table by matching on the first row

Return value 

The matched value from a table.

Syntax 

=HLOOKUP (value, table, row_index, [range_lookup])

Arguments 

• value - The value to look up.
• table - The table from which to retrieve data.
• row_index - The row number from which to retrieve data.
• range_lookup - [optional] A boolean to indicate exact match or approximate match. Default = TRUE = approximate match.

Usage notes 

HLOOKUP searches for a value in the first row of a table. At the match column, it retrieves a value from the specified row. Use HLOOKUP when lookup values are located in the first row of a table. Use VLOOKUP when lookup values are located in the first column of a table.

• Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
• Set range_lookup to FALSE to require an exact match.
• If range_lookup is TRUE (the default setting), a non-exact match will cause the HLOOKUP function to match the nearest value in the table that is still less than value.
• When range_lookup is omitted, the HLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
• If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, HLOOKUP may return an incorrect or unexpected value.
• If range_lookup is FALSE (require exact match), values in the first row of table do not need to be sorted.

Covert Large Numbers into thousands or million format

Covert Large Numbers into thousands or million format

• For converting the numbers into thousand use the code- 0.0, “k”
• For converting the numbers into million use the code- 0.0,, “Mn”
• Applying a comma (,) after the 0 means dividing the number by 1000, similarly applying 2 commas means dividing by 10 ^ 6 (1,000,000)
• This is an extremely good way to trim your numbers to shorter format without actually affecting the number