Stop counting duration

Question

I have a date field that needs to count the number of days that have passed from the date entered AND also need to the field to stop counting when a status is changed to completed or completed previous plan - not sure how to set this up.&nbsp;
fields are Date PoC Due (date field), PoC # of days past due (formula duration field Today()-[Date PoC Due]), Approval status (drop down field)------------------------------Lorrie------------------------------

gary1 · Answer

You need a fourth field "Approval Complete" to track the date when the status was updated to complete/completed previous plan. Users can enter the date manually, or you can do it with a pipeline when the status changes.
Once that field is in place, you can use this formula:
If (
    IsNull([Approval Complete]),
    Today()-[Date POC Due],
    [Approval Complete]-[Date POC Due]
)
------------------------------gary------------------------------

lb · Answer

Hi Gary,&nbsp;
Thanks for replying. The approval status fields is what holds the options of complete or completed previous plan. So I'm not sure I need to add an additional field for that. I need to count how many days have passed since the manually entered Date PoC Due and then when the status changes for that field to stop counting.&nbsp;------------------------------LB------------------------------

gary1 · Answer

You wrote:
"I need to count how many days have passed since the manually entered Date PoC Due and then when the status changes for that field to stop counting."

Therefore you need to track&nbsp;when the status changes in a fourth field.&nbsp;------------------------------gary------------------------------

lb · Answer

I need the counting field to stop counting when the approval status field is changed to complete or previously completed------------------------------LB------------------------------

markshnier__you · Answer

LB,
I think the most reliable way to record the date that the status is changed will be to change that status field properties to log changes. That way the date will be captured with the status. Then we will need a formula to par so the most current status and another formula to parse out the date of the most current status.

field properties to log changes. That way the date will be captured with the status. Then we will need a formula to par so the most current status and another formula to parse out the date of the most current status.

I am going to paste damn sheet notes to myself for how to parse out who did the update. What the most recent update was, and the date of the most recent update. So the idea is that you would change your existing field properties to log changes. Leave it at the default setting, which is too long new items at the bottom. And do not capture the time.&nbsp;
Parsing Append Only Fieldsso... if you have a text field set to Log Changes where the new entries act in the default manner which is to be at the bottom of the updates, here are some formuals to use to parse out the values from the most recent entry.&nbsp; I am also assuming that you are logging the date and not the date and time.Most recent value (formula text field type)Trim(Right([my update field],"]"))Date of most recent update (this needs to be a formula date field)ToDate(Left(Right([my update field],"["),9))Who did the most recent update (formula text field type)Trim(NotLeft(Left(Right([my update field],"["),"]"),9))

then you should be able to use Garry's formula. Post back if you get stuck.
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Stop counting duration

8 Replies