Sometimes in Excel you have two dates and you want to automatically calculate the exact time difference between them. A classic example is determining someone's age. There is actually a handy function in Excel that can give you an exact age. Read on for tips on calculating age in Excel.
Not everyone will appreciate that you create a spreadsheet with their exact ages, but it can certainly have its advantages. Here's how to get started.
How to calculate the age in Excel
1. This is a fairly simple three-column task. To start, enter a person's date of birth in your first cell. In our example, we use Tom Hanks and his date of birth is in cell A2.
2. Now enter today's date in 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 C2, enter the following formula: = DATEDIF (A2, B2, "y") . The two cell IDs are pretty straightforward, and using the letter y as the third indicator means you're only interested in the number of years.
You can also retrieve 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 does the rest as far as it identifies the date and calculates the age.
If you really want to get specific, you can also calculate a person's age on a specific date in history. We get historic with our example and calculate the exact age of Tom Hanks when the movie 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 last, most specific measure you can take is a person's age including months and days. The formula becomes slightly 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" .
Although it may seem like a mouthful, once you split it up it makes more sense. You must have a DATEDIF function for each measurement level, which means that the first function indicates years. The second function measures the years and months, but the & # 39; m & # 39; ensures that it only displays the month. The last 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. Only use your powers for good.
What else can I try?
What more can you do now that you have mastered one of the strange Excel functions? For starters, you can graph all the different ages you are calculating. You can also write macros that automatically calculate the ages for you. The world of Excel is practically endless when you have time to practice, and we're highlighting a new deal that can help you do that.
It's called the Complete Excel Bundle: Startup Toolbox and contains 12 modules in total. You can spend some time practicing almost everything 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 practical modules have a combined retail value of $ 2,800, but you can get it now for just $ 39 on Tech Deals to get. This is a relatively new learning package, but more than 200 people have already signed up. You can join them and learn more through the widget below.
$ 39 .00
The Complete Excel Bundle: Startup Toolbox
Save $ 2761 ] .00
Save $ 2761 .00 $ 39 .00