If you are watching this video, you are looking to calculate the age of someone or something
using a spreadsheet.
Most likely, you've looked around the internet and found a couple of functions that will
work, but they're just kind of complicated.
They aren't very easy to understand.
Google Sheets has a formula that will do this for you with this in mind.
So, it's relatively simple because when you are calculating age, you really only need
a few inputs and a couple of variables.
The formula that does it is called DATEDIF or date difference.
If you look at one of the cells down here you can see I have it input in the cell.
The DATEDIF function has three different inputs.
The first one is the start date so I reference the date of birth in this case.
The second one is the end date so I reference this column.
The third one is the unit and we're going to talk more about that later.
This shared spreadsheet is available if you go to my website which I am going to show
you a link for it and search for calculate age.
You can click on the page and it will bring you to the Google Sheet.
Feel free to copy the spreadsheet and use if however you want.
I have laid it out in a way that it is mean to show you what the different units do and
how you can string them together if you want.
This is the beginning data and I recycle this data throughout.
In the first row, I am going to show you how to end up with age by years, months and days.
This person is 17 years old AND 7 months AND 5 days.
The functions know that you have already calculated the years.
This one know that you only want days in that last month.
How you do this is - years is the DATEDIF formula with a Y as the unit.
This next one is YM and this next one is MD.
What YM and MD stand for, I'm not really sure but it doesn't really matter because know
you know what they do.
If you want it readable, in English, you can string them together using CONCATENATE and
I just did that by looking up the value in cell D14 & - which means joining it with a
string that says " year, " and then repeating the same process of joining the months and
then I'm joining the days.
That presents it this way.
Now, let's go down to an explanation of what the M unit is and the MD unit.
The M is the number of whole months this person is and the MD is the days after the month.
You see it is the same function as we used right here but it makes sense to trail after
this if you want months and days.
If you want years and days, then that is when you would use the YD function.
This person is 17 years old and then 217 days old in that last year.
Then, the last function is just a D that says calculate days.
This leads to a good illustration of how Google Sheets stores dates.
I have already unformatted these.
The date 8/28/16 is really just 42,610.
It's an integer. 1/23/99 is really just this.
If you use the DATEDIF function that tells you the number of days, really since they
are just integers, you can just subtract the two.
If you subtract it, that will get you days.
You really can't subtract the two and then divide by 365 though because it may be a leap
year and you certainly can't do any of these more advanced functions.
So the DATEDIF function in Google Sheets is really helpful and powerful.
If all you are trying to do is sort a large table of data by this age, then you only need
to do probably this last, simple formula.
But if you want it readable in years, months, days the DATEDIF function comes in handy.
So, I hope you found that helpful.
That's about all there is to go over with the DATEDIF function.