• egoldenSFDC
  • NEWBIE
  • 0 Points
  • Member since 2012

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 2
    Replies

I want to update two formula fields based a value selected in a third field(picklist field)

 

 

1. Status is a picklist field ---  when status is selected as 'Done' 

2. Completed - Formula  Date field -  would have the value

3. Open date  - Formula number field -  when Completed has a value it would populate the no. of days between the created date and Completed date value .

 If completed date has no value then Open date will have value  - Today  - Created date .

 

I have written this formula :

 

1. For Completed : 

 

IF( ISPICKVAL(Status ,'DONE'), Today(), NULL)

 

It is populating the value but not stoppping . IT is crossing the date and updating current date . IT should popualte the dae when Status is selected as DONE .

 

2. For Open Date :

 

IF(
isnull(IF( ISPICKVAL(Status ,'DONE'), Today(), NULL)),
ROUND(NOW()-CreatedDate, 0),
ROUND(((IF( ISPICKVAL(Status ,'DONE'), Today(), NULL))-DATEVALUE(CreatedDate)),0)
)

 

i am using IF( ISPICKVAL(Status ,'DONE'), Today(), NULL) because one firmula field is not called in other .

 

Here the Open date keeps on populating data based on the created date .

 

EX : 

 

If the record is createOCT 20  , status is changed to DONE on oct 22 

 

So the completed field should have OCT 22 as the date and 

Open date should have 2 as the value

 

but it showing the calculation based on todays date (27th OCT)

 

completed has oct 27 as the value and Open date has 7 as the value .

 

PLZ help here