Skip to main content
Topics#Data ManagementPaul Ginther の質問
Paul Ginther (Regions Bank) が「#Data Management」で質問
2020年6月16日 16:31

Convert Text Year-Month value to Date

I have a Text field that contains a Year-Month value (2020-Jan, 2020-Feb etc) and need to convert this to a Date value for reporting purposes.  This value is brought in via integration that cannot be altered, so this only leaves the option of coverting it via formula field.  I have attempted to use the CASE function as well extracting the component pieces of the text field to "build" a date value but have not succeeded with either approach.  Any ideas on how I can make this work?
2 件の回答
  1. Tony Trinh
    2020年6月16日 16:36
    Hi Paul,

     

    If it will always be entered in that format from the Integration, then we can use a formula date field like this:

    DATE(
    VALUE(LEFT(Text__c, 4)),
    CASE(RIGHT(Text__c, 3),
    "Jan", 1,
    "Feb", 2,
    "Mar", 3,
    "Apr", 4,
    "May", 5,
    "Jun", 6,
    "Jul", 7,
    "Aug", 8,
    "Sep", 9,
    "Oct", 10,
    "Nov", 11,
    "Dec", 12,
    null), 1)
回答を記入...
0/9000