All language subtitles for 3. Using Date and Time Functions

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.