All language subtitles for 6. Tabulate Date Differences with the DATEDIF Function

The date dysfunction in Excel will help you work out the difference between two dates, and this is

a little bit of a weird function, and I'll explain to you why in a moment.

Before we get onto that, let's take a look at the data.

So in this table, I have some employee numbers.

I have the employee's first name, their last name and their date of birth.

And what I want to do is I want to work out how old they are.

So what is their age?

Based off of their date of birth, but also using today's date?

So at the top of the spreadsheet, I have today's date and I'm using the today function to get that

date.

Now, in this type of scenario, it's going to be more beneficial for me to use the function today as

opposed to hard coding today's day in because I want this age to update as the current day changes.

And it's this piece of information today's date and the date of birth that we need in order to construct

this formula.

Now, as I mentioned, this is a bit of a strange formula and you'll notice why straight away.

So if we time equals and then we want to type in date diff notice that nothing comes up underneath.

Normally, when I type in a function, Excel recognizes it in the functions library and it shows me

underneath.

For example, if I was typing in the look up, you can see underneath it, says V.

Look up.

Whereas when we do date def, it doesn't.

It also means I can't press the tab key to put my first parentheses in.

I need to add in the bracket.

Now, as soon as I add that in, it does come up with date diff underneath, but it doesn't show me

any arguments.

So basically, this function doesn't exist in Excel's information library, although the function does

exist, which is very, very strange.

So effectively when we're constructing this, it's one of those cases where you either know or you don't

because you're not going to get any help with regards to the arguments you need to provide this function.

Now, fortunately, this is a very simple function.

Our first argument is going to be the date of birth, which is d six comma.

A second argument is going to be today's date.

So B three, and now we need to tell the formula what format we want to display this result in.

Now my result.

I want the people's ages so effectively.

I want this in year format.

So in quote marks, if I type in one y and closed the quotes.

That is my formula.

Let's hit enter and see what we get.

Looks pretty good.

Now can I double click to copy this down?

No, I cannot.

Why can't I do that?

Let's undo.

What if I double click to take a look at this formula?

You can see here that with Cell B three, which is where we have the today function, I haven't made

this absolute.

So when I copy this formula down, the cell reference for B three is also going to start moving down

into these first names.

So that is why we're getting an error.

So all I really need to do here is make B three absolute by pressing the F4 key.

Let's enter and try that again.

Double click to copy down.

And there we go.

I now have everybody's ages in here.

So just remember with that function, you don't get any help with regards to arguments.

And the thing that I find quite difficult here, it's not too bad on this example, but sometimes when

I'm trying to use day diff, I tend to get the cell references round the wrong way.

I can never really remember if the date of birth is supposed to go first or if today's date is supposed

to go first.

But the only thing I can suggest to you there is just try out if it doesn't work.

Switch them around the other way.

Now another thing if I double click to edit this, if I wanted to show their age in months, I don't

know why I'd want to do that.

But if I did, I would just substitute the Y for an M and hence I.

It's going to show me their age in months.

So Mark Berry is effectively two hundred and six months old.

If I wanted to show their age in days, I would substitute that with a D..

And there we go.

Mac is six thousand five hundred and eighty days old, so don't forget you can change the format from

here as well.

Now, for this type of calculation, there is another way you can do this or just finish off by showing

you this as well, because you might find it a bit easier.

Let's control, shift down, arrow and delete everything out.

What I could do is sometimes you don't even need to use a formula if you want to work out the difference

between two dates.

So what I could do is today's date minus the date of birth.

Now notice that's going to give me my result in days.

So what else would I need to do to this formula to get this to display as years?

Well, I could divide it by 365 now if I just do divided by 365 because of the way Excel works out calculations,

that's not going to be quite correct.

So what I need to do here is I need to enclose the first part of this formula in brackets.

So Excel is going to do the B three minus D six, first of all, and then it's going to divide the result

by 365.

What I also need to do is lock the cell reference for today's date.

Let's enter.

We can double click to copy those down.

And then all I need to do is just have a little play around with the formatting.

So I'm going to switch these to a number and then take those decimal places down, which is going to

give me the same result.

So two different ways there that you can work out the difference between two dates.