Hey, it's Andrew from Better Sheetz. Thanks for watching the video. I got a question in the I Love Google Sheets Facebook group. If you are not a part of the Facebook group yet, it is a public Facebook group, go to facebook.com/groups/i love Google Sheets because you love Google Sheets. Chris in the group asked a question about Filtering dates.

So I thought I could answer a very long text,  answer or a very short video. So this video is a better sheets members video now. And I'm also really seeing this on YouTube because it's public so that it is public and you all can see the answer. A very simple I'll show you how to filter dates.

 And also invite you if you have not. Purchased better sheets. Go ahead. Right at this moment, it is available on AppSumo full access, which is normally $99 is $19 only. I will never charge you again is my claim, and I've held myself to that. The only thing I've charged people for, I think is shirts, which say something like il, like Google Sheets, which is a very funny pun.

And that you can buy, I think at Maker Book thread.  And includes free international shipping. Okay. Enough of the promos. You should have joined facebook.com/uh, groups. GRA slash I love Google Sheets, and here's how you filter dates. Okay? So in this example, I'm gonna move my face over here. I have a set of transactions and I'm.

Building. Let's say I'm building a dashboard or I want to filter it and maybe these transactions are, you know, Carl and Bubba need to pay me. Bubba's paying me once a month. Carl's paying me once a week.  and I really wanna figure out like, Hey, is Carl paying me enough? Like these numbers don't add up very much.

Like Bubba's paying me $744, Carl's paying me $53. They obviously, they, these don't add up to the same, but we don't want to know say we, we do want to filter for like all of January.  dates or all of February or just one week. Hey, who paid me this week? So how we do this, let's, let's, I'm gonna label this start and I'm gonna label another one end, and we're gonna put dates here.

Let's say 1, 1, 20, 20 and two, one. Actually no, 1 30, 1 20 20. Okay. So I have these two dates and I want to say on a filter, I wanna get a list of these just setting up here, and I wanna filter by this and I want to get the name, the date, amount, and the date. So okay. Filter. And what is the range? The range is going to be all of A, B, and Z.

Okay. And then the, what we're gonna filter by is we're going to select. Cell. So c3, and we're going to say that the, this date must be less than or equal to. Nope, that's wrong. . I did that literally opposite. I want  the date column. This is, I did that literally opposite. So sorry. Whoops. So this should be C to C is the date column

That's it, is greater than the start date, but it could also be equal to, so we'll put equal two and there's, I'll show you one other way to do this, but I like using greater than or equal to, and then we want to make sure that the date is also.  Let's do this is also less than or equal to the end date.

Okay? And we have all of these that are engineering. See, we already got one off the list, which is the February one, but let's say we want to even tighten it up a little bit more, but you can just go here, double click. Let's say the second.  until the ninth who paid me then. Okay. It was only Carl. And so now I like to do this, I like to put these as blue cause these are inputs.

And how does this work? Well, dates are actually a single number. It's an integer. Let me show you what that means. I'm gonna paste values. Okay. So what I did is I copied this and I pasted the values This.  January 9th, 2020 is the number. 43,839. Now if I let's say change this to the eighth, I think you'll see here I'm gonna copy it as well and I'm gonna paste value shift command V and see it's one number, it's 43 8 30.

so we can use Google Sheets, knows these numbers, right? The, I just pasted the values. This is a representation really. Dates in Google Sheets are a visual representation of this number. We can write them and, and we, Google Sheets helps us a lot with this date picker. It really helps us represent it. But in real truth, what this 1 8 20 20 is, is this number 43,800 and.

We can also find this not just by pacing values, but we can find this date value by literally calling it date value and putting this D three in there. And see, now we have 4 38 38. So. , this helps us a lot when we are trying to figure out the difference between dates. We can say how many days are between these two.

We can use them in sort of any functions. This minus this and that six days are in between that. Right. If we do a week, like second to the ninth, the seven days this is really, really helpful. For things like, if you are doing this kind of filtering and you're having a human pick a date and pick a date, and now you can see, you know, duration, date, days, you can, you can have this filter, have this difference here.

And what's really cool, let's go back to this filter. Now, this transactions, all they see is filtered by, are they greater than this date? And are they less than this date? One thing I will mention very simple is if there are no, if there's nothing, here's gonna be a problem. Let's say one, three till one, four, you're gonna get an.

Now an error isn't necessarily wrong. In this case it is giving you the correct answer. It says that no matches are found, but an error looks bad and it looks, and it looks like a bad thing if someone else is using this sheet. So what I like to do around these filters is I don't like to show an error. I like to give a message if I know that there is an error.

So I go if error, and at the very end I put a message, I go, Hey try.  Try searching again or some message there. And so now that message shows up, not the error. And I know, okay, maybe these dates are wrong. I can infer, okay, I have to pick out different dates, and now I have an answer right on this date here.

Notice this date now suddenly is formatted. You can go up to this format and format this number anyway you want Here. Formatted date. There you go. , as you can see, you can even actually, funny enough, change these dates, any dates to numbers as you saw there, they were changed to those numbers. And you can use dates as numbers to do these filters greater than or equal to or.

One other way to do this, I will show you actually one other way to do this which is what I was doing for a long time before I figured out that you could do greater than or equal two. Basically if you want to include. You can again do some math. You can say it's greater than C3 minus one, or here you can do less than D three plus one.

And so that encapsulates the exact same. It is the exact same filter. It gets you the exact same results. It just treats these dates as numbers and then minus one and plus one. So this is really cool if you know. Oh, there needs to be some grace period. Like, I don't know, for some reason you select the date, but you only want dates three days ahead of that or below that.

Oh, that's really cool. So you can say like, Hey, like I want offset, you know, offset here. Why this is cool is cuz now you can say, okay, offset by three. And so I want to maybe add this one h. And we're gonna do the same thing here. Add H three. There you go. So now, no matter what you set here, start an end, you can offset it by this number.

Why you would do that? I don't know. , I have some ideas like maybe there needs to be some grace period. There needs to be some warmup period if it's bookings or something. I don't know. Thank you for watching. Hopefully you learned something here about how to filter dates and hopefully you can now join us in the group on Facebook, open to everyone and anyone.

It is a public group. Make sure you answer all of the questions to get into the group quicker. I love Google Sheets and I hope you do too. Bye.