Complex date filtering without a bunch of columns

Joshua Taylor

I want to be able to have a "review today" filter that shows me rows that fall within a variety of date-based conditions. I can't figure out a way to do complex logic in the filters (grouping with AND/OR, comparisons against today's date), so instead I've created all these columns:

• Starts today (start = today)
• Finishes today (finish = today)
• Past start date (start not blank and start < today)
• Past due date (finish not blank and finish < today)
• Overdue to start (past start and not in progress)
• Overdue to finish (past finish and not complete)
• Review today (which uses all the above columns + some additional logic to give me a column to filter on)

... and I realize after writing this up, I could probably also have columns telling me that a task that should start today hasn't, and a task that should finish today hasn't.

Anyway, this is an awful lot of columns just to get a sense of what I should look at on a given day. Is there any better way to do this?

Sean Morgan

Hello @Joshua Taylor ,

I fell this could all be done from a single Column, using "Status" types and a Formula. If possible, please can you provide Screenshots on how your Sheet looks/works (Whilst hiding any sensitive information), and I'd be happy to help create a solution. Nonetheless, here's an example of what could be achieved:

The Formula I used was: =IF(Start@row = TODAY(), "Starts Today", IF(Finish@row = TODAY(), "Ends Today", IF(AND(ISBLANK(Status@row), Start@row < TODAY()), "Past Start Date", IF(AND(Status@row <> "Complete", Start@row < TODAY()), "Past Due Date"))))

From here, you could filter by the types under "Task Tracker"

As mentioned, this is an example of what could be achieved, and doesn't cover all your criteria, but I feel that a visual representation of your Sheet would allow me to make this more bespoke.

Kindest Regards

Sean

Paul Newcome

You could use a similar approach to the above suggestion, but instead of nesting the IF statements to generate a single status, you can "add" the IF statements together along with delimiters to output multiple statuses.

For example:

=IF([Start Date]@row < TODAY(), "Late Start", IF([Finish Date]@row < TODAY(), "Late Finish"))

Using this would only populate "Late Start" OR "Late Finish"

But...

=IF([Start Date]@row < TODAY(), "Late Start") + " / " + IF([Finish Date]@row < TODAY(), "Late Finish")

Using this would generate one or the other or even both depending on which were true.

Sean Morgan

Hello @Joshua Taylor ,

I fell this could all be done from a single Column, using "Status" types and a Formula. If possible, please can you provide Screenshots on how your Sheet looks/works (Whilst hiding any sensitive information), and I'd be happy to help create a solution. Nonetheless, here's an example of what could be achieved:

The Formula I used was: =IF(Start@row = TODAY(), "Starts Today", IF(Finish@row = TODAY(), "Ends Today", IF(AND(ISBLANK(Status@row), Start@row < TODAY()), "Past Start Date", IF(AND(Status@row <> "Complete", Start@row < TODAY()), "Past Due Date"))))

From here, you could filter by the types under "Task Tracker"

As mentioned, this is an example of what could be achieved, and doesn't cover all your criteria, but I feel that a visual representation of your Sheet would allow me to make this more bespoke.

Kindest Regards

Sean

Joshua Taylor

Hadn't thought of this, but it actually makes me lean harder into my multiple columns.

The issue with this strategy is that I'd only be able to assign a single "task tracker" value per row, when I'd actually rather know all the concerns about a particular row. That being said, maybe my multi-column approach is the right one.

The structure of my sheet (for the purposes of this question, at least), is the standard project template + the columns I mentioned in my OP. The only other thing that might be relevant is that I set my "Status" column with a formula based on the "Complete %" column.

Paul Newcome

You could use a similar approach to the above suggestion, but instead of nesting the IF statements to generate a single status, you can "add" the IF statements together along with delimiters to output multiple statuses.

For example:

=IF([Start Date]@row < TODAY(), "Late Start", IF([Finish Date]@row < TODAY(), "Late Finish"))

Using this would only populate "Late Start" OR "Late Finish"

But...

=IF([Start Date]@row < TODAY(), "Late Start") + " / " + IF([Finish Date]@row < TODAY(), "Late Finish")

Using this would generate one or the other or even both depending on which were true.

Joshua Taylor

Hmm... I could then set up conditional formatting and filters using "contains".

I do wonder, though, if having one massive formula in a single column is actually a better solution than having the individual columns I created. I was hoping Smartsheets had a more OOTB solution for this, but it sounds like this really is something that has to be solved on a per-sheet basis, using formulas.

That said, the specific question I asked really is answered. This has to be done with formulas, and it can be done in a single column. I'm just not sure if a single column is better, or worse.

L_123

It isn't better or worse depending on the size of your sheet. If you aren't pushing smartsheets limits in sheets size or performance, you could even mix the both of them. have your multiple columns, use join to push them together for easy viewing, use the original functions for your criteria and to make your formulas/conditional formatting easier, and hide the extra columns.

With that you have something simple and straightforward to reference and check against, while keeping everything you need to see in a single cell to quickly understand that state of each line item.

L_123

Double tapped submit my bad...

Paul Newcome

I personally would give a vote for @L@123's solution. Multiple columns means shorter formulas and easier to manage while the JOIN column allows for a single column filter and easier to read.