Formula or Automation to toggle a check box on/off?

Hi all!

I've tried a couple of formulas and created some automation to try and get this to work to no avail. I am trying to have a toggle box selected when it has been over 24 hours since a start date was set with no end date. If an end date is assigned before that 24 hour window closes then the box stays off. Here's a visual:

I want the box under [Over 24 Hours?] to be checked in rows 154 - 156 since they have a [Date of Issue] greater than 24 hours ago and no [Date Resolved].

Using =IF(AND([Date of Issue]@row <> "", [Date of Resolution]@row = ""), 1, 0) doesn't solve the issue. Once I've entered up to the =IF(AND([Date of Issue]@row <> "", the formula breaks. For automation I've used this workflow:

I could sue some help with this one. Help and thank you!

Find more posts tagged with

Accepted answers

All comments

Ella

@Brandon R I would add a helper column for Due Date and create a formula in the check mark field based on it. So it would be IF Due Date is in the past and Date Resolved is blank, then add check mark, otherwise leave blank.

Lucas Rayala

Hi @Brandon R -- to be clear, I believe the dates are both hand entered, correct? Meaning they aren't formulas? If they are formulas, the ="" will detect a formula and see that as a non-blank cell. You can do this instead:

=IF(AND(LEN([Date of Issue]@row)>0, LEN([Date of Resolution]@row) = 0), 1, 0)

LEN looks for the number of characters or spaces in a cell, and does not detect formulas.

Brandon R

@Lucas Rayala, they are hand-entered. They have access to both a form for entering the information as well as the sheet. I'm just attempting to track how often an issue takes longer than the "24 hours" to resolve. Not exactly the best approach since the dates aren't automated but it's what I have built so far. It's a slow adoption of Smartsheet.

@Ella would the formula I provided in my question be the one to use, =IF(AND([Date of Issue]@row <> "", [Date of Resolution]@row = ""), 1, 0)? Once I get to the second half of that formula it breaks it. I'm getting the #UNPARSEABLE error code. Sorry about the ignorance. I'm slowly making my way through the core training but still have a long way to go.

Lucas Rayala

Hi @Brandon R -- does this slight mod work?

=IF(AND([Date of Issue]@row<Today(), LEN([Date of Issue]@row)>0, LEN([Date of Resolution]@row) = 0), 1, 0)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.