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)))
  • 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 '15 at 15:04
  • Make sure all the data types are dateTime type.
    – Sanjay
    Aug 12 '15 at 15:14
  • @Sanjay formula fields type is date, also Procedure Code Date and Service Date To field's type is date Aug 12 '15 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 '15 at 15:36
  • What is not working? Is there an error saving it (compiler)? Is there an error at runtime?
    – pchittum
    Aug 12 '15 at 15:48

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.

  • 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 '15 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 '15 at 15:56
  • @BartCotter: Good catch ! I edited my answer +1 for you
    – Sanjay
    Aug 12 '15 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 '15 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 '15 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.