Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Date and Number Format Excel

8 - Asteroid

Hi,

 

I have a table tool that outputs number and dates to an excel file. The problem with the date field is that theres no month/year drop down in excel and for the number field the  2 decimals don't show up in the table but only when the cell is selected. How do i reach the deisired output? is this possible?

 

alteryx date output : 

porpergrey_0-1610570119087.png

 

desired output format : 

porpergrey_1-1610570148358.png

 

alteryx number excel output (no decimals):

porpergrey_2-1610570190308.png

 

desired output :

porpergrey_3-1610570220159.png

 

 

 

Alteryx Certified Partner

Hi @porpergrey ,

 

May I ask, in your workflow, before the "Output Data" tool that produces that Excel file, what's the data type for the fields Value Date and Eur Equiv?

Alteryx Certified Partner

In my output, the data type for "Value Date" is set to a date and the filters drop down appearing on Excel seem to be working fine

 

AngelosPachis_0-1610571344280.png

 

If the data type is set to string, it makes sense for Excel to be unable to pick the different dates correctly because it's looking at the discrete values of dates.

 

Also, are you using a Table tool from the Reporting tool categories to output that table? I can see in the screenshots you sent attached that some lines have a different background shadings than others.

12 - Quasar

@porpergrey  -  Try this method for fixing your date format issue.

 

The default format for date is "YYYY-MM-DD" in the Alteryx workflow, so in the end of your workflow (but just before the Output tool) you need to convert that date field into desired "mm/dd/yyyy" format then export your data through the Output tool.

 

Please attach your dummy records for speedy solution.

8 - Asteroid

@AngelosPachis my Workflow uses a render tool after creating a table using a table tool. 

What are my options if I'm not using the regular output tool?

Alteryx Certified Partner

Hi @porpergrey ,

 

If you're using a Table and a Render tool, I don't think you can do much about it based on  what I am aware of.

 

The table converts into everything into plain text, so you lose the property of date. Hence, Excel can not subsequently understand that the information contain in that field is a date and return you the desired dropdown in filters.

 

Do you necessarily have to use the table tool?

8 - Asteroid

@AngelosPachis okay, thanks got it now. What about the numbers.. I see two decimals missing in the table but once the cell is selected it shows us in results in excel. How do I make it in the cell itself? using a table tool here

Alteryx Certified Partner

Hi @porpergrey ,

 

In that table tool of yours, can you make sure that when you select the "Eur Equiv" field, the number of decimals is set to be 2 as shown below

 

AngelosPachis_0-1610736974897.png

 

Let me know if that worked for you.

 

Cheers,

Angelos

 

Labels