• egoldenSFDC
  • 0 Points
  • Member since 2012

  • Chatter
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
  • 2

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 :


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