Is there a formula to display the date when a field changes to a specific value?

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

I'm trying to determine the number of days between when a task is assigned to someone and when that task is marked compete.  

Here's the Scenario:  An invoice is assigned to the "Process Payment" Queue.  I'd like to write a formula to capture the date it's assigned. After it's processed, the worker assigns it to the "Completed" queue.  I'd also like to write a formula to capture the date it's assigned to "Completed"

Afterwards, I'll write a formula to count the number of days between.  

Make sense?

Photo of Jay (DDS)

Jay (DDS)

  • 430 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 450 Points 250 badge 2x thumb
One way to do this is to use Form Rules to record the "assigned" and "completed" dates.

Depending on how your app is set up, this would probably need some adjustments to make it work for you.  But here is the basic idea:



First create 2 new Date fields:  [Date - Process Payment] and [Date - Completed].  You can put these on your form.

You didn't specify how exactly an Invoice is "assigned" to the various queues - is it through a [Status] field or something similar?

Assuming it's through a field that can trigger a form rule (such as a Text - Multiple Choice field for [Status]), you can now set up 2 dynamic Form rules:

1)  When [Status] is equal to the value "Process Payment"

- change [Date - Process Payment] to "the current date"

2)  When [Status] is equal to the value "Completed"

- change [Date - Completed] to "the current date"

When the user saves - then the dates at which those [Status] values were changed would be recorded.

Then you can create a new Formula Numeric field - called [Days between Process Payment and Completion], with the formula:

ToDays( [Date - Completed] - [Date - Process Payment])

Which would return the number of days between the 2 dates.