Finding the precise difference between two dates in Excel might not be a well-liked use, but it are often tons of fun. A classic example is functioning out someone’s age. It’s not as hard because it sounds, and there’s a handy function in Excel which will offer you a particular age to the day. Read on for recommendations on the way to calculate age in Excel.
See also: the way to split cells in Excel
People might not always appreciate that you simply have a spreadsheet of their exact ages, but it can definitely have its benefits. Here’s the way to start .
How to calculate age in Excel
1. this is often a reasonably simple three-column task. to urge started, enter the date of a person’s birth into your first cell. In our example, we’re using 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 stay both dates together if you’re tracking multiple ages.
3. within the third cell, for us it’s C2, enter the subsequent formula: =DATEDIF(A2, B2, “y”). the 2 cell identifiers are fairly straightforward, and using the letter y because the third indicator means you’re only curious about the amount of years.
You can also get a person’s age without entering today’s date within the second cell. to try to to this, change your formula to =DATEDIF(A2,TODAY(),”y”). Excel will do the remainder as far as identifying the date and calculating the age.
If you actually want to urge specific, you'll also calculate a person’s age on a selected date in history. We’re getting to get historical with our example and calculate the precise age of Hanks when the film Castaway was released: December 7, 2000.
The formula should appear as if this: =DATEDIF(A2, DATE(2000,12,7), “y”). Note that the date format is year, month, day.
The final, most specific measurement that you simply can make may be a person’s age, including months and days. The formula gets a touch bit longer than previous measurements, but the method remains an equivalent . Your formula should appear as if this: =DATEDIF(A2,B2, “y”) & “y” & DATEDIF(A2, B2, “ym”) & “m” & DATEDIF(A2,B2, “md”) & “d.”
While it's sort of a mouthful, once you break it down, it makes more sense. you would like to possess a DATEDIF function for every level of measurement, which suggests that the primary function indicates years. The second function measures the years and months, but the “m” ensures that it only displays the month. the ultimate function measures the differences in months and days without the years and displays the date with “d.”
Now you recognize the way to calculate age in Excel. Please only use your powers permanently .
See also: the way to protect cells in Excel
What else am i able to try?
Now that you’ve mastered one among the more niche Excel functions, what else are you able to do? For starters, you'll make a graph of all of the various ages that you’re calculating. you'll also write macros that calculate ages for you automatically. the planet of Excel is practically endless if you've got the time to practice, and we’re highlighting a replacement deal which will assist you do exactly that.
It’s called the entire Excel Bundle: Startup Toolbox, and it includes 12 total modules. you'll spend a while practicing almost anything you would like to find out , from large spreadsheets to logic functions and conditional formulas. the selection is yours, and you've got the liberty to finish each module at your own pace.
The 12 hands-on modules have a combined retail value of $2,800, but you'll snap it up for just $39 now on Tech Deals. this is often a comparatively new learning kit, but quite 400 people have already signed up. you'll join them and learn more via the widget below.