1

I have a spreadsheet I update daily that's grown to be ~1700 rows. I reset it every year, but I don't really need to see more than the last 90 days of entries.

I can't use FILTER(), as I still need to make entries and edits to the list, so I'm trying to work with filter view, but the "custom formula" field just doesn't seem to accept any functions.

I've tried "=(Today()-90)" to no avail. The only option that seems to work is to pick a specific date, only I'll have to change this date every so often as the filter grows too large.

Is there really not a way to auto-filter dates based on how far they are from TODAY()?

| improve this question | |
  • Kevin, Filter rules work the same way as conditional formatting rules. You have to name the cell range (and, since the filter selects the whole range, you only need to name the top cell in the range; see my answer below). – Erik Tyler Oct 15 '18 at 14:28
1

In your filter, choose "Custom formula is" (bottom of the dropdown list) and enter this formula:

=A2>=TODAY()-90 (replacing A2 with the first data cell in your date column)

Click "OK."

Works similar to conditional formatting rules.

| improve this answer | |
0

You could use a combination of the SORTN and SORT functions to at least get close to what you are aiming for.

Please note that you might have to create a copy of your data to make this work - or I could be wrong here, depending on your situation.

This article provides the sample formula with steps in detail you can use.

Essentially, we want to sort the recent 3 months data, so while editing your formula, ensure the date is a dynamic entry within the formula (not a hard-coded date field, but the one that picks up the last 90 days).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.