Flag if date created > 48 hours and completed column is blank

yhengyheng ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
04/10/19 Edited 12/09/19

Hello,

I'm new to using SmartSheets and first time poster here. Hoping you can help me with an elegant solve? I'm trying to create a function where a row will be highlighted when it satisfies two criterias:

1. if the date created has been more than 48 hours

2. and completed is blank (not marked with a date)

Basically in terms of workflow automation, when a request is submitted and the other team has an SLA of 48 hours and hasn't completed the request yet, the row will be flagged/highlighted.

Thank you!!

 

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    Try this.

    =IF(AND([email protected] <= TODAY(-2); NOT([email protected] = "")); 1; 0)    

    The same version but with the below changes for your and others convenience.  

    =IF(AND([email protected] <= TODAY(-2), NOT([email protected] = "")), 1, 0)

    Country Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Mike WildayMike Wilday ✭✭✭✭✭

    You'll want to create a Column using Symbols Flag type and put Andree's formula in that column. 

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Mike,

    Thanks!

    And you'd then use the flag to trigger a conditional formatting rule to highlight the row. 

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    If the sheet is not regularly active though, the TODAY() function won't update. I personally would go with a helper Date column and use 

     

    =[Created Date]@row + 2

     

    I would then set up my reminder to go out on the date listed in the helper column if the Completed Date field is blank.

    thinkspi.com

  • Mike WildayMike Wilday ✭✭✭✭✭

    Genius idea in case users don't regularly open the sheet. 

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Thanks!

    The way the post is written, it almost sounds as if new entries may be added via form.

    If that is the case, a new form entry would update the TODAY() function without the sheet having to be opened.

    The problem with that is if a new form isn't submitted everyday, then dates will fall behind until a new one is added again.

    That's why I have gotten into the habit of using my above solution for "ticket submission" type setups.

    thinkspi.com

  • I need to flag a field after 24 actual hours, not 1 calendar date in the future. For example, if a form is submitted 05/01/20 8:04AM, I need to flag a field if it hasn't been modified by 05/02/20 8:04AM. if there any way to do this in smart sheet?

    I have this formula with the following logic, but it if a form is submitted 11:59 PM it will show as flagged 12:01 AM.

    =IF(AND(TODAY() > [Due Date]3, ([IT Done?]3 = 0)), 1, 0)


    [Due Date] is =[Submission Date]3 + 1

    [Submission Date] is a date is the system submission time + 1 day


    Any help would be greatly appreciated

Sign In or Register to comment.