Complex date filtering without a bunch of columns

Options

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?

Best Answers

Answers

  • Joshua Taylor
    Options

    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.

  • Joshua Taylor
    Options

    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
    L_123 ✭✭✭✭✭✭
    Options

    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
    L_123 ✭✭✭✭✭✭
    edited 11/16/20
    Options

    Double tapped submit my bad...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.