Convert Date Columns in XX to 20XX format to Apply filters in Excel

[grromerro]

Dear Community, 

I have a report with columns dates that I need to be able to provide in the format dd-Mon.-yy. This report is saved as a table with color format so I use a render tool to save the final result. 

My issue is that, when the table is open and after adding a filter in excel, the dates in the date's columns does not let the filter provide the option to filter by year / month, but instead each date is listed as a value. See example below

       

 

In order to have the filter to work by year / month, I have to convert the dates columns into XX to 20XX formats using the scroll down option in excel. 

Then I can filter as needed

 

This report has 4 dates columns and +30K rows which means that applying this conversion takes approx. 10 min to complete and I can't really use excel in any other table or it get frozen.

Is it any way possible to get dates colums in a format that allows to add filter option in excel and filter by year/month?

A data sample workflow attached - it will be necessary to indicate the place to save the file to be able to run the testing.

Thank you so much

GR,

[perin · Accepted solution]

Hello @grromerro 

It's a limitation of Excel to read Alteryx Date data as actual date when rendering. 

Solution: Select Data or Column of Data>Go to Data Tab>Select Text to Columns>Select Delimited and then click Finish.

Unable to get Excel to recognise date in column - Super User

See the reference: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Output-date-in-MM-DD-YYYY-form...

Best,

Gustavo.

[TUSHAR050392]

The issue here with your format is that you are outputting the dates as string so it won't recognize it as a date and hence you are not getting month year filter in excel. So, either you can use excel to convert into a date or else you should output that date column as a date which will be in the standard Alteryx format (YYYY-MM-DD).

[Ankur_BDO]

The Dates provided in Excel are String value. You need to convert them to Date values before you can change the format. See the attached solution for help. Also visit the DateTime Function page DateTime Functions (alteryx.com)

[grromerro]

Thank you all for your feedback.