Forum Discussion

omiaye's avatar
omiaye
New Contributor II
2 years ago
Solved

Different Date standardization into a format

I have three different ways date column records can be made available in my excel input file for processing namely  YYYYMMDD, YYYY-MM-DD ,  and MM/DD/YYYYY. This means for any input excel file record, the date column can have one of these three formats. Please my question is how can I format any one of these record into YYYY-MM-DD format?

YYYYMMDD >> format into YYYY-MM-DD
YYYY-MM-DD >> do nothing since it is in the right format
MM/DD/YYYYY >> format into YYYY-MM-DD

Thanks.

3 Replies

  • alchemiz's avatar
    alchemiz
    Contributor III
    2 years ago

    Hi omiaye ,

    Good day, see expression below. Hope this helps.

    $myDate.contains('-') ? $myDate : ( $myDate.contains('/') ? Date.parse($myDate,'MM/dd/yyyy').toLocaleDateString({'format':'yyyy-MM-dd'}) : Date.parse($myDate,'yyyyMMdd').tolocaleDateString({'format': 'yyyy-MM-dd'}) )

    Thanks,
    EmEm

  • ivicakoteski's avatar
    ivicakoteski
    New Contributor III
    2 years ago

    Hi omiaye  ,

    You can try the following expression that uses the logical operator || (OR) :
    (Date.parse($Date, "yyyyMMdd") || Date.parse($Date, "dd/MM/yyyy") || Date.parse($Date, "yyyy-MM-dd")).toLocaleDateString({"format":"yyyy-MM-dd"})

    BR.
    Ivica



  • omiaye's avatar
    omiaye
    New Contributor II
    2 years ago

    Thanks everyone for your response; it is greatly appreciated.