I have a custom field that calculate time elapsed after due date passed. How can I make it to calculate the date elapsed after the due date if only it is not completed on the status/progress field.
Hi @Tsegaye_Shewangzaw_Z welcome to the forum 
If you have already created the formula field (today minus due date) then hide it from your project and instead add a new number in it’s place.
You could then add a rule that when the status field is not complete, to populate the number field with the value of your formula.
And then add a second rule to clear the value of the number field once the status is completed.
Would that work for you?
1 Like
When you say hide, you mean the column? I hide the column and added another column with a new number field but then there are three things to create a rule. ‘When’, ‘check if’ and ‘do this’. I don’t think I can find the action on the ‘do this’ part. I was setting the rule as 'When task status is changed, check if task progress is not completed then…? I need your help on this.
Hi @Tsegaye_Shewangzaw_Z , to be fair, having tested this myself, this is a bit more complicated because the formula field I mentioned needs to be formatted into a number (rather than in Weeks/Days) so that it can be used in the rule I mentioned.
So let’s start with a checklist:
-
Add a Status single-select field in your project. You can optionally skip this if you want to use the native task status instead (incomplete/complete).
-
Create a formula as per below or copy paste this into the Advanced editor:
( ( [[$TODAY]] - [[$DUE_DATE]] ) + 0 ) / 1440
You can call the field title anything you want, and optionally add this to your library. -
Add a number field like this (with likely no decimal spaces):
Once the above are in place, you can create the following rule:
A. When the Status field is changed
B. Check if your Status field isn’t Complete
C. Do this: Change Elapsed after due date # (your number field in step 3)
and click the + and select Elapsed after due date (fx) (your formula field in step 2)
D. Add an Otherwise if…
and select when field is set to…
E. Do this: Change Elapsed after due date # (your number field in step 3) but change it to Clear the field value.
Publish your rule and try it out!
3 Likes
Hey @Richard_Sather, Thanks a lot. It works and saves a lot of work. Thanks again for your help.
1 Like
Hey @Richard_Sather, just another help. Can I make the number to hold on to the number of days past the due date until it is completed instead of clearing it. Currently the elapsed days (fx) kept going even after it is completed. Can we make it to stop on the day where the assignee make the status completed?
Thanks in advance
Hi @Tsegaye_Shewangzaw_Z , the formula field will keep going but the number field will not, it will stay as is once a status is set.
But I understood you wanted it cleared. If not, then remove the Otherwise if condition in step D & E.
Then, in step B, change the ststus to when it is Complete instead of isn’t.