Tick a checkbox depending on value of another field

mo'connor113211

I have a multi column Smartsheet that tracks items by date.  Part of the functionality shows the number of days the item has been active:- 

=IF(OR([S.Status]5 = 3, [S.Status]5 = 1), TODAY(0) - [Start Date]5)

There is also a check box column.  What I would like to do is to automatically tick the check box if the number of days is more than a pre-determined value (let's say 20).  This is simple enough:-

=IF([Days in Queue]5 > 20, 1, 0)

Now for the "but".  Each item within the Smartsheet can move to another team and when this happens the [Days in Queue] goes back to zero, however what we need to happen is for the check box to remain ticked if it has ever been ticked.

 

Example: 

[Days in Queue] is 15 and item moved to another team and then back to this team (checkbox not ticked and [Days in Queue] value returned to zero)

[Days in Queue] gets to 22 - we want the check box ticked when the value was 21.

Item moved back to another team and then back to this team (checkbox must remain ticked and [Days in Queue] value returned to zero)

[Days in Queue] gets to 15 and then the item is set to closed at which point the [Days in Queue] is made null but we want the check box still ticked because at one point in its life cycle, the item's [Days in Queue] went over 20.

 

All assistance greatly appreciated.

Thanks,

Matt

Paul Newcome

You can add in another date type column. One that records the ORIGINAL date that does not get updated as it changes teams. Then use that date in your above process.