Find Age in Years within Microsoft Excel [Video Tutorial]

Wondering How To Find the Age in Years from Date of Birth, in Microsoft Excel?

See multiple ways to calculate Age in Excel from any date of birth.

This simple, step-by-step tutorial on finding the age in Excel from date of birth or any other date, work on Microsoft Office 365, Microsoft Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & Excel 2003 too.

We cover Microsoft Excel Functions & Formulas that allow you to calculate the following things in Excel:

  1. Find the age in Years
  2. Find the Age in Months
  3. Find the Age in Days
  4. Finding the exact age in Years, Months & Days.
  5. Combining Years & Months to create a Text String the way we want.

Here’s how to calculate the number of years since the date of birth

Not only the years we can also calculate the months. I will show you multiple techniques to calculate the age in years, months & days.

If you want a whole number or you want the age as a fraction, there are so many ways to do it.

What we want to calculate is the age in years –  something like Age is 55 years. Or Age is 55.56 years.

Before we start i want to highlight to you that the date of birth that you are using should be a date field so you can select it and make sure the formatting is not General or text, but it should be set as a short date or any kind of date.

To calculate the years,  the formula is extremely simple. We will be using the DATEDIF function of Excel.
=DATEDIF(DateofBirth, TODAY(), “Y”)

Similarly, we can calculate the age in months too.

=DATEDIF(DateofBirth, TODAY(), “M”)

This will result in the number of months from the date of birth to today’s date. You might see a huge number like 666 here.

But you may not want this.  Perhaps we only want how many months after the 55 whole years have elapsed.

In this case,  we modify the formula from only “M” to “YM”

=DATEDIF(DateofBirth, TODAY(), “YM”)

YM means it is only the months after the completed years. By doing this we can see that only six months have elapsed after 55 years.

Finally, we only need the days that have elapsed after the completed months in the age.

=DATEDIF(DateofBirth, TODAY(), “D”)

This will tell you that 20,000 days have passed from the date of birth, but, of course, we don’t want this.

So you change D to MD. The “MD” tells Excel to calculate how many days after the month have elapsed. 

As you can see, we have the age calculated in different cells – the age is 55 years 6 months and 9 days exactly till today.

If you wish to see a complete combined date, you can use the CONCAT function to combine multiple formulas, or use the & to combine text as well.

As an example, we can write

=CONCAT(DATEDIF(dateofbirth,TODAY(),”Y”),” Years, “,DATEDIF(dateofbirth,TODAY(),”YM”),” Months & “,DATEDIF(dateofbirth,TODAY(),”MD”),” Days”)

For a DateofBirth date of 6-December-1966, it generates, 55 Years, 6 Months & 28 Days.

Hope you find this DATEDIF function quite handy for such use. Do give it a try and let us know how you use it.

Cheers,
Vinai

Recommended Reading for Next Steps:

  1. Using Date & Time Functions in Excel
  2. Difference Between 2 Dates, Without the Weekends

Beginner’s Guide to Microsoft Excel

Most people just start using Microsoft Excel without much thought. After all, it seems so easy to use Excel.

Yet, if you don’t have a strong foundation of Microsoft Excel, you will begin to struggle after a while… Most people begin to feel overwhelmed when they see

  • begin doing data entry,
  • need to sum or count the data quickly,
  • want to format the data properly.

And if you don’t know the shortcuts to do such things quickly, you’ll be wasting a lot of time, causing delays.

Step by Step Beginner’s Tutorial on Microsoft Excel [2022]

In this step-by-step Excel Beginners tutorial by ExcelChamp Founder Vinai Prakash, we teach you to get started with Excel easily.

This video is created for beginners in Excel. You can use any version of Excel, but you can still benefit from the steps and it will help you in using Excel easily.

