I have a field called Invoice_Sent_Date_Text__c that has values like:

Sent [11/30/2018]

Billed as Package; Sent [1/28/19]

Sent [3/6/19]


I need to create a field that will isolate and display only the date values in this field but I'm not advanced enough with formulas to know the right direction to take with this. If anyone has any pointers on how to get started on this, I would be very grateful!

  • Is the field content always either (a) just a date or (b) some text followed by a date in square brackets at the end of the field? Or does it vary more than that? – David Reed Mar 7 at 19:13
  • A formula is not going to be able to get you where you want to go. You instead need to write a trigger so you can take advantage of better string handling capabilities and decision logic. – sfdcfox Mar 7 at 19:16
  • @sfdcfox how about using substitute? – Pranay Jaiswal Mar 7 at 19:42
  • 1
    @PranayJaiswal The problem I foresee with that is if the user wants them to be real date values (e.g. sortable). In addition, OP gives a list of example types; we don't know what other possible formats exist. A trigger would be future proof against all kinds of values that may or may not exist in the database. – sfdcfox Mar 7 at 19:50
  • @sfdcfox thanks. Makes a lot sense. – Pranay Jaiswal Mar 7 at 19:51

You can use SUBSTITUTE to replace the special characters with blank

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Name,'Sent', ''),'Billed as Package;',''),'[',''),']','')

Whatever special character sequence comes, just chain it to the substitute queue.

Input : Sent [11/30/2018] Output : 11/30/2018
Input : Billed as Package; Sent [1/28/19] Output : 1/28/19
Input : Sent [3/6/19]  Output : 3/6/19
Input : 3/6/19 Output : 3/6/19

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.