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

yheng

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!!

 

Andrée Starå

Hi,

Try this.

=IF(AND(DateCreated@row <= TODAY(-2); NOT(Completed@row = "")); 1; 0)    

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

=IF(AND(DateCreated@row <= TODAY(-2), NOT(Completed@row = "")), 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

Mike Wilday

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

 

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

Paul 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.

Mike Wilday

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

Paul 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.

Mark Berger

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