In this Excel Beginners Tutorial Video, We show you step by step, how to do the following tasks:

  • Start Excel,
  • Open a blank workbook,
  • Get used to the rows, columns, and cells in Excel
  • How to do data entry in Excel
  • How to cut, copy, paste, move and select multiple cells, rows and columns in Excel,
  • Where is the formula bar in Excel,
  • How to use the formula bar in Excel,
  • Using the name box for navigating within an Excel file
  • Adding multiple sheets in Excel
  • Renaming a sheet in Excel
  • Deleting a sheet in Excel,
  • How to do formatting in Excel – Bold, Center etc.
  • How to Merge Headings in Excel
  • How to add borders
  • How to format the values and text in Excel.

Improve Your Speed, Accuracy, and Competence in Microsoft Excel with a strong foundation in Microsoft Excel.

After viewing the video tutorial carefully, a couple of times,

  • you will become an expert in the basic usage of Microsoft Excel,
  • You will be able to finish your data entry in Excel faster,
  • You will be able to work with multiple Excel sheets, rows, columns, and
  • begin to do data formatting in Excel like a Pro!

I am sure you will enjoy this Excel Beginner’s Video tutorial. And let me know what you would like to learn next… I’ll be happy to receive a message from you – Vinai 🙂

Author: Vinai Prakash

I help Excel users on how to properly and effectively use Microsoft Excel formulas and functions, Pivot Tables, Charts, and Shortcuts with Tips & Tricks to get their day-to-day work like data entry, consolidation, analysis, charting, reporting etc. is done quickly and efficiently, without any errors, every time!

Excel Pivot Table Tutorial

Excel Pivot Table Tutorials: Step By Step Training Videos By ExcelChamp

Pivot Tables are one of the most important features of Microsoft Excel. Pivots help to quickly summarize and analyze any data set.

Almost all Executive, Managerial, Analyst and Serion Management roles require solid understanding and knowledge of Excel Pivot Tables.

Overview of Pivot Tables Training Course

Part 1: Introduction to Pivot Tables in Excel

Part 2: Introduction to Pivot Tables Formatting, Styles & Presentation in Excel

Part 3: Customize a Pivot Table For Best Results in Excel

Part 4: Create Subtotals in Pivot Tables in Excel

Part 5: Sorting Options in Excel Pivot Tables

Part 6: Filtering Data in Excel Pivot Tables

Part 7: Create Calculated Fields & Columns in Excel Pivot Tables

Part 8: Summarize Data With Excel Pivot Tables

How Did You Like This Training?

We hope you enjoyed this Free Pivot Table Training that helps in analyzing data quickly with Excel.

Do let us know how you liked this training. Simply write a comment below the YouTube Videos.

We read each and every comment, and will really appreciate it.

Cheers,
Vinai Prakash
Founder, ExcelChamp.Net

25 Amazing Excel Tips For Massive Productivity Gains

Most people use Microsoft Excel to Organise and Visualize large amounts of Data & Save Time by Getting Complex Tasks Done Faster.

Yes, I find people using long and cumbersome methods to do simple tasks, which makes them much longer to get things done. They are frustrated but think that there is no other way.

Actually, you can improve your productivity by learning some simple tips and tricks, shortcuts, formulas that will save you countless hours.

Also, learn to Analyze Data Quickly with Excel here.

Here’s my collection of the Best & Most Amazing Excel Tips & Tricks that I have compiled for you. I am sure you will learn something new, and gain from these amazing tips that will give you massive productivity gains.

