(upbeat music)
Okay, so if you're using Google Sheets
and you have a table of data.
We have one here,
and we're going to try to sort it by date.
It doesn't always work very well,
and it's usually because your dates are broken.
And I'm going to show you a couple of different ways
to fix them.
We'll start with this short table here
just so you can see it easily,
and then I built a longer one, too.
And I'll show you a different way to check them
in case that works better for your layout.
So, the first thing,
let's act like we're going to sort it,
so we'll select B3 through C14,
and we'll do Data Sort Range.
You can do these preset sorts here,
but they don't always work like I want them to.
So, just control it exactly by doing Sort Range.
It's going to ask you a few things.
So, this data does not have a header row.
If you look at where I selected,
I did not go all the way up,
so leave that unchecked.
And we'll sort by Column B.
Just click Sort,
and if you see what happened here.
The top few sorted correctly,
so July, and then you have August.
Then you go to September,
but then you come down to the bottom.
And these three didn't sort correctly.
So, what's happening here is
these three are not valid dates.
So, Google Sheets can't sort them in date order
because it doesn't think that they're dates.
So, let's undo that sort.
I'm just doing Control + Z,
and we'll come in here to Column B.
And we're going to apply Data Validation first
before we do the sort.
So, select the data.
Go to Data,
and come to Data Validation.
You'll get this popup window here.
It's confirming the range of data
that you want to validate.
We had already selected that,
so that's okay.
In the criteria, it's saying,
"How do you want me to validate this?"
So, let's click on this dropdown,
and we say, "We want you to check to see if it's a date."
And the criteria for the date is just to see
if it's valid,
so just stay up here at the top.
We just want to see if it's a valid date.
And you have two options for what to do
if it's not a valid date.
Since these dates are already in here,
we'll keep it at Show Warning.
We just want it to tell us that.
If you were entering dates,
you could use Reject Input,
where if you entered a bad date,
Google Sheets wouldn't let you do it,
and it would say, "You have to enter one that's valid."
So, just leave it on Show Warning for now.
Click Save,
and Google Sheets is pointing out
these three dates that aren't valid.
So, if I hover over one of them
it's telling you the input must be a valid date.
So, we'll look at these.
You know, this one accidentally has a space in it,
so we'll take that out.
This is a day that doesn't exist.
August only has 31 days, right?
So, are you seeing this calendar pop up?
That happens when you double-click in these fields.
We'll fix that,
and September is misspelled.
If I just click on September 12th,
that's going to fix that, as well.
So, you see there's different formats here
that are valid dates.
They don't all have to be slashes or dashes.
Mine happen to be with that month, day, year
because I'm in the U.S.
Yours are going to be set for wherever you are,
but whatever format they're in,
they just need to be valid
so Sheets recognizes them.
So, now that I've fixed these,
if I come back in here and I do Data Sort,
Data Sort, and just click Sort,
these all sorted correctly,
with July, August, and September.
Now, just because it's bothering me
I'm going to highlight this data
and fix the formatting.
Let's just make it all kind of right now nice,
and there we go.
So, this little table is all set.
It's sorted correctly,
and then I made a longer table here.
And let's just say for some reason
that you don't want to apply Data Validation.
Maybe you're going to be copying and pasting it
to another sheet,
and you're not going to use formatting.
Whatever it is, so you can just add a column
to the right,
and you can use the function called ISDATE.
And what this does, this function
just waits for a value that you pass it.
In this case, it's going to be G3,
and it validates whether or not it's a date.
I haven't finished the function yet,
but it's already telling me TRUE.
Let's close this off with an ending parentheses,
and hit Enter.
It's saying that this one's valid.
Going to copy this formula all the way down
to the end of this table,
so if this is a thousand lines,
it'll do it as quickly as if it's two.
We'll double-click on the square
in the lower right-hand corner of the blue rectangle,
and it copied the formula all the way down.
So, you see that there are some FALSE's here,
so you can just, let's add a header here.
And then let's get a little tricky,
and just sort this data
and bring the broken ones to the top.
So, I've selected the entire table,
Data, Sort This Range.
We wanted Column I.
We'll sort it.
We'll go back up to the top.
These are your three broken dates,
and I broke them in a similar way.
So, this one has a space.
Fix that.
And this one doesn't exist in this dimension,
and this one, I guess this one doesn't either.
All right, those are fixed.
You can just leave this column here.
If this is a live table and you're continuing to add to it,
maybe you want it.
And let's re-sort this.
And there you go.
You have July, August, and September.
And I thought you would find it interesting
that this video came about because I had a user
point out on a previous video I had about sorting dates,
that I never actually sorted the dates.
So, I went back and looked at it,
made the video,
used a better technique, I think,
and I actually sorted.
So, thanks for pointing that out.
If you enjoyed that video and want to see others just like it,
just little, quick videos mostly about Google Sheets,
you can click on the icon in the lower right-hand corner
to subscribe, thanks.