Sometimes in Excel, you have two dates and you want to automatically calculate the exact time difference between them automatically. A classic example is working out someone’s age. There’s actually 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.
Not everyone will appreciate you creating a spreadsheet of their exact ages, but it can definitely have its benefits. Here’s how to get started.
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 historic 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 the age of a person 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.
What else can I try?
Now that you’ve mastered one of the stranger 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 200 people have already signed up. You can join them and learn more via the widget below.
Save $2761 .00 $39 .00
Via Phandroid