Skip to main content
Question

How can I get days overdue to stop revving without setting the value to null

  • April 23, 2019
  • 0 replies
  • 49 views

I am currently using the following formula which captures the days overdue within the formula field ?Days Overdue? while the status is not equal to Delivered.  I would like to be able to set the status to Delivered and not reset the ?Days Overdue? field to null. I also want to ensure that the ?Days Overdue? field stops counting so I have a record of the exact number of days the task was overdue so it can be reported.

 

Current working formula:

If([Status] = "Delivered", null, ToDays(Today() - ToDate([Calculated Finish Date])) <= 0, null, Today() - ToDate([Calculated Finish Date]))

 

For example:

Status = In Process with a the Calculated Finish Date of 4-19-2019. This displays 4 days overdue.

If I set Status = Delivered, I would like to keep the current value in Days Overdue field (4) and would expect if I logged in the next day that the Days Overdue value remains at 4. 

 

Any help would be appreciated.


    This topic has been closed for replies.

    You will need to create a field called [Date Delivered] to capture the date that the Status was changed to delivered.

    One way to do that is when a form rule

    When the record is saved
    and condition
    Status has changed to Delivered

    Action
    Change the value in the field [Date Delivered] to "the current date"

    The you would change your formula for days overdue to something like this.


    If(
    [Status] = "Delivered"
    and [Date Delivered] >=  ToDate([Calculated Finish Date]),
       ToDays([Date Delivered] - ToDate([Calculated Finish Date])),  

    Max(0, ToDays(Today() - ToDate([Calculated Finish Date]))))

    A form rule will only work if you are editing record one by one on a form.  If you intend to do Grid Edit, then you would need to use an automation or Action to update the [Date Delivered]


    I added in a new Date Field "Date Delivered" and set the form action to set the date to date status was changed to Deliver.  I than added in the formula specified to the field "Days Overdue" .  I now get the error "Expecting duration but found number" and the days overdue field end up being empty.
    the field Date Delivered is a date field, and Calculated Finish Date is a formula-Work Date field
    Can you post your formula and the complete error message?
    Terms & ConditionsAccessibility statement