Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.