Stop counting duration
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 previo...
Forum Discussion
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
------------------------------
Hi Gary,
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.
------------------------------
LB
------------------------------
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 when the status changes in a fourth field.
------------------------------
gary
------------------------------I need the counting field to stop counting when the approval status field is changed to complete or previously completed
------------------------------
LB
------------------------------Logically, your formula isn't "counting" but it's dynamically calculating the difference between two dates (aka the duration). You always need to provide two dates in order to calculate a duration.
You have two scenarios:
- Calculate the difference between POC Due Date (a date) and today (a date)
- Calculate the difference between POC Due Date (a date) and "when the approval status field is changed to complete or previously completed" (not a date)
For #1, you have two dates. But for #2, you only have one date.
Behaviorally, you want to "stop counting" "when the approval status field is changed to complete or previously completed", so you need to know the date on which to stop counting. Therefore, you need to record that date in a fourth field.
This is the way.
------------------------------
gary
------------------------------
- MarkShnier__You
Qrew Legend
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.
Parsing Append Only Fields
so... 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. 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
------------------------------Hey Mark,
I'm not needing to record the date - the date is manually entered and then once that date is entered I have a formula field that tracks the number of days that have passed since that date was entered.
Then as the record Approval Status is updated by the user selecting completed, or selecting completed previous plan I no longer need the field tracking the # to continue to count.
------------------------------
LB
------------------------------