List of Amazing Excel Tips For You

  1. How to Use Relative & Absolute Referencing To Your Advantage
  2. Begin Using Range Names & Get Rid of Absolute Referencing
  3. Master IF Function For Quick Decision Making
  4. Conditionally SUM a few rows only, based on a criterion with SUMIF
  5. Count only a few cells, based on any logic you can think of, by using the COUNTIF function
  6. Go Beyond Formatting Tables to Actually using them For amazing Insights
  7. Stop using Average. Use the Median Function to get a better sense of your data.
  8. Use Special Data Types built into Excel to get Demographic, Weather, and other useful information
  9. Learn to Combine Cells to Make Better Looking Reports
  10. The Amazing Flash Fill can Do wonders to save you time
  11. Learn some Date Magic in Excel
  12. Find the difference between any 2 dates, with or without counting the Weekends – your choice!
  13. Convert any data from Centigrade to Fahrenheit, or centimetres to inches. There are hundreds of free conversions built into Excel for you.
  14. Master Pivot Tables To Analyze Any Data within minutes. Also, check out our Free MasterCourse on Analyzing Data With Pivot Tables on YouTube
  15. Create Self Updating Pivot Charts
  16. Create Beautiful Printouts & Reports With Beautiful Printing Options within Excel
  17. Move around easily by Freezing or Splitting Sheets and Views easily
  18. Don’t have time to analyze data? Can’t figure out where to start? Too much data? Fret not. Get Free Analysis Ideas done for you in a jiffy. And for more Advanced Data Analysis, use the Analysis Toolpak.
  19. Hide or Show Grid Lines to make Excel look like Word or PowerPoint. Plus it makes better output and a pleasing view too.
  20. Setup Links to other sheets or other workbooks with this simple hyperlinking technique and never have to search for your sheets. It is best is to create a Table of Contents with this technique.
  21. Use Color To Enhance your experience working with multiple sheets, and create a livelier Workbook that looks amazing.
  22. Before you analyze anything, remember to Remove Duplicates in a single click
  23. Never get invalid or wrong data in your data. Setup Drop Down Lists so that all data entry is done to conform to the valid values only. Saves countless hours fixing data quality issues. Nip the evil in the bud with proper data validation with drop-down lists.
  24. Moving around? Don’t have your own laptop? Don’t want to pay for an Excel license? No worries. You can use Excel For Free on the web. All you need is an Internet account and a free Microsoft account.
  25. Got a colleague or Team to Brainstorm ideas? Now you can all work on the same Excel file at the same time with Collaboration built right into Microsoft Excel.
Do watch this video a couple of times to make sure you get all the points clearly and master them. Also, don’t forget to like this Amazing Tips & Trick in Excel video, and subscribe to our channel on Youtube by clicking here.

Master These Excel Lookup Functions To Save Time

One of the most popular Excel Lookup functions is VLOOKUP. Another Excel function to check out is the HLOOKUP function, which can do a horizontal lookup.

The newly added XLOOKUP is becoming very popular too. (The XLOOKUP function is currently only available in Office 365 versions).

For the advanced & power users of Excel, the mastery of Excel lookup functions like INDEX, MATCH & OFFSET can be considered vital. These functions are considered the “advanced” lookup functions in Excel.

With the help of these functions, you will be able to analyze data quickly. To learn more, you should enrol in the data analysis and interpretation training class

But with the introduction of XLOOKUP, some of the jugglery created by mixing INDEX & MATCH combination is no longer required.

VLOOKUP Function of Excel

The most MUST HAVE Function ever. Even Excel gurus can’t live without it. I polled a group of Excel experts recently, asking if Excel’s VLOOKUP was overrated. I got a severe backlash for even mentioning it.

Almost everyone said that it is their GO TO function, an absolute must-have and that Excel won’t be that useable if this VLOOKUP function was taken away from Excel!

Most people swear by their VLOOKUP functions. It is their GO TO function when they want to lookup value of any type.

According to legend, VLOOKUP mastery is what separates the Pro Excel users from the Amateurs!

Vlookup is akin to using a dictionary. You know the word, and you want to find out the meaning. This dictionary is the range of cells that contain the lookup up value, and its associated value. The V in VLOOKUP stands for the dictionary data array being a vertical dictionary. So for a vertical lookup you must use VLOOKUP function only.

=VLOOKUP(word, dictionary, column number of meaning, exact_match_ype)

In the VLOOKUP formula, the first column in the dictionary must contain the lookup up value, and the first row should be of the data. You should not include the headings in the dictionary table. The difficulty most people have with VLOOKUP is the last flag – the logical value of TRUE or FALSE (You can use 1 for True and 0 to indicate the False flag).

