Formula field to calculate duration of a record from creation-->complete status

[Hannah_Morgan]

Hello!

I’m looking to create a calculated field that shows me the number of days it takes to complete a record. The start date should be when the record is created, and the end date will be based on a dropdown field’s value (e.g. when the status field is set to “complete”).

My thinking is that I need to create three formula fields:

1. The date when a record is created (I’ve managed to achieve this part).
2. The date when the status dropdown field is set to “complete”.
3. The difference between the two dates in days (I’ve managed to achieve this part).

So basically I’m missing how to create the 2nd field.

Am I approaching this the wrong way? Is there a better way to show this report that doesn’t involve using an app add-on?

Thank you!

[Dimitris_Goudis · Accepted solution]

Hi Hannah,

you use the following if formula:

if(status=“Completed”,LAST_MODIFIED_TIME(status),“leave it blank”)

[Hannah_Morgan]

Hi Dimitris! Thank you! I tried creating the field, but it returns an error like so:

Can you see what I’m missing?

This is the field I’m pulling from:

[Dimitris_Goudis]

Hi Hannah,

most probably something is wrong in other fields. In order to understand where exactly the problem occurs, please follow the following steps:

1. Create a formula field with following function:
   if(status =“complete”, 1,0) → if this works then the status value checker works perfectly
2. Create a formula field with following function:
   Last_modified_time(status) → if this works perfectly then the last_modified_time() function works perfectly.

Then try to combine the 2 functions in one, if it doesn’t work please check the text on your functions, all characters you use have to be english and not in other language. An other common issues is some workspace or base settings and we can check them after that trial.

[Hannah_Morgan]

Thanks for helping me troubleshoot :slightly_smiling_face: all seems to be working now.

Weirdly this formula throws an error:

IF(Status=“Complete”,LAST_MODIFIED_TIME(Status),“leave it blank”)

And this one doesn’t:
IF(Status=“Complete”,LAST_MODIFIED_TIME(Status),“leave it blank”)

Unless I’m blind, they both look exactly the same to me :grinning_face_with_sweat:

[Dimitris_Goudis]

Lol I can’t give an explanation :white_check_mark: