Filter Date Columns
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Filter Date Columns
I have an issue with filtering date column rows. I have set the filter to say m/dd/yy and all dates listed, so I changed the filter to mm/dd/yyyy and all dates are listed. As a final step I inserted # before and after like Access and all dates are listed. So what is the proper entry to obtain a filtered list of dates i.e. 5/27/2021?
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
Additionally there is a summation row=2 and two completely blank rows=3,4. The summation row is filtered out and the blank rows are not.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Date Columns
One way to do this:
- Click the Filter dropdown.
- Clear the check box 'Select All'. Now nothing is selected.
- If necessary, expand 2021, then expand May.
- Scroll down, and tick the check box for 27.
- Click OK.
- Click the Filter dropdown.
- Clear the check box 'Select All'. Now nothing is selected.
- If necessary, expand 2021, then expand May.
- Scroll down, and tick the check box for 27.
- Click OK.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
You may have to go back one or more generations as I am using 2007. I should have included that in the original post, sorry.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
I just tried another similar workbook/worksheet and the filter process works as expected.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Date Columns
Did you click in the data and then click Filter in the Sort & Filter group of the Home tab of the ribbon? That will add a filter dropdown arrow to each of the column headings.
For a date column, the options shown in my previous reply are available in Excel 2007 too.
For a date column, the options shown in my previous reply are available in Excel 2007 too.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
I'm not sure what you mean exactly, but here are some images. You did see that the filter functions work properly in another similar instance?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 12633
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Filter Date Columns
Is the date definitely dates, or could it be text that just looks like dates?
StuartR
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
Look at the first image with the date selected. BTW I tried filtering on "5/27/2021", that didn't work either. I refer back to the post of 08:21 my time.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Date Columns
Since there are blank rows below the column headers, the filters don't "see" the data.
Do the following:
1) Select cell A1.
2) Click Filter in the Sort & Filter group to turn the filter OFF.
3) Select the entire range in columns A to O from row 1 to the last used row.
4) Click Filter in the Sort & Filter group to turn the filter ON again.
5) Click the filter arrow in one of the date columns.
Do the following:
1) Select cell A1.
2) Click Filter in the Sort & Filter group to turn the filter OFF.
3) Select the entire range in columns A to O from row 1 to the last used row.
4) Click Filter in the Sort & Filter group to turn the filter ON again.
5) Click the filter arrow in one of the date columns.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
Here is a similar worksheet.
The images are too large for the site and the filtered results will be in the next post.
The images are too large for the site and the filtered results will be in the next post.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
Here is the filtered results. As I indicated this worksheet behaves in the manner expected. There are blank rows in this spreadsheet also, but I will attempt to follow your suggestions Hans.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
Yes Hans the filter process worked with your suggestion, that doesn't explain the different result in two similar spreadsheets.
Thanks Hans.
Thanks Hans.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Date Columns
The most probable explanation is that the whole range was NOT included when the filter was turned on in the "problem" worksheet, while it was included in the worksheet where it worked as intended.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Filter Date Columns
Using that thought, I deleted the filter then selected Columns A-O and then reapplied the filter to the top row as before resulting in an expected behavior, thank Hans.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Date Columns
Best wishes,
Hans
Hans