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
In this lesson, we're going to blast through some of the most useful date and time functions in Excel.
And we're pretty much going to cover most of the date and time functions that you're going to come across.
So on this worksheet, we have a column column A. that contains a date and this date is currently in
short date format and we then have an invoice total.
And what I want to do is I want to extract certain parts of this date into separate columns, and we're
going to do this using date functions.
Now the majority of these that I'm going to show you rely on the fact that we have the full date in
Column A because we're going to use this column as our reference point to extract the various different
parts of this date.
So effectively, we're breaking up this date into its different parts, and I'm going to show you a
function in a moment the kind of does the opposite.
So if you have things like the day number, the month number and the year, how you can combine them
all together into a short date format.
But let's not get too far ahead of ourselves.
Let's run through all of these different functions.
So in Column C, we want to extract the day number from the date and column A..
So we have a function called day.
Now notice there is only one argument for this and that is serial number.
And this is slightly misleading because what excel means by a serial number is just the cell that contains
the date.
So in this case, A4 close the bracket, enter, it's extracting the day part of this date.
So in this case, 10, I can then double click, which is going to copy that formula down and extract
the days for the rest of the date.
Super easy.
Now what if I want to extract the day name from this date?
And what I mean by the day name is Monday, Tuesday, Wednesday, Thursday, so on and so forth.
Now this works in a slightly different way, because if the answer that you want to extract from a date
is a text based answer such as the day name or maybe the month name, you need to use the text formula.
This function has two arguments value and then format text.
Now the value again, is just the cell that we want to extract from.
And then we need to provide the format for this day.
So this is where those moms and dads come in again.
So if we're extracting the day, we want to use the D, but how many days we use will determine if I
get money mo and all Monday, and this needs to go in quote marks because it is essentially text.
And if I type in three days, first of all, close my quotes and hit enter, I'm going to get the short
version of the day name.
It's double click to edit.
If I was to add another day in here and her answer, I get the long day name and then I can double click
to copy that down.
So just remember that when you're trying to extract things from dates, if it's text, you need to use
the text function.
Let's move across to the next column because this time we want to extract the month number.
And for this, we use the month function.
Again, one argument, which is the serial number eight.
Close the bracket.
Enter and we get the month number parts of this date.
Now, once again, the next thing that we are extracting is the month name.
So this is going to be January, February, March, and this is text.
So we need to use the text function again.
Let's select our value, which is A4, and then we can choose our format, which needs to go in quote
marks.
So this time within with the month.
So we want MS in here.
If I do three MS, I'm going to get the short version of the month.
So Jan fed Ma, if I put forums, I'm going to get the long version of the month.
Let's say enter.
And then, of course, I can double click to copy that down.
Really straightforward.
The next one is super easy.
We're extracting the year and we have a year function.
You guessed it serial number.
Close the bracket, hit enter and it's going to extract the year parts of that date.
And then finally, I can extract the week day number and we can do that using the week day function.
Now, the first argument here is the serial number, so we need the date again.
And now we can specify how we number our weekdays.
Now I tend to number mine.
As in Monday is one all the way through to Sunday being seven.
But some people in different.
That's the world number there weekdays differently, some people might start their week on a Sunday,
and so that is number one to them.
Some people might start on a Thursday, so on and so forth.
And this is the argument where you can specify exactly that.
So if I choose to put a two in here is this argument.
It's going to number Monday as one through to Sunday seven.
And that is exactly what I want to close the bracket into.
And now I get the weekday number double click.
So if I just take a quick look at this, this first one is full.
And yes, Thursday, according to me, is day number four.
And if I wanted to get super fancy and this will even give us a chance to practice a quick if statement,
I could type in an if statement here, which tells me if this date effectively in Column A is a weekend
or a weekday.
So what I could say here is equally if my logical test.
Well, what I can say is if the weekday is greater than five, if that is true, then yes, it's the
weekend.
If it's not, then.
No, it isn't, because Saturday and Sunday are effectively numbered six and seven.
So if I hear Enza and then copy this down, I should find that most of these are no except when we get
to anything that is a six or seven, which will be yes, because these represent Saturday and Sunday,
so some really useful functions in there.
Now I mentioned earlier that we can in fact do the reverse of that and combine values together to create
a date.
So if we take a look at this table at the top here, you can see I have the year in one column, the
month in another column and the day in another column.
So what I can do here is use the date function and all I need to do is specify the cells.
So the year is OK for the month is full and the day is M4 closed.
The bracket enter and I get that date, which I can double click and copy down.
I could do exactly the same for time, so equals time.
We need to specify the hour, the minutes and the seconds close bracket enter.
And there I get my time's super useful functions.
Now let's jump across to the Time Functions worksheet because I just want to finish off by showing you
how we can do pretty much exactly the same, but this time using time as a serial number.
So I have some times in this first column and we can break those down using more functions in Excel.
So the first one is the our function.
Again, it just requires a serial number and it's going to pull out of that the our double click to
copy down.
We have a minutes function again.
Serial number is required.
And we can double click to copy that down.
And then finally, we can add the seconds by using the second function serial number.
Close the bracket, hit enter and then we can double click to copy that down.
Now, because of the way that I have these times, these last two are just showing zeros.
And that is actually correct.
It's really only this first column that has different values in it.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.