How can I hide the field "overdue age" field, if the "submit date" field reached?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

This  is my formula:

Case([Task Status],"C-Closed-Resolved" , [Actual Resolution Date / Time]-[Submit Date]
,"C-Closed-Unresolved" , [Actual Resolution Date / Time]-[Submit Date]
,"C-Closed-Rejected" , [Actual Resolution Date / Time]-[Submit Date]
,"O-Assigned" , Now()-[Submit Date]
,"O-Pending" , Now()-[Submit Date]
,"O-In-Progress" , Now()-[Submit Date]
,"O-Escalated" , Now()-[Submit Date]
,"O-Stalled" , Now()-[Submit Date])

Photo of Mary Pearl

Mary Pearl

  • 50 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,998 Points 2k badge 2x thumb
You need to build in an "If" statement around your "Case" formula.  I'm not exactly sure what you mean by "submit date" field reached; so I have assumed that you're calculating Overdue only when the Submit Date is in the past.  


Also, I am assuming that your [Submit Date] is also a Date/Time field as well as your [Actual Resolution Date/Time], in order for you to use the Now() and also to not convert your Actual Resolution to a Date instead of date/time field value.  I am also assuming that your formula is a duration formula.

If([Submit Date])<Now(),

Case([Task Status],

"C-Closed-Resolved" , [Actual Resolution Date / Time]-[Submit Date],

"C-Closed-Unresolved" , [Actual Resolution Date / Time]-[Submit Date],

"C-Closed-Rejected", [Actual Resolution Date/ Time]-[Submit Date],

"O-Assigned" , Today()-[Submit Date],

"O-Pending" , Today()-[Submit Date],

"O-In-Progress" , Today()-[Submit Date],

"O-Escalated" , Today()-[Submit Date],

"O-Stalled" , Today()-[Submit Date]),

null)
Photo of Mary Pearl

Mary Pearl

  • 50 Points
Formula syntax error

There are extra characters beyond the end of the formula.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,998 Points 2k badge 2x thumb
Mary - I had to update the formula a couple of times to get it to work (because of the date/time issue).  You may have to re-check the currently-posted formula versus the original one notified to you by email as my response.
Photo of Mary Pearl

Mary Pearl

  • 50 Points
Hi laura- This is the new error message

The expression  Today() on the left hand side of the operator "-" is of type date while the expression [Submit Date] on the right hand side is of type date/time. The operator "-" cannot be used with these types of expressions.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,998 Points 2k badge 2x thumb
Hi - I had updated the formula from my original one to change the If statement from Today() to Now(); which is how Date/Time fields need to be handled.

If([Submit Date])<Now(),

Make sure your formula has Now() instead of Today().