how to output to excel as datetime column format, using table and render tool?

Hi,

I can't figure out how to output datetime values to excel using the table and render tool, so that excel recognizes the values as a proper date time format, so excel users can build pivot tables using the date time values?

When outputting to excel using table and render tool, opening up the excel file recognises it as a number?

If outputting to excel using the normal output tool, then it works fine.

examples below, appreciate your help,

Ryan

example test using a text tool with 1 datetime value, along with table and render tool:

example datetime output.PNG

example test using text tool with normal output tool

example datetime output 2.PNG

excel 2.PNG

Reporting

Output

Accepted answers

JessicaS

Hi Ryan,

Workflow attached. I output my field as a datetime format using table and render to excel 2007 spreadsheet.

I then used formula: =DATEDIF(A3,A2,"D") on the spreadsheet without doing any reformatting in excel and it returned '1'

Thanks,

ExcelDate.yxmd

All comments

JessicaS

Hi @RyanNewsome,

I tested this on my machine and I see the same outputs as you do (excel recognizing the same data as different field types depending on output format).

However, I tested with some date functions on each output type and excel seems to recognize my outputs as a date field (can perform date calculations on the date field I created in Alteryx).

If you take the output of the render, and apply a date calculation to it in excel do you get errors?

RyanNewsome

Hi Jessica,

Thanks for testing.

I can't get date functions to work in excel? Are you outputting a datetime value from Alteryx?

I tried to do a DATEDIF and get #NAME? error

I tried using the date column in a pivot table, dragged it to rows, and it doesnt detect it as a date i.e. show years/quarters/months drop downs.

If you can get DATEDIF or pivot table to work with your output, could you please send me your workflow?

Thanks,

Ryan

JessicaS

Hi Ryan,

Workflow attached. I output my field as a datetime format using table and render to excel 2007 spreadsheet.

I then used formula: =DATEDIF(A3,A2,"D") on the spreadsheet without doing any reformatting in excel and it returned '1'

Thanks,

ExcelDate.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703