Relative Dates in Filters

Is it possible to set up a condition in a filter that includes a relative date like "Today" or "Tomorrow"? For instance, I want to only show rows where the Due Date is "Today" but when I come to work tomorrow, will that filter now have yesterday's date in it?

Find more posts tagged with

Sheets

Comments

J. Craig Williams

Use the criteria "is today" not one of the other choices that requires a specific date to be put in.

For tomorrow, use "is in the next (days)" and 1.

This will show both today and tomorrow (since today is in the next 1 days)

If you only need tomorrow (and not today and tomorrow), then...

Use

1. is not today

2. is in the next (days) = 1

and make sure that Show rows that match = all conditions.

Hope this helps.

Craig

Josh TerAvest

Oh duh! Yes, I see that now in the operator dropdown. Thank you!

Gary

I am trying to create a report that only includes tasks for the next day. I have been using the critera of "is in the next (days) 1 but both today and tomorrow show up on the report.

I tried to use the above critera:

1. is not today

2. is in the next (days) = 1

but "is not today" is not available in the dropdown menu.

I then tried to use the exclude function but that applies to both criteria so that does not work either.

Has anyone come up with another method??

Thanks

J. Craig Williams

Gary,

The OP was about a Filter, not a Report.

The "is not today" does not exist for the Report criteria.

Because the Report criterias are OR statements and can not be changed (to an AND), I have no work-around in the Report Builder.

You'll need to create a column to get the results you want.

Craig

Lars Einarsson Mansén

From a start date and an end date I would like to use filter to only show active lines, meaning the start date is today or older and end date is today or later. The problem is that if one of the dates actually is today the filter will not work.

I can do it with a "hard coded" date of today like the screenshot, but it would be great to have a relative date.

What am I missing, or can it not be done?

Thanks for input!

Smartsheet FILTER screenshot.png

J. Craig Williams

If you scroll down in the criterion, there are a few date related criteria, "in the future", "not today" and so on, however several things limit the ability to be useful in many date related situations, yours being one of them.

As I said, you'll need to create a column to capture your criterion and then filter off that.

Craig

Lars Einarsson Mansén

Good idea, thanks. I think I follow you, you mean that I create a third column which with a formula can identify today's date from the first two columns "start date" and "end date", correct?

If so, I just need to find that formula, and I'm not that good in Smartsheet's formulas... Any ideas on how to find this?

Mink

Hi there

Does anyone know how to filter results for a particular month, across multiple years?

Thanks

Eileen OH

you could create a month column with this formula

=MONTH(dateColumnHere)

then you can filter by the Month column and the day/year won't matter