- [Instructor] In our chapter seven exercise file,
the second worksheet has a long name
and it's merely a list of some of the different functions
that we might be using as we work with dates and times.
I haven't listed all of them.
The name of the sheet is date, ear, month, day, minute.
Now, we get dates from different locations
and out of different files in a variety of ways.
I'm not necessarily saying this is typical
but there is a date function that sometimes you will want
to use to create a date out of information like
what we're seeing in columns A, B and C.
Again, although not necessarily typical,
I've gotten data in this form
and it's not that workable
as we see the information here in columns
A, B, and C.
We will be able to do a lot more with it
if we combine that information into a single cell
using the function called date.
Let's first pick up the information for the year.
In this case is cell A2 comma,
the month is in B2 comma,
and the days in C2.
Enter, we've got a data entry.
That's being stored as a value and then the earlier movie,
you may have learned what that really means.
We can do this for the rest of the information
in the column here,
you see what's happening.
If for example, there were an impossible month,
we'll change this to a 13 momentarily.
Excel actually turned that into the January
of the following year,
the 13th month of 2018, that could have been a surprise.
I'll undo that.
How about an impossible day?
I'll make this be the 31st.
There's no 31st for September.
And that turns it into October 1st.
What happens if that's a 32nd?
See what's happening there?
So that could be a surprise too.
That could have been a typo,
not sure what someone meant.
Let me press control Z a few times undo that.
Back to that.
So be alert to that scenario too.
Now over in column F we might have
what might be called the opposite situation.
There could be times when you've got data entries
and you need to extract from the date, the year.
And as soon as you see one of these,
you know what the other ones are going to be.
The year of this particular date here is,
as expected 2017.
And an age too, month.
The month out of there, the seventh month,
There it is,
and the day.
Now although less likely to be used,
there are situations where you'll be using time functions.
And here too it's probably unlikely you would get data
in this form, but if you did,
you could reconstruct this into a workable time
by using a function called time.
We'll pick up the hour from cell K2 comma,
the minute from cell L2 comma,
and the seconds, if present from cell M2.
Enter and we've got a time.
Keep in mind when you do work with times,
sometimes you're actually talking about specific times
of day.
Maybe that's the case.
We often would want to see the am, pm displayed.
If it refers to a time length,
we might want this display without the am, pm.
So it's worth noting whenever you have a time,
think out that issue of whether there should be an am, pm
or even a 24 hours style.
So, pressing control one,
a quick way to get to format cells,
on the number tab, time category,
the am, pm display if you only want minutes and seconds
could be the third choice in 1:30 pm,
we double click it, there we are.
It looks like that.
If you wanted this to be a length of time,
press control one.
Use the entry of 13 colon 30.
Again, that can be used in a couple of different ways.
These other times over here,
if they represent times of day,
I'll simply drag that lower right hand corner downward a bit
displays the information this way.
That's 9 pm.
If we're talking about a time of day,
if it's time elapsed, it's 21 hours and 55 minutes
for that kind of a scenario.
So we can use these in different ways but it's important
to note how we sometimes need to construct these
with a function,
and then consider how they're being displayed.
And similar to what we saw with dates,
we can when given a time extract the hour
or a minute and much less likely, a second.
But here too,
you can almost guess what these are going to be.
Pick the hour out of here.
We get that entry,
and minute.
And of course the other entry too.
In the same way, second,
less likely to be needed, but same idea.
So different techniques here for working with the elements
of dates and times using the various functions,
date time, year, month, day, hour, minute, second.