0

I have two dates fields as (Procedure_Code_Date__c and Service_Date_To__c ) on my object.And i want to make a new formula field for if one of the dates is null then other one will populates or if both of them null then createddate will populates my formula field.So i wrote this formula field but it not works. How can i modify this ?

> IF( Procedure_Code_Date__c <> Null , Procedure_Code_Date__c , IF( 
    > Service_Date_To__c <> null , Service_Date_To__c, IF( CreatedDate <>
    > null , CreatedDate)))
5
  • What is the return type for your Formula field ? and what are the data type for Procedure Code Date and Service Date To fields?
    – Sanjay
    Aug 12, 2015 at 15:04
  • Make sure all the data types are dateTime type.
    – Sanjay
    Aug 12, 2015 at 15:14
  • @Sanjay formula fields type is date, also Procedure Code Date and Service Date To field's type is date Aug 12, 2015 at 15:32
  • CreatedDate returns dateTime type, if you want your result in 'Date' type then use the DATEVALUE function to convert a date/time value into a date value.
    – Sanjay
    Aug 12, 2015 at 15:36
  • What is not working? Is there an error saving it (compiler)? Is there an error at runtime?
    – pchittum
    Aug 12, 2015 at 15:48

1 Answer 1

1

Try -

IF( NOT(ISBLANK(Procedure_Code_Date__c)) , Procedure_Code_Date__c ,    
IF( NOT(ISBLANK(Service_Date_To__c)), Service_Date_To__c,     
IF( NOT(ISBLANK(CreatedDate),DATEVALUE(CreatedDate),''
     ) )

So CreatedDate returns value of type dateTime and the formula field you are using returns value of type Date

DATEVALUE function converts the return type of CreatedDate's to data type - Date.

Also using IsBlank() is the best practice when you are checking if an expression has a value or not.

6
  • This is a correct answer. A good answer would explain why you made the changes you did and explain why the original formula failed. Please expand, and I will vote up!
    – pchittum
    Aug 12, 2015 at 15:49
  • 1
    It should be !ISBLANK (not blank), your formula returns the field if its blank. Your last IF statement should probably be IF (!ISBLANK(CreatedDate), DATEVALUE(CreatedDate), null) so that it returns null
    – BarCotter
    Aug 12, 2015 at 15:56
  • @BartCotter: Good catch ! I edited my answer +1 for you
    – Sanjay
    Aug 12, 2015 at 15:59
  • 1
    Also, I don't think you need to include condition for createddate. I assume it will be always present since it is a standard field.
    – Dave
    Aug 12, 2015 at 16:01
  • Sanjay answered correctly. Just in case you want to avoid condition for createddate, you can use this formula. IF(!ISBLANK(Procedure_Code_Date__c), Procedure_Code_Date__c,IF(!ISBLANK(Service_Date_To__c), Service_Date_To__c,DATEVALUE(CreatedDate)))
    – Dave
    Aug 12, 2015 at 16:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .