Discovering the precise distinction between two dates in Excel might not be probably the most used characteristic, however it may be quite a lot of enjoyable. A traditional instance is figuring out somebody’s age. It’s not as exhausting because it sounds, and there’s a helpful operate in Excel that may give you an actual age to the day. Learn on for tips about the right way to calculate age in Excel.
Learn extra: How you can add cells in Excel
QUICK ANSWER
To calculate somebody’s age in Excel, enter the method =DATEDIF(A2,TODAY(),” y”), the place cell A2 incorporates the date they had been born and “In the present day” is at present’s date. Press Enter and Excel will output the age.
How you can calculate age in Excel
It is a fairly easy three-column job. To get began, enter the date of an individual’s start into your first cell. In our instance, we’re utilizing Tom Hanks, whose date of start is in cell A2.
Now, enter at present’s date into the cell subsequent to your first cell. It’s simply simpler to maintain each dates collectively for those who’re monitoring a number of ages.
Adam Birney / Android Authority
Within the third cell (for us, it’s C2), enter the next method: =DATEDIF(A2, B2, “y”). The 2 cell identifiers are comparatively simple, and utilizing the letter y because the third indicator implies that you’re solely within the variety of years.
Adam Birney / Android Authority
You can too get an individual’s age with out getting into at present’s date within the second cell. To do that, change your method to =DATEDIF(A2,TODAY(),” y”). Excel will do the remainder by figuring out the date and calculating the age.
For those who actually need to get particular, you too can calculate an individual’s age on a selected historic date. We’re going to get historic with our instance and calculate the precise age of Tom Hanks when the movie Castaway was launched: December 7, 2000.
The method ought to appear like this: =DATEDIF(A2, DATE(2000,12,7), “y”). Be aware that the date format is yr, month, and day.
Adam Birney / Android Authority
The ultimate, most particular measurement you may make is an individual’s age, together with months and days. The method takes a bit of bit longer than earlier measurements, however the course of stays the identical. Your method ought to appear like this: =DATEDIF(A2,B2, “y”) & “y” & DATEDIF(A2, B2, “ym”) & “m” & DATEDIF(A2,B2, “md”) & “d.”
Adam Birney / Android Authority
Whereas it seems like a mouthful, it makes extra sense when you break it down. It’s good to have a DATEDIF operate for every stage of measurement, which implies that the primary operate signifies years. The second operate measures the years and months, however the “m” ensures that it solely shows the month. The ultimate operate calculates the variations in months and days with out the years and reveals the date with “d.”
If you wish to calculate what somebody’s age was on a selected date, both previously or sooner or later, then modify the method to be =DATEDIF(XX, “MM/DD/YYYY,” “Y”)
If you wish to calculate what somebody’s age was on a selected date, both previously or sooner or later, then modify the method to be =DATEDIF(XX, “MM/DD/YYYY,” “Y”)
