Use filter to filter last 7 days in a date column

[Triscia]

Is there a custom filter to use to only get the last 7 days? When I use the last week, it gives me this week and last week. I only need the last 7 days. TIA.

[FlameRetired]

Have you tried the option for Top 10? It can be changed to Top 7. As long as dates are sorted ascending this should work.

[Triscia]

No I haven't tried that at all. Not sure how to do that. I would sort ascending, but then how would I only get the last 7 days?

[FlameRetired]

Select a column header. Apply filters ... Data > Filter. Click the drop down arrow over the dates. Since dates are numbers (unless they have been entered as text) there will be an option "Number filters". Click on that. Then click Top 10. You will have the option to change that to Top 7.

Edit My mistake. They don't have to be sorted ascending. It will return the latest 7 dates without that.

[Triscia]

I do not see the top 10 listed. It has a bunch of things listed, but not Top 10. Is there another way to do it?

[FlameRetired]

Triscia, does it have Text filters listed? If so it sounds like your "dates" are text that look like dates. If you are not aware of it dates are numbers starting with day one in 1/1/1900. 9/21/2017 is day 42999 from 1/1/1900.

In a temporary helper column use the following formula =ISNUMBER(A2) or whatever the first date containing cell is and copy down. If they show FALSE then they are text.

Use Text to columns in the Data ribbon. Follow the prompts. When you get done the Number filter should show up.

[Triscia]

No the column is formatted as a date. The selections are equals, before, after, between, tomorrow, today, yesterday, this week, last week, next week, this month, last month, next month, this quarter, last quarter, next quarter, etc. When I select last week, it gives me too much, it goes clear back to 9/11/17.

[FlameRetired]

Aha. I forgot about the date filter.

If you can temporarily format those dates as General (Ctrl + Shift + ~) they will be numbers and the filter Numbers > Top 10 will be available.

Once filtered for the last 7 you can format those back to date. I usually keep a date somewhere handy so I can restore the dates with format painter.

Sorry about the oversight.

[sandy666]

you can try create PivotTable then : TopTen together with post£8

[Triscia]

but it only gives me 7 items. There are more than 7 rows that are within the last 7 days.

[sandy666]

Attach a sample workbook (not a picture). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Add description what you want to achieve

[Cantabrigian]

I feel it was quite clear what the OP wished to achieve, and why they were having difficulty.

I think the easiest way will be to add a helper column to your table - perhaps at the left-most side.
Title it "Show?" or something similar.
In it you can then place any formula evaluating any citerion (or indeed criteria) you wish to filter on.

So in this case, something like this is required, to get everything from the most recent 7 days.

=IF((NOW()-[Date])<8,"Y","N")

Note that I've suggested using 8 rather than 7, so that if it's late on Tuesday, say, you still get all the records even from early on the previous Tuesday.

Also note that the column name [Date] above, should be preceeded byt the AT symbol to get relative addresses correct for each row, but I can't type that as I've only just registered and the site things I'm trying to post a (dodgy) link!!!

;-)

[AliGW]

Thanks for this. I presume you realise that the thread is four and a half years old, though?

[Cantabrigian]

I do indeed!

But I'd stumbled across this thread just now, having just had the same problem, and it helped me get my thoughts in order, and suggest another solution which, having tried it, I find marginally less elegant than my own.

So this thread may still help someone else!

[AliGW]

Yes, it may well do so. Just wanted you to be aware that you are unlikely to be thanked by the OP given the time that's passed since they posted the query.