Forum Discussion

PriscillaFramar's avatar
PriscillaFramar
Qrew Trainee
6 years ago

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

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.


Formulas and functions

16 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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]


  • PriscillaFramar's avatar
    PriscillaFramar
    Qrew Trainee
    6 years ago
    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
    • PriscillaFramar's avatar
      PriscillaFramar
      Qrew Trainee
      6 years ago

      Used what you suggested except Date Delivered is Actual Finish Date.

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

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

       

      The line: ToDays([Actual Finish Date] - ToDate([Calculated Finish Date])),   give me the warning of �Expecting Duration but found number�


    • PriscillaFramar's avatar
      PriscillaFramar
      Qrew Trainee
      6 years ago
      the formula is saved.  the days overdue value is wiped out. 
      here is a clean scenario on a app that you could walk through to see what I am referring to:

      if you download the following app under sample app:  Dynamic Dashboard by Bill Marshall 24 May 17and look at any task that is marked overdue. I will used the ROI assessment task with the start date of 4-23-2015 for example:

      This shows the task being 1458 days overdue.

      If I change the status to Completed, the overdue date gets wiped out. (This was my original formula)

       

      I would like to keep the # of days overdue so I can obtain metrics on how we did meeting deadlines.

       

      I tried implementing the following formula which does not wipe out this value in days overdue, however the number of days keeps incrementing ever day.  How can I get the Days Overdue entry to stop incrementing once the status is marked completed?

       

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