Date Issue using Excel file

[Karlo]

Hi,

I am having trouble with Alteryx as my data contains date type values (i.e. 02-Apr-17) for some reason Alteryx changes this to 2017-04-02 (I take it this is the default way in which Alteryx shows it). However the output I want is the initial one I started with (dd-mmm-yy) but I don't get that. Also what if the data type is not a date can I not get the initial output i started with?

Furthermore, if some a column has 'custom' type data (looks like a date -i.e. 02-Apr-17) and within that same column there are N/A's would this be considered within Alteryx as null?

Thanks and hope my question can be answered :)

[Rohit_Bajaj]

Hi Karlo,

Is there a possibilty of modifying the column in source connector to string if it is getting defaulted to date.
Or can a formula be used to format date is desired manner or handle N/A

[Karlo]

I have tried to use the formula to format the date but I have had no luck with it - tried to use both datetimeformat() and datetimeparse() for some reason keep getting errors. hopefully the attached files will shed some more light on the issue. Apologise for making it so cryptic, there is some sensitive data so I had to cut and splice it.

The header titled engagement kick off...has both n/a and date values don't know if this is the issue. I only need Alteryx to output the data the way it receives it (Excel input). The files uploaded I have not done any date-parsing, etc. The only file that shows this is the error file.

[mcarrico]

A few things here:

1. Alteryx is likely receiving the data and converting it to Date or DateTime format with its default formatting for dates (YYYY-MM-DD)
2. One thing to recognize about the way Excel stores dates is that it is simply an integer with formatting happening within Excel.  For example, the dates you provided can be seen below with their corresponding numerical values.  You can verify this on your side by changing the number formatting from Date to General.
   
3. If you are outputting from Alteryx to Excel using the date field, Excel will also apply its default number formatting, which is different than yours.  Each time you run this workflow and overwrite your output, the default number formatting from Excel will be inherited by those cells.
4. If you want to get your number formatting output to Excel, you can use a DateTime tool and tell it that you want to go from DateTime to a formatted string (as shown below).
   
5. The only problem with this is that when you output to Excel, it will recognize it as a potential date, but in order to convert it to a Date field, you have to tell it whether the 2 digit year is 19XX or 20XX.  Note that you will have to do this each time you output from Alteryx as it will overwrite any changes you have made to the file within Excel.
   

See attached for sample workflow and Excel files.  Hopefully you can get to an acceptable solution using one of these routes.

[mcarrico]

Whoops.  Double post.

[Karlo]

mcarrico thanks for this, I appreciate you being so detailed with your response. However, what if the date field had N/A values (and you wanted to output these as N/A for your actual output). For some reason I have N/A's within this column called "Engagement kick off process complete" and this is a business requirement; as it also has blanks aka null values and dates too. Although your solution would help if I only had dates within the column, I think the underlying issue is bigger and I am not sure what I can do in order to get this to output properly. 

[mcarrico]

If you are using the DateTime to formatted string approach, you could simply use a formula tool to check for a Null and change the Null to 'N/A.'  See attached for an example.

[Karlo]

Thanks for the prompt response but for those values that are n/a i'd like to output as it is (as these might be populated at a later date - so there could be a potential date eventually in these cells), however the column also contains nulls which are also fine as an output. In summary i'd like the column to be output with data as it is; with all the respective n/a's and blanks/nulls that the input data held plus the correct date format which is dd-mmm-yy (i.e. 02-Apr-17). 

[mcarrico]

This can also be achieved with some data manipulation.  By adding a Null filter before the date conversion, you can separate these records and union them back on later.  Situations like this can be accounted for, but just may require a few additional tools.

[Karlo]

thanks mcarrico I will try this out from your output it seems like thats what I want. I will mark it as a solution as soon as I've tried it :)

One last question what if this type of thing is across a few columns (date, n/a, nulls) so in order to get all these filtered out for each column all the nulls would I just use a formula tool?

Thanks