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

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?

Output

Date Time

Accepted answers

michael_treadwell

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.

dates.yxzp

All comments

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-db95123d273e?ui=en-US&rs=en-US&ad=US

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-Alteryx/td-p/11184

https://community.alteryx.com/t5/Data-Preparation-Blending/How-to-Create-Date-from-Excel-Date-Formatted-as-Number/td-p/9199/page/2

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

dates.yxzp

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).

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335