Excel Date format using Table and Render tools

[derar-alhussein]

Hello,

How can I output Date values to Excel using the Table and Render tools, so that Excel recognizes the values as a proper Date format (as seen in the below image).

I've attached a sample workflow.

Thanks

[RogerS]

I changed the date to a string and then used the formula tool to format the way excel wants it.  DateTimeFormat([Date],'%m/%d/%Y')

[derar-alhussein]

Hi @RogerS

In fact, I already tried this before! but Excel was unable to recognize the values as Date.

Does It work in your case ? Could you please send me a screenshot of your Excel after applying the filter on the Date column.

[MichalK]

I'm running into exactly the same problem.

Excel file generated with 'Render' tool does not recognize date fields properly.

It's possible to fix this on Excel side ( https://superuser.com/questions/817110/unable-to-get-excel-to-recognise-date-in-column ), but I would prefer not having to add extra manual steps.

I've tried multiple formatting options, but nothing seems to work.

[treloarc]

Hi,

I am just wondering if you ever found a solution to this? I am experiencing the same problem with the render tool

Thanks

[derar-alhussein]

Hi @treloarc 

No, I didn't find a solution.

I did a workaround solution through writing a Powershell script, and executing it using the Run Command tool. The script opens the excel file and format the required column:

$worksheet.Columns.item(1).NumberFormat = "dd/mm/yyyy"  

[treloarc]

Hey thanks for your response. 

So when you've updated the format of the cells in the Powershell script did you also manage to find a way to force a refresh of the cells after you had changed the format?

(i.e the equivalent of a user selecting a cell and then hitting enter?)

Otherwise the cells don't display the correct format in excel:

 

[derar-alhussein]

Hi @treloarc 

I forgot to mention that I first converted the date in Alteryx to Excel number as mentioned in this post by the reply of @LordNeilLord 

With this, you can later format it as date format using Powershell as mentioned above.

Derar

[riyadh_jose]

Sharing the solution I found.

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