Date Formatting

[jlfromm]

I am bringing in several files that have the start and end dates formatted all different. When I bring them into my file they are a V_string, if I change the type to Date then the file show Null for them and nothing is brought in for those columns.  What do I need to do to convert them all over to a date format?  Here is a example of how my dates are coming in because that is the way they are in the different files.   I want to  reformat to read MM/DD/YYYY.

[AngelosPachis · Accepted solution]

Hi @jlfromm ,

I believe you should find all the different date formats appearing in your columns and then write an If statement that captures all different scenarios. In the attached screenshot, these are the different scenarios I could identify

Because you want to apply a formula across multiple fields, you can use a multi-field formula (it will also allow you to change the data type without having to use an extra select tool at the end).

Then the painful part is writing the expression (have this page open when doing so)

IF CONTAINS([_CurrentField_],"/") THEN  // either 10/01/2019 or  01/01/20
     IF LENGTH([_CurrentField_])=10 THEN DATETIMEPARSE([_CurrentField_],"%m/%d/%Y")  // 10/01/2019 
     ELSEIF LENGTH([_CurrentField_])=8 THEN DATETIMEPARSE([_CurrentField_],"%m/%d/%y")  // 01/01/20
     ELSE NULL()
     ENDIF
ELSEIF CONTAINS([_CurrentField_],"-") THEN [_CurrentField_]  // 2020-12-31
ELSE
      IF REGEX_MATCH([_CurrentField_],"\u{2,}")  // string has 2  or more uppercase letters appearing in a row, i.e. JANUARY 1

      THEN DATETIMEPARSE([_CurrentField_],"%b %d")
      ELSE DATETIMEPARSE([_CurrentField_],"%b %d, %Y") // July 1, 2019
      ENDIF
ENDIF

For DECEMBER 31,.. maybe you have to do a bit of cleaning before the multi-field formula so you don't get Null as a result.

Hope that helps,

Angelos