Tick a checkbox for current and upcoming work

SteveSteveSteve

Hi all,

I use Smartsheet as a kanban board, and primarily use the Gantt view for team calls. I'd like to be able to filter to see work happening now or coming up in the next few days.

My definitions and existing formulas:

Happening now = TODAY is after the start date but before the end date.

Formula: =IF(AND(TODAY() <= [Completion date]@row, TODAY() >= [Start date]@row), 1, 0)

Coming up = start date is within the next 15 days.

Formula: =IF([Start Date]@row < TODAY(+15), 1, 0)

Thanks to other posters I have an IF statement for each, but I'm struggling to merge those into a single formula, to give me a single checkbox.

I have this - again using previous poster's formulas - but it won't parse and I don't understand why:

=IF(OR(AND(TODAY() <= [Completion date]@row, TODAY() >= [Start date]@row)),([Start Date]@row < TODAY(+15), 1, 0)

Can anyone help me here, please?

Thanks, Steve.

Paul Newcome

You're on the right track, but your syntax is off a bit.

=IF(OR(this is true, that is true), 1)

=IF(OR(happening now criteria, coming up criteria), 1)

=IF(OR(AND(TODAY() <= [Completion date]@row, TODAY() >= [Start date]@row), [Start Date]@row < TODAY(+15)), 1)

SteveSteveSteve

Hi Paul,

Thank you for your input.

I'm some lines are returning TRUE where I think they should be FALSE:

The lines above are dates in the past - or no dates - but they are being captured as TRUE.

I'm a bit snowblind on this but it looks like the evaluation isn't working. Can you see any errors, please?

Kind regards, Steve.

SteveSteveSteve

Well there are embarrassing timestamps.

One of my two original formulas is not behaving as expected:

The "Within 15 days only" formula is:

Formula: =IF([Start Date]@row < TODAY(+15), 1, 0)

Paul Newcome

Try making it an AND statement to be less than or equal to TODAY(15) AND greater than or equal to TODAY().