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; } } }

CSV date format is *M/DD/YYYY but Alteryx reading as YYYY-MM-DD

Jake5

Hello,

My workflow is trying to validate that a processor is entering date values in *M/DD/YYYY format. While doing a test run, I noticed the input file had the correct format entered as shown per the attached, but Alteryx is converting to YYYY-MM-DD. As a result, my filter is directing these records into the false lane when it should be the true lane

(using custom filter formula !IsNull(DateTimeParse([DATE_SHOULD_PROCESS],'%m/%d/%Y')) .

Even more confusing is I have another test file - also correctly formated as *M/DD/YYYY and Alteryx is retaining that format as desired. How can I get Alteryx to be consistent in this regard so as to avoid reporting false errors?

Alteryx date issue.png

Data Investigation

Accepted answers

binu_acs

@Jake5 You can check the dates based on / or - and filter it out

DateCheck_1025.yxmd

All comments

Luke_C

Hi @Jake5

Alteryx's date format is yyyy-mm-dd. Any other format is considered a string. When you load data from excel in if there's a date field identified (i.e. formatted as a date like your screenshot) it will treat it as a date automatically in Alteryx. You could convert back using a datetimeformat formula.

I would note, when you load data from CSVs every field is treated as a string by default, so the real question is are you dealing with Excel or CSVs? If CSVs like your title indicates you should not have this problem.

Jake5

I can confirm this is a CSV input I'm using exclusively - and agree, all fields are coming in as V_String. Additional research has uncovered the following. If I place the "problem" csv in as an input without opening beforehand, Alteryx reads it in as YYYY-MM-DD. But if I open the csv and hit save - with no alterations made - then Alteryx will read it in as *M/DD/YYYY. Any idea why this is happening?

Luke_C

Hi @Jake5

If you open the csv in notepad or some other text editor what does the data look like? I would bet it is in yyyy-mm-dd format, and opening in Excel and saving retains the format that excel applies.

Jake5

yes - I just confirmed in notepad it shows as yyyy-mm-dd format. So my question would be if I want to consider either *M/DD/YYYY or YYYY-MM-DD as valid formats, how do I write the custom filter to direct either of those conditions to the True lane and anything else to the False lane?

binu_acs

@Jake5 You can check the dates based on / or - and filter it out

DateCheck_1025.yxmd

Jake5

Thank you! That helped.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15911

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703