Finding the exact difference between two dates in Excel may not be a popular use, but it can be a lot of fun. A classic example is working out someone’s age. It’s not as hard as it sounds, and there’s a handy function in Excel that can give you an exact age to the day. Read on for tips on how to calculate age in Excel.
People may not always appreciate that you have a spreadsheet of their exact ages, but it can definitely have its benefits. Here’s how to get started.
Table of Contents
How to calculate age in Excel
1. This is a pretty simple three-column task. To get started, enter the date of a person’s birth into your first cell. In our example, we’re using Tom Hanks, and his date of birth is in cell A2.
2. Now, enter today’s date into the cell next to your first cell. It’s just easier to keep both dates together if you’re tracking multiple ages.
3. In the third cell, for us it’s C2, enter the following formula: =DATEDIF(A2, B2, “y”). The two cell identifiers are fairly straightforward, and using the letter y as the third indicator means that you’re only interested in the number of years.
You can also get a person’s age without entering today’s date in the second cell. To do this, change your formula to =DATEDIF(A2,TODAY(),”y”). Excel will do the rest as far as identifying the date and calculating the age.
If you really want to get specific, you can also calculate a person’s age on a specific date in history. We’re going to get historical with our example and calculate the exact age of Tom Hanks when the film Castaway was released: December 7, 2000.
The formula should look like this: =DATEDIF(A2, DATE(2000,12,7), “y”). Note that the date format is year, month, day.
The final, most specific measurement that you can make is a person’s age, including months and days. The formula gets a little bit longer than previous measurements, but the process remains the same. Your formula should look like this: =DATEDIF(A2,B2, “y”) & “y” & DATEDIF(A2, B2, “ym”) & “m” & DATEDIF(A2,B2, “md”) & “d.”
While it looks like a mouthful, once you break it down, it makes more sense. You need to have a DATEDIF function for each level of measurement, which means that the first function indicates years. The second function measures the years and months, but the “m” ensures that it only displays the month. The final function measures the differences in months and days without the years and displays the date with “d.”
Now you know how to calculate age in Excel. Please only use your powers for good.
Related: Here’s how to merge cells in Excel
What else can I try?
Now that you’ve mastered one of the more niche Excel functions, what else can you do? For starters, you could make a graph of all of the different ages that you’re calculating. You could also write macros that calculate ages for you automatically. The world of Excel is practically endless if you have the time to practice, and we’re highlighting a new deal that can help you do just that.
It’s called the Complete Excel Bundle: Startup Toolbox, and it includes 12 total modules. You can spend some time practicing almost anything you want to learn, from large spreadsheets to logic functions and conditional formulas. The choice is yours, and you have the freedom to complete each module at your own pace.
The 12 hands-on modules have a combined retail value of $2,800, but you can snap it up for just $39 now on Tech Deals. This is a relatively new learning kit, but more than 400 people have already signed up. You can join them and learn more via the widget below.