Data validation and Output - Reading in an Excel file - Date Formatting issue?

[Claje]

Hi,

I'm not sure if either part of this is possible, but I figured I would reach out to the community to see if anyone has encountered anything like this.

I'm trying to build a data validation step for a business user, who will be providing an excel file.  A piece of this data validation is that all date fields have to be formatted as YYYY/MM/DD.

I'm encountering two problems:

1)  Since Excel stores all dates as numbers in date format, the formatting that is maintained in the Excel file is lost on reading it into Alteryx.  Is there any way around this?
2) I'm trying to output the file, maintaining the same formatting, but adding several new fields for data validation to the end of the file.  Is there any way to maintain a YYYY/MM/DD formatting in an Excel output?

[michael_treadwell]

If you format the date column in Excel as a date column, the dates should read in correctly to Alteryx:

https://support.office.com/en-us/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95...

If for some reason you cannot change the column formattting in the Excel file, there is another community post that explains how to convert from Excel numeric time in Alteryx:

https://community.alteryx.com/t5/Data-Sources/How-to-convert-the-Excel-numerical-date-to-date-in-Alt...

https://community.alteryx.com/t5/Data-Preparation-Blending/How-to-Create-Date-from-Excel-Date-Format...

I've attached a workflow to show that Excel date columns should come in formatting non-numerically in Alteryx.

[Claje]

Hi,

Thanks for the quick response!

So the issue I'm having is actually the opposite - I might not have been clear enough.  The dates are stored as dates in excel, and alteryx reads them as valid dates.  Unfortunately, that means that I can't do any automatic validation that the date is displayed as YYYY/MM/DD - since Alteryx reads it as a date before it even gets into the workflow.

I am encountering the same issue on output, even if I store the dates as YYYY/MM/DD strings, if I create a new Excel file (Or CSV for that matter), Excel automatically reads them as dates and converts back to its standard date format (MM/DD/YYYY).

[michael_treadwell · Accepted solution]

If the date column in Excel is formatted as a string, then Alteryx will read the field as a string in the correct format.

Then you can use the RegEx tool to test the format.

If you make sure the field in Alteryx is a string, it should output back to Excel as a string in the correct format.

I've attached a new example.

[Claje]

Not entirely sure why I couldn't get it working in testing yesterday.  Enforcing the field type to string was able to output a formatted string for date to excel.

The input, it sounds like there isn't much I can do, since I don't want to force the users who will be running this to reformat the excel file they are using as an input.

I've marked this as a solution.  Thanks!