+ Start a Discussion
amyer2240amyer2240 

Update Date formula field to NULL value

I have a text field with a date in it (Opt_Out_Date_txt__c), and I'm trying to put that date into a Date-type formula field (Opt_Out_Date_Formula__c). The text field is in MM/DD/YYYY format. Here's what I have:

IF(OR(NOT(ISNULL(Opt_Out_Date_txt__c)), NOT(ISBLANK(Opt_Out_Date_txt__c))), DATEVALUE(RIGHT(Opt_Out_Date_txt__c, 4)+'-'+ LEFT(Opt_Out_Date_txt__c,2)+'-'+RIGHT(LEFT(Opt_Out_Date_txt__c,5),2)), NULL)

This is working fine when the text field is populated, but when it's blank I'm getting a #Error! message in the field (the syntax saves fine).

Any ideas? Thank you!
Best Answer chosen by amyer2240
David ZhuDavid Zhu
Just a minor change on the order would work.

IF(OR(ISNULL(Opt_Out_Date_txt__c), ISBLANK(Opt_Out_Date_txt__c)),null, DATEVALUE(RIGHT(Opt_Out_Date_txt__c, 4)+'-'+ LEFT(Opt_Out_Date_txt__c,2)+'-'+RIGHT(LEFT(Opt_Out_Date_txt__c,5),2)))