🔵 In Excel, see how to AutoFilter for a Date Range in 2 different ways
✅ Get the sample Excel file to follow along
► www.contextures.com/xlautofil...
⏰ Video Timeline ⏰
0:00 Intro
0:17 Date Filter Type 1
2:01 Date Filter Type 2
4:03 Add Conditional Formatting
6:21 Get the Workbook
🔶 Instructor: Debra Dalgleish, Contextures Inc.
💡 Get Debra's monthly Excel tips: www.contextures.com/signup01
Subscribe to Contextures RU-vid: ru-vid.com...
#ContexturesExcelTips
'-----
VIDEO TRANSCRIPT
I'm going to check whether each date is on or after this date, and on or before this date.
We could do that on the worksheet with a formula, and we'll do it here first, and then use the same formula in conditional formatting.
In this cell I'm going to type an equal sign. I want to check two things.
So I'm going to use the AND function and an open bracket.
The first thing I want to check is if this cell B4 is greater than or equal to this cell. B4 greater than, equal to, and the start date.
Now it's gone in as a relative reference, but no matter which row I'm checking, I always want it to check this cell.
I'm going to press the F4 key, to put dollar signs in there, and make that an absolute reference.
Then I'll type a comma and the next thing I want to check is if B4 is less than or equal to the end date.
B4 less than equal to.
Click on the end date and again press F4, to lock that and make it an absolute reference to that cell.
Close the bracket and press Enter.
Now this one is not in that date range, but if I drag down, and we've got a few more dates here, going down to the beginning of April, we can see that these are all TRUE and they're from January 17th to February 14th.
If I look at that count down here, we've got 8.
We're going to use this same formula in the conditional formatting rule that we'll set up.
So I've selected the first cell, I want to be in the first row.
I'll highlight that in the formula bar, Ctrl+C to copy it.
Then press Enter, to get out of the edit mode in that cell.
I'm going to click the heading of the Date column, to select all the date cells.
Then up on the Home tab, Conditional Formatting. New Rule.
In here, I want to use a formula.
We're going to use that formula that we just copied and I'll click in the format values, formula cell, and Ctrl+V to paste that formula.
I'm going to click Format, and on the Font tab, I'm just going to make a subtle change to the color.
That way, if we wanted to print this sheet, nobody would notice that there is a difference in some of the cells.
So instead of Automatic, which right now is black, I'm going down to this last one, which is Black Text, 1 Lighter 5%.
I'll click OK and we can see the formatting example here.
Click OK and now we don't really see any difference here, but if I go to that drop down arrow, now there's a Filter by Color option.
Filter by Font Color, and it shows in the filtered results, just the ones where we have the TRUE.
And it's got the same 8 count that we had before.
To clear that, I've got a shortcut up on my Quick AccessToolbar.
Now all the records are showing again,
We can clear out this list of formulas that we entered.
Now if I want to look at from January 1st to February 15th.
I can go back to my Filter by Color, and it now includes all of the dates in this new date range.
without having to go in and change the settings in the filter Between that we did before.
.
30 июл 2024
