Getting Date in Excel output which can be filtered based on year and month (Like normal)

[tonysavio]

Hi,

I was creating a report from alteryx workflow which consists many date fields. In Alteryx and in excel, the date is getting displayed as yyyy-MM-dd format. The end user is not too fuss about the format, but he wants to filter the date fields based on year and month as shown in below example.

And my data which is in date datatype in alteryx is getting copied as Number in Excel. I am using render tool to generate the excel output.

Q1) Why is the data not getting generated as Date in Excel?

Q2) Is there a way to rectify this and generate date in excel which can be filtered as above picture?

Thanks,

Tony

[Qiu]

@tonysavio 
Are you outputting to a new Excel file?

Is there any other data rather than date in your column?

Based on test, it works just the way you are asking.

[afv2688]

Hello @tonysavio ,

as @Qiu  stated (too quick for me to keep up 😁), what is exactly the process you are doing?

Are you overwriting a sheet?

Creating a new sheet?

Overwriting a specific range?

are you saving as xlsx or any other format like xls?

There are many variables to be taken into account but just to know, if you create a new xlsx file and add dates does it happen too? Are you sure that the dates that are being written into the sheet have the Date or DateTime metadata assigned (just to be sure that they are not strings).

I am also unable to replicate the issue

Regards

[tonysavio]

Thanks for the quick reply guys 

 afv2688 , Qiu 

Are you outputting to a new Excel file? - Yes, using render tool

Is there any other data rather than date in your column? - Nope, just dates

Are you overwriting a sheet? - No, its a new sheet. Its getting generated using Render tool.

Overwriting a specific range? - No

are you saving as xlsx or any other format like xls? - xlsx

There are many variables to be taken into account but just to know, if you create a new xlsx file and add dates does it happen too? - If you add dates manually, I am able to filter on year and month, no problem in this case. But when I am generating the output through render tool, the date field in workflow becomes number in Excel.

Are you sure that the dates that are being written into the sheet have the Date or DateTime metadata assigned (just to be sure that they are not strings) - Yes, they have date datatype in workflow.

Hope this gives an idea.

Thanks.

[Qiu]

@tonysavio 

I think I have identified the issue is with the Render tool. Maybe it just outputs the data as string as default. 🙄

[tonysavio]

Hi Qiu,

I could use normal output tool, but I have an action tool which generates a list box and this requires me to have a render tool I believe.

[tonysavio]

Hey Guys,

I have identified that this is a known issue. There is a ticket raised regarding this. Although one person is suggesting that this is fixed in the latest 2021, I don't know where this is at.

So basically, everything that comes through render tool is formatted as Number. There is no workaround for this in earlier versions of Alteryx.

Thanks.

Check that discussion here.