Ian Asks: How to sort dates by year/month (can't do with filter)

About this Tutorial

In Excel you actually have these options of sorting by year, by month, by date. But let's do it in Google Sheets.

Video Transcript

<div> Hey, this video is for Ian. He asks, um, how to sort dates by your month when he has them, um, formatted like this, which, um, it's like the n date or the day of the month, uh, hyphen a, um, month, then hyphen then here, um, what he's having str trouble with his that in Excel . Basically, you can take, um, this data here, do a filter . and in Excel you actually have these options of sorting by year, by month, by date.

Um, there's a, um, other types of things you can sort by , but in Google Docs, as you can see, you need to select each and every different, um, date and year and month, and you can't sort it by month. So this is a little bit of a workaround. May not work for everyone. But at least you'll learn about, um, month, year in day formulas and how they work.

And then, um, show you one pitfall, uh, two potential other ways you or not other ways, uh, something you can do on top of this, which with, uh, array formula , but you might, uh, run into some pitfalls or challenges with that. And you can even reformat the dates here. I'll share that with you as well. First off, um, we want to be able to sort by month, for instance. Um, you can convert the month. And actually this, uh, let me turn off the, uh, filter . So this. Date, Google Sheets does look at it as a date.

It understands that this is, um, the date, uh, the month and the year. Um, it, you can use the month. So we literally just go equals month. And in parentheses, we, b2, put B2 and we just copy and paste that down the ro uh, column. And you can get the number of the month. And, and this is sortable. So what you can do is essentially if you wanna sort this data , say in column A, you have these dates, you can create these other, uh, columns and absolutely this is not the best solution. If you are trying to keep your, uh, data lean. You're trying to show this off to other people.

But also, honestly, this is a really quick way to sort this, um, in this way. Um, If you just need to sort it real quick, um, just type in month and put in the, uh, in the parenthesis the cell that you're trying to convert to month and it gives you this number . And here you can sort the whole sheet by. , you can sort sheet by Z to A and everything sorts perfectly. Um, you can do that with year as well. You literally, it is equals year. And then in parentheses you put b2, whatever cell you're trying to, um, do.

And you can also grab the day too with just day, uh, with b2. And now you have this, um, day, year, month, and you can sort these, you know, move these around as however you want. Um, reformatting the date doesn't solve the issue. Uh, it merely just makes it reformatted. I just reformatted it. So literally all I did was put equals B two and then copied and paste it here. And what I did is I went to format .  went to number , um, more formats , more Dayton time formats. And here's a bunch of ways you can format it. Um, but it doesn't change the issue.

You still have, um, Google Sheets still gives you the exact same problems, um, can change it to, let's do this, let's change it to that. Let's change day 2, 0 5 month to, I like adding the leading zeros . Makes it easy to read sometimes.  can copy and paste that down and we can see the same problem exists if we turn on the filter and we have the dates exactly the same thing, right? So this solution of the of. I don't know what to call it. Parsing out the day, month, year is one solution to your exact problem.

What might happen though is like if you have thousands o of these dates that you need to sort real quick, um, or at least like look through, you might think, oh, you might think this is a solution, and I'll share and I'll share with you what the problem is. You might say, okay, let me just do array formula .  here. And instead of b2, let's do b2 colon B for the entire, um, thing. We'll delete this and now you'll have your dates and you can add as many dates here.

This column will always be the day, and it's only one, um, formula here, so you don't need to type or, or copy paste this anywhere. Here's the issue though. Now you can't sort it. If you sort . Um, let's see, let's, let's do this. If I sort z to a , what it's actually going to do is sort it and it's gonna move this one with the formula down to here. And now it's like in reverse  and everything disappears. Um, so it knows the data immediately, but then this array formula. Is now here and it's like, oh, it, it doesn't know what to do until you lost all of your data here.

Um, we'll just come command, see that. So while, uh, array formula does work to get you that information, it doesn't work well with sorts. So if you're literally trying to sort , you cannot use a Ray formula in this case. Um, unless. , you don't mind this breaking. So what's interesting about that is that the only thing that breaks is this sea column. But if literally the only thing you want to do is sort it real quick.  and you don't want to copy and paste these, uh, formulas all the way down.

Then actually this does work for that specific situation. Um, and that's really exciting. You only have to remember to do array formula around the day and put the form the column here. You get it, sort it done, delete that column, and you're done. And you've crunched your data . You've, you've done some data Ming, you've sorted your sheet and you're done. It just doesn't work. If you wanna sort it a couple ways, couple different ways, and you have to redo the array formula , it's very complicated. So hopefully this helps you out.

Hopefully this helps Ian out with his issue of, of trying to sort this. Um, I know it's not exactly the best situation of like, you can't really just go here, click and filter . Um, . And unfortunately, you know that that's a, it's not a bug, it's a feature. Um, you get to keep your data as you wish, and it's not filtered and, and Excel excels at that, um, one feature, but at least you have some way of getting around it right now. Um, you can create this day, month, year, columns and sort as you wish, and then you have these sorted done. Have a great one.</div>