nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Excel input has a date column with 2 different date formats

bh1789

The Excel input file has 2 different dates, both are formatted as a general number so they come into Alteryx as a V-String. When I use the DateTime Parse tool, and convert string to date/time format from mm/dd/yyyy to yyyy-mm-dd, the 2nd format of yyyy-mm-dd converts to [null]

Is there a way to convert both formats to yyyy-mm-dd, Alteryx default date format?

Thank you

Date Time

Accepted answers

CarliE

hi @bh1789 ,

You can use the BB Date Macro to have it to normalize the date format automatically:

attached is the workflow.

If this helped to solve your issue, please make sure to mark it as a solution

DateHelp.yxzp

All comments

DataNath

Hey @bh1789, your 2 bottom dates are already in the correct format for Alteryx so we can just check whether or not ToDate() results in a null and if so, apply DateTimeParse(), leaving the other, already correct dates, as they are:

2 date formats to ISO.yxmd

Sebastiaandb

Hi @bh1789 ,

Here is the solution (i hope) ;-).

I used the formula tool for it :-).

By the use of regex it tests if the condition is met in that it matches your first way of writing dates, if not it assumes it is the second format. For both formats i converted them with datetimeparse to the Alteryx format.

You only need the first formula, the rest was just for testing :-).

Greetings,

Seb

Datetime.yxmd

Sarreddy

@bh1789

Can you try this way.

New Workflow049.yxmd

bh1789

Hi Seb,

I cannot seem to get this to work, can you assist?

DateHelp.yxmd

DataNath

@bh1789 did you try the other workflows? The ToDate() expression works fine:

Here is the issue:

@bh1789 the workflow you attached above is still using the Regex_Replace solution - I was saying that the workflow/expression I posted initially works fine.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703