Once a matching value is found out, you will be able to get the return value based on the search. The error value of N/A will be generated if there is no exact match until the last row or the last column of the array.

The mystery is created because to use VLOOKUP for an exact match, you have to specify the last optional flag, and set its value to a FALSE or a 0. By default, it is set to 1, which is useful for an approximate match type only. So for an exact match of a specific value, the last parameter is not really optional… it is mandatory.

Practical Examples of VLOOKUP:

= VLOOKUP(50, age_income, 2) will find the income from the second column of the age_income range for the age value of 50. Note that this is set as an approximate match lookup, as the last optional value is not specified.

=VLOOKUP(A5, $K$10:$L$100, 2, FALSE)

In this VLOOKUP example, we are looking for a piece of information from the column of data in the range $K$10 to $L$100. The search value is in cell A5, and the particular value of interest is in the lookup array that is set as an absolute reference.

The above example uses the reference functions that will generate an Exact match. If the correct value is not found, an error value of N/A will be received.

There are two major shortcomings in using the VLookup function of Excel.

  • First of all, the VLOOKUP is really a slow function. It is obvious when you do a lookup on a large list of 100,000 values or more.
  • Secondly, VLOOKUP can only look up a corresponding value from the columns on the right of the looked-up value. It can’t look to the left!

Make sure you master this Excel function really well.

HLOOKUP Function in Excel

An often forgotten cousin of the VLOOKUP formula, this Horizontal Lookup and Reference function in Excel works in a similar way too. The only difference is that in this case, a lookup dictionary is a horizontal dictionary of columns, denoted by the H.

HLOOKUP is most used in range lookups, rather than exact matches, as columns are not the best suited for exact values, because of their limit of only16,000 columns. Whereas a list can grow vertically to over a million records easily.

In the following formula, this lookup function searches for the closest match, especially when we are not searching for an exact match, but an approximate match.

The dictionary is the table array and it is recommended that we use the absolute reference to lock the cells from moving.

=HLOOKUP(A5, $G$2:$K$100, 2)

Here the HLOOKUP will search for the exact or the next smallest value in the lookup table source data range of $G$2 to $K$100, and return the second row. If you want the third row, you can change the 2 into a 3.

Both VLOOKUP & HLOOKUP return single-cell values from a single row or a single column.

XLOOKUP Function in Excel

Did you know that new functions are added to Excel quite frequently, and these are extremely useful functions making approximate matches as well as exact matches?

Finally, after years of backlash at Microsoft for creating the mess with the Match Type (True and False) in VLOOKUP, they got rid of it completely in the Excel XLOOKUP function.

By default, XLOOKUP is set to do an exact match.

XLOOKUP requires a deeper understanding of the various scenarios it can handle. I’d recommend a dedicated video tutorial on XLOOKUP by Excel MVP Chandoo to learn how to use XLOOKUP in Excel.

This new XLOOKUP function of Excel is only available from Microsoft Office 365 users. It does not work on Excel 2016 or Excel 2019 versions.

Good knowledge of these lookup functions can help you to prepare the data in advance before you begin to analyze or summarize data with Excel.

Using INDEX Function in Excel

If you know the row number, you can find the value on that row or column cell directly. INDEX can be used as an Array function also. Paired with MATCH, you can find any value on any row or column in a 2-dimensional array.

How To Use Excel MATCH Function

When you want to find an exact match in an array and return the row number in the array, MATCH comes to your rescue.

It is one up on VLOOKUP, which requires you to know the column you want to return. MATCH can find a match for a value that is lower, exactly equal or higher than the specified value.

=MATCH(value, range_Lookup_Cells, FALSE)

This simple example MATCH function will look for the value in the range_Lookup_Cells, and return the row of the matched value exactly.

Index and Match can work on a single row or a single column of data too. These 2 lookup functions of Excel are the exact opposite of each other.

