Dates can be a major issue with important data.
That can be because your regional settings are not the
same as the regional setting from whence the data came,
or simply because they have used a format that Excel just doesn't recognize.
That's the situation we have here.
While these dates are easily recognizable to us,
Excel does not recognize this date format.
That can be a problem the moment you try and do
any type of calculations or processing with these dates.
One of the things the client wants to be able to do,
is to run some analytics on which month people are most likely to join.
So they would like to get the months name from that date.
The way this would normally work,
and I'll just quickly demonstrate,
I'm going to click in here,
in J 1, type equals today,
open brackets, close brackets,
and press enter to get today's date.
So that is a valid date format for my regional settings,
and I would like to be able to extract the month from that date,
but not just the number 10,
I actually want the month name.
One really useful function that you can use when you're trying to extract
information and present it in a particular format is the RTEXTfunction.
What the TEXT function does is takes the value
and presents it as text in a format of your choosing.
We're going to type equals TE and tab,
and I'm going to click on the value that I want to extract the text from,
and I'm going to type a comma.
The second argument required is what format you want that text in.
Now I want to get the month name,
and you may recall when working with dates we use a kind of code.
Two D's is for the day.
Three D's is the abbreviated name of the day,
and four D's is the full length name of the day.
We can use M for the month.
In this case, I want the full length name of the month so I'm going to type four M's.
Close my quotes, close my brackets, and enter.
There's the name of the month.
That worked really nicely.
Let's try to apply that same function to our join date.
I'm going to click on column G and insert to get a new column.
I'm going to type in Join Month in the heading and click enter.
I'm then going to use my TEXT function again, equals TE,
tab, click on join date,
comma, and our four M's.
Close my brackets.
However, when I press enter this time I do not get what I was expecting.
This is because Excel does not recognize this as a valid date.
The next thing we're going to look at are some functions that can
help us take what is really text and convert it to a date format.
Let's come and have a look at this.
Basically what we have is a string of texts.
The function that will help us take text and convert it to
a date that Excel recognizes is the date function.
We're going to double click on a cell and just after the equals,
we're going to type DA for date and press tab.
The Date function requires three arguments.
A year, a month, and a day.
How are we going to get the year out of this little bit of text?
Well, you can see that it is the first four characters and that is consistent throughout.
So what we're going to do,
we're going to use our functions that we learned about in the Intermediate One course,
LEFT, RIGHT, and MID,
to extract the piece of text that we need.
Just before I do that,
I'm just going to copy this because we're going to need that again.
What I need to do first of all is get the year,
which is the first four characters sitting in sheet 1 F two.
To get the first four characters,
I use my LEFT function.
I'm going to type LE and tab.
I'm then going to type a comma and the number of characters I
need off the left is full and then close my brackets.
I'm then going to type another comma.
The month is sitting in the middle of the text.
To get this, I'm going to need my MID function.
I'm going to type mid, open my brackets.
Now I'm going to paste that text I copied earlier,
which is sheet 1 F2 and the MID requires two arguments,
a starting point and a number of characters.
Our starter point is actually the sixth character in,
and then we need two characters returned.
So there's our mid and type a comma.
The last thing I need is my day,
which is my to our right most characters.
For this, I'm going to use
the RIGHT function and I'm going to paste my Sheet1 F2 again.
I just need the two characters of the right.
Close by brackets from my RIGHT function,
close my brackets from my date function.
What that is going to do is chop off the text into day, month,
and year, and reformat it as a recognized Excel date.
Let's click enter.
Not only has that corrected the date,
but our month function is now working.
I'm going to copy it down.
That has corrected all my dates and I can copy down my join month,
and that has been repaired as well.
So just to summarize then.
A very useful function for taking text and converting it
to a proper recognized date is the DATE function.
We often use that in conjunction with LEFT, RIGHT, and MID.
Another little function we looked at which does the
opposite actually takes the date and extracts some texts,
which we want from it and that is the TEXT function.
In the next video,
we'll look at another potential issue,
which is how we get rid of unwanted spaces.
Dr Yvonne BreyerDirector, Learning and Teaching
