Skip to main content
Solved

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

  • September 13, 2021
  • 5 replies
  • 2 views
H

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!

Best answer by Dimitris_Goudis

Hi Hannah,

you use the following if formula:

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

View original
Did this topic help you find an answer to your question?

5 replies

Dimitris_Goudis
Forum|alt.badge.img+17

Hi Hannah,

you use the following if formula:

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

H
  • Hannah_Morgan
  • Author
  • Participating Frequently
  • 9 replies
  • September 13, 2021
Dimitris_Goudis wrote:

Hi Hannah,

you use the following if formula:

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


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
Forum|alt.badge.img+17

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.

H
  • Hannah_Morgan
  • Author
  • Participating Frequently
  • 9 replies
  • September 14, 2021
Dimitris_Goudis wrote:

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.


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
Forum|alt.badge.img+17
Hannah_Morgan wrote:

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:


Lol I can’t give an explanation :white_check_mark:

H
1 person likes this
  • H
    Hannah_Morgan

Reply


Most helpful members this week

Terms & Conditions