The index finds the value on a particular row, and Match finds the row or column number where the match happens to be found in the data range.

Both Index and Match can also run as an array form of the Excel Lookup Function.

Paired with INDEX, an INDEX & MATCH Function can manage to look up on the left or the right of any array of cells.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Master the OFFSET Function within Excel

To navigate your way in a two-dimensional array of rows and columns, you can use the OFFSET function in Excel. It can traverse any number of Rows or Columns, and get you the value.

How to use the offset function in Excel:

=OFFSET(Starting Cell, Row to move up or down, Columns to move left or right, Number of rows required to be returned, number of columns required to be returned)

I generally use OFFSET more than INDEX and MATCH combinations. Using one super-powerful OFFSET function is more straightforward.

Thee

Once you start using Offset in Excel, you wouldn’t want to use other lookup functions of Excel.

When Do I Use the INDIRECT Function of Excel?

The Excel INDIRECT function returns the reference specified by a string text. Any References are immediately evaluated to display their contents.

You can Use the INDIRECT function when you want to change the reference to a cell within a formula, without changing the formula itself.

=INDIRECT(A3)

The above Indirect function will check what is in cell A3. And A3 will have the cell reference to another cell. So if A3 contains B35, Excel will then read the value in cell B35.

Thus, we can get the value of the reference in cell A3. The reference is to cell B3, which may contain the value 45.

The INDIRECT can be very useful in creating custom management dashboards and reports.

What does the FORMULATEXT Function of Excel Do?

Displays the text of another formula. This formula helps to see all formulas next to their values and can be useful to spot mistakes and issues with formulas.

=FORMULATEXT(A3) will provide you with the formula in cell A3 as a Text Value.

This FormulaText function is useful to see the formula without having to go into Editing mode.

View this article for more information on how to get the Formula of another cell in Excel.

Begin Using ROWS Function of Excel

Displays the row number of a reference cell.

=ROWS(A1:B4)

The above function will return a value of 4. This is because there are 4 Rows in the given range.

Use the COLS Function in Microsoft Excel?

Displays the column number of a reference cell. As evident in the following syntax, COLS function is useful to count the columns in a range.

This can be useful, when combined with INDIRECT and OFFSET, the function begins to help in generating a dynamic range.

=COLS(A1:B4)

Will return a 2. This is because there are 2 Columns in the given range: A & B.

Use the TRANSPOSE Function in Excel like a Pro

Converts rows into columns and columns into rows. Just like the Transpose feature in Paste Special, but done programmatically.

So if you use TRANSPOSE(A1:D3), you have selected 4 columns and 3 rows.

After the Transpose is completed, you will get an array reference of 3 Columns, and 4 Rows. The horizontal table would have flipped and will be visible vertically.

So the next time you have a list of products that you want to flip from rows to columns, you can use the Transpose function.

Pretty nice use of hanging values in rows into columns.

When Do I Use the UNIQUE Function of Excel?

The UNIQUE function of Excel generates a list of unique values that automatically spill down. An array function can be used to create data validation lists too.

Available from Microsoft Office 365 onwards. This UNIQUE function is not available in Excel 2016 or Excel 2019.

=UNIQUE(Data_Range)

Learning the Lookup Functions in Excel Quickly & Easily

As you can see, there are a lot of LOOKUP functions in Excel, and learning and mastering them takes time. But once you do master them, you can do wonders with your Excel skills.

It is worth the effort to learn the Excel Lookup Functions. Call Intellisoft at +65-6296-2995 for Excel 365 Classroom Training that covers the key Lookup functions of Excel.

You will definitely enjoy it!

Cheers,

Vinai

Founder of ExcelChamp. Master Trainer at Intellisoft Systems in Singapore.

Need Tips on Pivot Tables, Data Analysis or Creating Better Charts?

Do check out these resources

And if you want to learn Advanced Excel fast, then check out our detailed guide.

Articles Written By Vinai.

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts online Excel workshops as well as classroom workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Show Buttons
Hide Buttons